Introduction to MSSQL Server 2000 Analysis Services: Manage Distinct Count with a Virtual Cube

Monday Feb 14th 2005 by William Pearson
Share:

MSAS Architect Bill Pearson delves deeper into DISTINCT COUNT concepts, then leads practice in a "best practice" approach for improving the performance of DISTINCT COUNTS in cubes.

About the Series ...

This article is a member of the series Introduction to MSSQL Server 2000 Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server 2000 Analysis Services, with each installment progressively adding features and techniques designed to meet specific real - world needs. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial article, Creating Our First Cube.

Note: Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples. Images are from a Windows 2003 Server environment, upon which I have also implemented MS Office 2003, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2000 and MSSQL Server 2000 Analysis Services ("Analysis Services" or "MSAS"). The same is generally true, except where differences are specifically noted, when MS Office 2000 and above are used in the environment, in cases where MS Office components are presented in the article.

Introduction

In this article, we will continue the exploration of distinct counts we began in our previous article, Distinct Count Basics: Two Perspectives. Having discussed why distinct counts are useful, and often required, within the design of robust analysis and reporting applications, we described some of the challenges that are inherent in distinct counts. We then undertook practice exercises to illustrate general solutions to meet example business requirements, providing an approach afforded us by the MSAS user interface, and then an alternative approach we enacted using MDX. Our stated purpose was to lay the framework for this and subsequent articles, where we will focus upon specific scenarios that occur commonly in the business environment, within which the optimal use of distinct counts can become a very real consideration.

In this article, we will examine one approach to the efficient use of DISTINCT COUNT within our applications: We will focus upon the optimization of DISTINCT COUNT through the isolation of the DISTINCT COUNT attributes into a separate cube, and show how this represents one of the more efficient approaches to optimizing the related functionality. To accomplish our objectives, we will undertake the following steps in this article:

  • Set the stage by providing a hypothetical business requirement;
  • Meet the requirement with an MDX query that contains DISTINCT COUNT;
  • Comment upon performance of the query in general;
  • Create a separate cube to house the DISTINCT COUNT attributes of our solution;
  • Combine the new DISTINCT COUNT cube with the previously existing cube, through the creation of a virtual cube in MSAS;
  • Create a new query, targeting the virtual cube as its source, to return a dataset identical to that returned by our initial query;
  • Comment upon performance gains in executing the new query upon the new cube combination.

Manage Distinct Count with a Virtual Cube

Overview and Discussion

We mentioned in our introductory article, Distinct Count Basics: Two Perspectives, that it is common in the business environment to encounter the need to quantify precisely the members of various sets of data. A simple example, and one upon which we will expand in our hypothetical business requirement, involves the number of customers who are purchasing a product, or group of products, sold by an organization. We learned in the previous article that we can exploit settings within MSAS' Analysis Manager, as well as take more advanced approaches, to extend our analysis even further, and leverage MSAS to reach our specific business objectives.

We discussed why distinct counts differ from simple counts, noting that a distinct count might comprise, as an example, a count of the different products that were purchased, or of the individual customers who purchased our products. To review our discussion, COUNT(), in providing a total number of, say, customers, would also be providing multiple counts of the same customers, because customers will have, in most cases, purchased multiple products, multiple times. To reach our objective of counting different customers, then, we would need to count each different customer, only once. As we noted in our previous session, using COUNT() when DISTINCTCOUNT() is required not only misstates the number of different customers, but it also likely renders averages, and other metrics similarly based upon the count value, misleading or totally useless in our analysis efforts.

In this article, we will discuss and practice a solution for meeting an illustrative need, which expands upon the customer example to which we have alluded. Our practice example will also highlight the performance challenges that can arise in simply addressing such requirements in an intuitive manner. We will then take steps to reshape our solution to take advantage of another approach that meets the need, while bettering the performance of the overall solution.

Considerations and Comments

For purposes of this exercise, we will be working with the Warehouse cube, within the FoodMart 2000 MSAS database; these working samples accompany a typical installation of MSAS. If the samples are not installed in, or have been removed from, your environment, they can be obtained from the installation CD, as well as from the Analysis Services section of the Microsoft website. If you prefer not to alter the structure of your sample cubes as they currently exist, make copies of the cube we reference in the article before beginning the practice exercises. For instructions on copying cubes, see the Preparation section of Introduction to MSSQL Server 2000 Analysis Services: Semi-Additive Measures and Periodic Balances.

