Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jan 10, 2005

Introduction to MSSQL Server 2000 Analysis Services: Distinct Count Basics: Two Perspectives - Page 5

By William Pearson

Rendering Distinct Counts Using MDX

We now have a set of "answers" that we can attempt to replicate in direct MDX. Let's initialize the MDX Sample Application, as a platform from which to perform our practice exercises, taking the following steps:

1.     Start the MDX Sample Application.

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

Click for larger image

Illustration 14: 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.  Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

4.  Select the Warehouse cube in the Cube drop-down list box.

5.  Click File --> New to open a blank Query pane.

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


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

We will begin creating our query with a focus on returning results in the same general formation as the Data View we left in the Cube Editor. We will retrieve the Warehouse Profit and Product Count measures, as pictured in Illustration 13 above. Next, we will attempt to add a calculated measure that we craft directly in MDX, to replicate the distinct count information we obtained with the Product Count measure that we created in Analysis Manager earlier. This will afford us a side-by-side comparison between our "MDX solution" and the "Analysis Manager" approach we took in the last section.

1.  Create the following new query:


-- ANSYS31-1 Initial Attempt at Distinction
WITH MEMBER 
   [MEASURES].[ProdCount] 
AS  
   'DISTINCTCOUNT({[Product].MEMBERS})'
SELECT
   { [MEASURES].[Warehouse Profit], [MEASURES].[Product Count],
[MEASURES].[ProdCount] } ON COLUMNS,
   {[Product].CHILDREN} ON ROWS
FROM 
   [Warehouse]

The above represents an attempt to meet the information consumers' objectives - with what appears to be the straightforward use of the DISTINCTCOUNT() function. This might seem intuitive to a practitioner who has given up on the handful of non-working or nebulous examples that can be found on the web, (and which happen to be about all we seem to have as a basis for learning MDX, in many instances). While this approach ultimately fails to provide the desired solution, as we shall see, it should not be surprising that we might attempt this, given the definition in the Books Online, not to mention the words used in the name of the function itself. (Most will agree, also, that it is better to attempt it now, than when under the gun of an employer or a hurried client.)

The calculated member ProdCount embodies the function. I named it ProdCount to distinguish if from Product Count, the measure we created while within the user interface in the earlier section, which I have also decided to present within the results dataset for comparison purposes. Warehouse Profit is also presented to align with our Data View as we left it in the last section.

2.  Execute the query using the Run Query button.

The results dataset appears as shown in Illustration 16.


Illustration 16: The Results Dataset - DISTINCTCOUNT() Approach

3.  Save the query as ANSYS31-1.

It doesn't require a huge leap of logic to conclude that the ProdCount calculated measure is generating a transaction count. The count is correctly "distinct," within its own (actual) meaning, but not at all what the information consumers have requested in our practice example.

Having seen why the "intuitive" approach is lacking, let's resort to another, more cumbersome approach, which results in the distinct product values that we seek.

4.  Create the following new query:


-- ANSYS31-2  Distinction at its Finest 
WITH MEMBER
   [MEASURES].[CalcCount]
AS
   'COUNT(CROSSJOIN({[MEASURES].[Warehouse Profit]}, DESCENDANTS
    ([Product].CURRENTMEMBER, [Product].[Product Name])), EXCLUDEEMPTY)'
SELECT 
   {[MEASURES]. [Warehouse Profit], [MEASURES].[Product Count], [MEASURES].[CalcCount] } 
      ON COLUMNS,
   [Product].CHILDREN ON ROWS
FROM
   [Warehouse]

The above "attempt at distinction" is embodied by the calculated measure CalcCount, named, again, simply as a means of distinguishing it from the measure we created in the Cube Editor, and which we include once again for comparison purposes.

The above approach may not have been the initial impulse that many of us had in tackling what seemed to be a straightforward replication of the Data View we saw earlier. What we are doing, in short, with the CrossJoin() function is marrying the Warehouse Profit values with the products, and returning (thanks to EXCLUDEEMPTY) a count of the non-empty pairings. The Descendants() function builds in flexibility, allowing us to apply the logic equally well to a group of products as to the full set of products. The key to this is the selection of the current member's descendents, adding the "relativity" that so pointedly underscores the power of the .CurrentMember function.

5.  Execute the query using the Run Query button.

The results dataset appears as shown in Illustration 17.


Illustration 17: The Results Dataset - Distinction Attained

6.  Save the query as ANSYS31-2.

The values for the new measure are in alignment with those of the measure we created in the Cube Editor. (All that remains to make the measures identical is the addition of formatting syntax).

7.  Exit the MDX Sample Application and Analysis Manager when ready.

Conclusion

In this article, we introduced the concept of distinct counts, discussing why they are often a requirement in our multidimensional analysis efforts, and those of the information consumers whom we support. In our introduction and overview, and throughout our examination of the objects and MDX syntax we explored to achieve our illustrative ends, we highlighted some of the challenges that are inherent in distinct counts.

We performed practice exercises, to illustrate solutions for hypothetical business needs that called upon the use of a distinct count capability, obtaining exposure to the options afforded us by the MSAS user interface, as well the MDX syntax involved with using the alternative solutions that we proposed.

We now have a basis in distinct counts that will allow us to examine more detailed nuances surrounding the capability. In subsequent articles, we will examine specific performance considerations inherent in the production of distinct counts, as well as options that are available to tune our efforts for more efficient operation. The need for distinct counts is a fact of business life, and mastery of the costs and results of this vital capability represent a unique opportunity to add another tool to our MSAS skill sets.

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

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date