Hands-On Procedure

We will begin with a scenario that illustrates a requirement for a distinct count, using a hypothetical business need to add practical value. Let's say that a group of information consumers within the FoodMart organization have approached us with an information request they wish to meet using the Sales cube. The consumers want to be able to analyze the performance of products, by category, both in terms of dollar sales, and number of different customers contributing to those sales, for the third quarter (Q3) of 1997. In addition, they wish to see an "average sales per (distinct) customer" within the same dataset.

We will initially attempt to meet the needs of the consumers with relatively simple MDX, having introduced both MSAS and MDX approaches in Distinct Count Basics: Two Perspectives (see the steps provided there, if you have joined the series with this article, and find the initial query we present to be less than intuitive.)

Initial Approach via MDX

Let's initialize the MDX Sample Application, the platform from which we perform many practice exercises within the articles of our series. (We choose it because any organization that has installed MSAS has access to the Sample Application). We will create our initial query by taking the following steps:

1.  Start the MDX Sample Application.

We are initially greeted by the Connect dialog, shown in Illustration 1.


Illustration 1: The Connect Dialog for the MDX Sample Application

The illustration above depicts the name of my server, MOTHER1, and properly indicates that we will be connecting via the MSOLAP provider (the default).

2.  Click OK.

The MDX Sample Application window appears.

3.  Click File --> New.

A blank Query pane appears.

4.  Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

5.  Select the Sales cube in the Cube drop-down list box.

The MDX Sample Application window should resemble that depicted in Illustration 2, complete with the information from the Sales cube displaying in the Metadata tree (left section of the Metadata pane).


Illustration 2: The MDX Sample Application Window (Compressed View)

We will begin creating our query with a focus on returning results to meet the expressed business need of the information consumers. We will construct two calculated members / measures, one to contain the distinct count of the Customers, and one to calculate the average sale for each Product Category, per individual Customer. We will then SELECT the two calculated measures, presenting them to the immediate right of the Unit Sales measure for each respective Product Category.

We will retrieve a dataset with the measure / calculated measures forming the column axis, and the Product Category forming the row axis.

1.  Create the following new query:


-- ANSYS32-1 Initial Attempt at Distinct Customer Dataset
WITH 
   MEMBER
       [Measures].[Distinct Customers]
      AS
         'COUNT(CrossJoin({[Unit Sales]}, 
             Descendants ([Customers].CurrentMember, 
                [Customers].[Name])), ExcludeEmpty)'
   MEMBER
      [Measures].[Avg Sales per Customer]
      AS
         '[Measures].[Unit Sales]/[Measures].[Distinct Customers]'
SELECT
   { [Measures].[Unit Sales], [Measures].[Distinct Customers], 
   		[Measures].[Avg Sales per Customer]} on Columns,
   {[Product Category].Members} ON ROWS
FROM 
   [SALES]
WHERE 
   ([Time].[1997].[Q3])

The above represents an attempt to meet the information consumers' objectives with what appears to be the straightforward use of the DISTINCTCOUNT() function within a calculated member, to contain the count of the distinct Customers. We then create a second calculated member based upon the first, which we divide into the Unit Sales measure to derive the Average Sales per (individual) customer, as requested by the intended audience. We SELECT all three into the desired matrix to render the desired presentation.

The calculated member Distinct Customers embodies the "heavy lifting" in the query. We used the following definition (within the AS clause string for calculated member Distinct Customers):


'COUNT(CrossJoin({[Unit Sales]}, 
             Descendants ([Customers].CurrentMember, 
                [Customers].[Name])), ExcludeEmpty)'

to count the non-null Sales / Customer member tuples that it found, thereby deriving the number of customers. Because we wish to avoid counting all customer names (the lowest level of the Customer hierarchy), regardless of our level position in the hierarchy, we inserted the Descendants() function shown; this forces a limitation upon the count to solely the customers under the current member of the Customers dimension.

2.  Execute the query using the Run Query button.

The results dataset appears as partially shown in Illustration 3.


Illustration 3: The Results Dataset (Partial View)

The first thing that we notice, after clicking Run Query, is that this query takes a little longer to run than many of the "sample" queries we have created in past articles. As a matter of fact, this is exactly the observation that I am hoping even those new to MDX in general will make. The query provides the data to meet the requirements of the information consumers, but performance could become a problem.

The overhead generated in the query is due to the requirement for MSAS to perform a runtime assessment of each customer member, and there are many members. While this overhead may not be unduly troublesome from the perspective of our sample data, performance will be degraded far more within the context of the sizes of member populations that exist in many production environments. The performance degradation we have witnessed in our tiny sample cube is extrapolated to those larger populations, to an extent that becomes very real to analysts and other information consumers that rely upon the system to provide data in a reasonable response time.

3.  Save the query as ANSYS32-1 in a convenient location.

4.  Close the Sample Application.

Our next step will be to examine an option for mitigating the performance hit suffered within the straightforward application of DISTINCTCOUNT() within our query.




Isolating DISTINCT COUNT into a Separate Cube




We will begin performance enhancement efforts with the creation of a cube into which we will isolate the Distinct Count capabilities of the existing solution. We will manage this aspect of our solution from within the MSAS Analysis Manager.




Let's start Analysis Services and proceed with the following steps:



1.  Open Analysis Manager.



2.  Expand the Analysis Servers folder by clicking the "+" sign to its immediate left.



Our server(s) appear (my server, MOTHER1, is depicted in some of the illustrations).



3.  Expand the desired server.

Our database(s) appear, in much the same manner as depicted in Illustration 4.




Illustration 4: A Sample Set of Databases Displayed within Analysis Manager

4.  Expand the FoodMart 2000 database.

5.  Expand the Cubes folder.

The sample cubes appear, as shown in Illustration 5.


Illustration 5: The Sample Cubes in the FoodMart2000 Database

NOTE: Your databases / cube tree may differ, depending upon the activities you have performed since the installation of MSAS (and the simultaneous creation of the original set of sample cubes). Moreover, some of the illustrations will occasionally depict measures, dimensions, and objects that do not appear in the pristine samples. If this happens, please ignore the differences, as they are unlikely to impact the steps we are undertaking as part of our practice exercise.

Should you want or need to restore the cubes to their original state, simply restore the database under consideration. For instructions, see the MSSQL Server 2000 Books Online.

Because we need a cube that matches the Sales cube, with which it will be working, as we shall see, with regard to dimensions, we will simply copy the Sales cube and rename it in the following steps:

6.  Right-click the Sales sample cube.

7.  Select Copy from the context menu that appears, as depicted in Illustration 6.


Illustration 6: Select Copy from the Context Menu

8.  Right-click the Cubes folder for FoodMart 2000.

9.  Select Paste from the context menu that appears, as shown in Illustration 7.


Illustration 7: Select Paste from the Context Menu

The Duplicate Name dialog appears, providing a means for the renaming of the Sales cube clone we are attempting to create. MSAS will not allow duplicate cube names within the same database.

10.  Replace Sales with DISTINCT_CUSTOMERS in the Name box of the Duplicate Name dialog, as depicted in Illustration 8.


Illustration 8: New Name Appears in the Duplicate Name Dialog

11.  Click OK to close the dialog, and to create the DISTINCT_CUSTOMERS cube.

The DISTINCT_CUSTOMERS cube appears in the cube tree.

While the dimensions of the DISTINCT_CUSTOMERS cube must mirror those in the Sales cube, the new cube will require only one measure; that measure will be a Distinct Count measure, which we will add next. To prepare the new cube further, we will then dump the unneeded measures.

12.  Right-click the new DISTINCT_CUSTOMERS cube.

13.  Select Edit from the context menu that appears, as shown in Illustration 9.


Illustration 9: Select Edit from the Context Menu

The Cube Editor opens.

14.  Right-click the Measures folder in the Tree View to the left of the Schema tab.

A single-line context menu appears, as depicted in Illustration 10.

15.  Select New Measure from the context menu.


Illustration 10: Select New Measure from the Context Menu

The Insert Measure dialog appears.

16.  Click-select customer_id.

The Insert Measure dialog, selection circled in red, appears as shown in Illustration 11.


Illustration 11: Select Customer_Id from the Insert Measure Dialog




17.  Click OK to accept the selection.


The Insert Measure dialog closes, and we see the new measure appear (default name of Customer Id: the underscore character is removed) in the Measures folder.


18.  Click-select Customer_Id in the Measures folder, if required.

19.  If necessary, click the downward arrow beneath the Cube Tree to open the Properties pane.

20.  Click the Basic tab.

21.  Modify the default Name of Customer Id to the following:

Distinct Customers

22.  Type the following into the empty Description box, just below the Name box:

Distinct Count - Customers

23.  Click the box to the right of the Aggregate Function property label (at the bottom of the Basic tab), to enable the selector.

24.  Select Distinct Count in the Aggregate Function selector.

The Basic tab of the Properties pane appears, with our modifications, as shown in Illustration 12.


Illustration 12: Distinct Customers Measure - Properties Pane - Basic Tab

25.  Click the Advanced tab of the Properties pane.

26.  Ensure that Big Integer is selected for the Data Type property, as depicted in Illustration 13.


Illustration 13: Distinct Customers Measure - Properties Pane - Data Type Tab

27.  Right-click the Unit Sales measure in the Measures folder.

28.  Select Delete from the context menu that appears, as shown in Illustration 14.


Illustration 14: Select Delete from the Context Menu

The Confirm Measure Delete dialog appears, as depicted in Illustration 15.


Illustration 15: Confirm Measure Delete Dialog

29.  Click Yes.

The Unit Sales measure disappears from the Measures folder.

30.  Perform steps 27 through 29 above for all members of the Measures folder, except the new one we created above, Distinct Customers.

Our DISTINCT_CUSTOMERS cube will have no measures except the DISTINCT COUNT measure we have created. The idea here is that, once we have finished designing our new cube, it will contain only this single measure and an identical set of dimensions to those found in the cube whose performance we are intent upon improving (in the case of our exercise, the Sales sample cube). It is far simpler, since our Sales cube has already been designed, to clone it, as we have, and eliminate the unwanted objects to achieve our ends, than to build a new cube from scratch. We are also assured, using this approach, that the dimensions will, indeed, precisely mirror those in the original Sales cube, a requirement important to our achieving success.

Let's clear out any remaining, unneeded objects in the DISTINCT cube.

31.  Expand the Calculated Members folder below the Measures folder in the cube tree, if necessary.

32.  Using the same approach taken with each of the measures we deleted above, delete each of the calculated members.

33.  Expand the Actions folder below the Measures folder in the cube tree, if necessary.

34.  Using the same approach taken with each of the measures we deleted above, delete any actions that appear.

The DISTINCT_CUSTOMERS cube is now prepared structurally for its intended use. Let's process the cube to activate our changes, and to finalize its preparation for its role in optimizing distinct count operations.

35.  Select Tools --> Process Cube to initialize the processing steps, as shown in Illustration 16.


Illustration 16: Initiating Cube Processing ...

The Save the Cube dialog appears, informing us that the cube must be saved before we can proceed.

36.  Click Yes, to allow processing to proceed, as depicted in Illustration 17.


Illustration 17: Save the Cube Dialog

A message box appears, stating that the cube has no aggregations, and asking if we wish to design them at this time, as shown in Illustration 18.


Illustration 18: Aggregations Message Box - Just Say "No"

37.  Click No to skip designing aggregations at present.

The Select the Processing Method dialog appears.

Full Processing is the default, and only, option, as the cloned cube has not been processed since the structural changes we have made to it.

The Select the Processing Method dialog appears, as depicted in Illustration 19.


Illustration 19: The Select the Processing Method Dialog

38.  Leaving settings at default, click OK.

Processing begins, and runs rapidly, as evidenced by the Process viewer's presentation of processing log events in real time. The Processing cycle ends and the success of the evolution is indicated by the appearance of the Processing Completed Successfully message (in green letters) at the bottom of the viewer, as shown in Illustration 20.


Illustration 20: Indication of Successful Processing

39.  Click Close.

We are returned to the Cube Editor. We can now browse the data and see our new Distinct Customers measure in action.

40.  Click the Data tab, if necessary.

On the refreshed Data View, data appears in the default formation, ready for manipulation and review, although we stop here only to view our handiwork. The Data View, depicting the Distinct Customers measure, appears in Illustration 21.


Illustration 21: Our New Distinct Customers Measure in the Data View

We now have a processed cube, consisting of dimensions identical to those that exist in the Sales cube, together with the single distinct count measure, Distinct Customers, that we have created to store our distinct customer count information. We are ready to leave the new cube, and to move to the next step of integrating it into our overall solution for enhancing performance of distinct counts in our Sales data.

41.  Select File --> Exit from the main menu.

The Cube Editor closes, and we are returned to Analysis Manager.



Creating a Virtual Cube to House Our Solution

We will now combine the cubes with which we have been working in this session within a virtual cube. The intent will be to create a "logical" cube that appears, from a browsing and reporting perspective, as a standalone physical cube. We will thus combine functionality into a single logical cube, accruing benefits that we will discuss as we encounter them.

As many of us know already, a virtual cube allows us to broaden both the scope and capabilities of the OLAP data contained within the participating MSAS cubes, while incurring no additional physical storage space requirement. Virtual cubes can be useful in many applications, one of which is the combination of standard cubes for the purposes upon which our practice example is focused.

Let's take the following steps to create the virtual cube to complete the assembly of our solution.

1.  Right-click the Cubes folder.

2.  Click New Virtual Cube on the context menu, as shown in Illustration 22.


Illustration 22: Select New Virtual Cube

The Virtual Cube Wizard Welcome Dialog appears as depicted in Illustration 23.


Illustration 23: The Virtual Cube Wizard Welcome Dialog

3.  Click Next.

The Select the cubes to include in the virtual cube dialog appears.

4.  Double-click the DISTINCT_CUSTOMERS cube, to select and move it from the Available cubes list to the Virtual cube includes list.

5.  Double-click the Sales cube to add it to the Virtual cube includes list, as well.

The dialog appears as shown in Illustration 24, after our selections.


Illustration 24: Select the Cubes to Include in the Virtual Cube Dialog

6.  Click Next.

The Select the Measures for the Virtual Cube dialog appears.

7.  Click the Add all measures (">>") button to add all measures to the Selected measures list on the right of the dialog.

The Select the measures for the virtual cube dialog appears as depicted in Illustration 25, with our selections appearing in the Selected measures list.


Illustration 25: Our Measures Selections Appear

8.  Click Next.

The Select the dimensions for the virtual cube dialog appears. We will select all dimensions for purposes of this lesson.

9.  Click the Add all dimensions (">>") button to add all dimensions to the Selected dimensions list on the right of the dialog.

The Select the dimensions for the virtual cube dialog appears as shown in Illustration 26, with our selections appearing in the Selected dimensions list.


Illustration 26: Our Dimensions Selections Appear

10.  Click Next.

The Finish the Virtual Cube Wizard dialog appears.



11.  Type the following into the Virtual Cube Name box:

Customer Sales

After we create a virtual cube, we must process it before client applications can browse it. The necessary internal links to the specified dimensions and measures in the underlying cubes are established through processing the virtual cube. While the linking operation that is involved in processing is typically quick in itself, we need to keep in mind that initialization of processing of our virtual cube will automatically trigger the processing of any underlying cubes that themselves require processing. This can add significant time to the update, and needs to be included in planning when taking the virtual cube route. Ideally, the underlying cubes will be preprocessed, but this is certainly not a requirement, and may not even be the best strategy, in certain situations.

12.  Ensure that the Process Now radio button is selected (the default).

This, the last step of the Virtual Cube Wizard, presents us with the option to process the virtual cube now, or at a later time. The Finish the Virtual Cube Wizard dialog, with our selections, appears as depicted in Illustration 27.


Illustration 27: The Completed Finish the Virtual Cube Wizard Dialog

13.  Click Finish.

After clicking Finish above, we see that the Process dialog appears just as it did when we processed the DISTINCT_CUSTOMER cube earlier, logging the significant processing events, then rapidly presenting a green Processing Completed Successfully message at the bottom of the dialog, as shown in Illustration 28.


Illustration 28: The Process Dialog (Compressed View) Indicates Completion

14.  Click Close.

The Process dialog closes, and the Virtual Cube Editor (a customized version of the Cube Editor, which has no Schema tab, as does the standard Cube Editor) appears, as partially depicted in Illustration 29.


Illustration 29: The Virtual Cube Editor (Compressed, Partial View)

15.  Select File --> Exit from the Virtual Cube Editor main menu to close the Virtual Cube Editor.

We are returned to the main Analysis Manager console window.

16.  Expand the Cubes folder, if necessary, once again.

We see the Customer Sales cube appear, as shown in Illustration 30.


Illustration 30: The New Customer Sales Cube Appears ...

Analysis Manager identifies virtual cubes with the "double" cube icon, as circled in red above.

17.  Exit Analysis Services, as desired.

Verification with MDX

Let's return to the MDX Sample Application, and resurrect our earlier query to execute it against the new virtual cube, taking the following steps:

1.  Start the MDX Sample Application.

2.  Click OK at the Connect dialog.

The MDX Sample Application window appears.

3.  Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

4.  Select the Customer Sales virtual cube in the Cube drop-down list box.

5.  Select File --> Open, and locate and select the query we created and saved as ANSYS32-1 in the earlier section.

The query appears in the Query pane.

6.  Expand Measures -> Measures Level in the Metadata tree, exposing the new Distinct Customers measure (from the DISTINCT_CUSTOMERS cube).

The MDX Sample Application - Metadata tree (left section of the Metadata pane) should resemble that partially depicted in Illustration 31, complete with the measures from both physical cubes combined in the Customer Sales virtual cube displaying in the Metadata tree (left section of the Metadata pane).


Illustration 31: The MDX Sample Application Window (Compressed View)

We will make a couple of modifications to the query, and then execute it against the new virtual cube.

7.  Change the first line of the query (the comment line) to the following:

--ANSYS32-2 Distinct Customer Dataset with Isolated DISTINCT Cube

8.  Select File -> Save As ...

9.  Save the query as ANSYS32-2, to protect ANSYS32-1.

10.  Remove the following (the first calculated member definition within the WITH clause)


MEMBER
       [Measures].[Distinct Customers]
      AS
         'COUNT(CrossJoin({[Unit Sales]}, 
             Descendants ([Customers].CurrentMember, 
                [Customers].[Name])), ExcludeEmpty)'

11.     Change the cube name in the FROM clause from

[Sales]

to

[Customer Sales]

The query is now pointed toward our new virtual cube. The modified query appears as shown in Illustration 32.


Illustration 32: The Modified Query

12.     Execute the query using the Run Query button.

The results dataset appears as partially depicted in Illustration 33.


Illustration 33: The Results Dataset (Partial View)

We notice, after clicking Run Query, that the query runs and data is returned appreciably faster than the initial query we created in the first section of this article. This is because only the new DISTINCT_CUSTOMERS cube is subjected to the intensive portion of processing required to return the detailed set needed by the calculations we have put into place. The DISTINCT_CUSTOMERS cube, with only one measure, is much smaller than the Sales cube, so less processing is necessary to render the contextually important distinct count within our query. As we see, the results are identical to those of our initial query, with all that remains being to format the Avg Sales per Customer calculated measure, if we choose to do so.

There are more actions we can take within our current scenario, where we created the virtual cube, containing the DISTINCT_CUSTOMERS cube, which isolates the Distinct Count, to further increase performance. In our next article, we will examine a further step to leverage the solution we explored in this article to provide a higher degree of performance enhancement within the context of using distinct counts.

Conclusion

In this article, we extended our previous introduction to DISTINCT COUNT, and examined one approach to its efficient use within our applications. We focused upon the optimization of DISTINCT COUNT through the isolation of the distinct count measure into a separate cube, and showed how this "best practice" can help us to achieve our objectives with enhanced performance.

As in the other articles of our series, we set the stage by providing a hypothetical business requirement. We then examined a way to meet the requirement with an MDX query that contained DISTINCTCOUNT() in a calculated member, and that used a single cube as a data source. We noted query performance, and set about to improve it via the creation of a separate DISTINCT_CUSTOMERS cube, which we designed to house the distinct count attributes of our solution.

We then "married" the DISTINCT_CUSTOMERS cube to the initial cube data source through the creation of a virtual cube. Finally, we targeted the virtual cube with the query we had set out to improve, as a means of confirming that performance can be enhanced through the forehanded use of an isolated distinct count cube in scenarios with similar business requirements.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved