Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

How many products do we have in the Adventure Works cube ?

Posted by thomasivarssonmalmo on June 10, 2008

This is my first blog post about Counts in SSAS 2005 cubes. I have an idea of posting MDX code snippets that can serve as a help, mainly for beginners and medium experienced developers. The final goal is to create a good enough newbies education in MDX.
 
Lets get started on MDX and Count from the dimension perspective. This post might also help you with connecting the relational database source world with the SSAS multidimensional world.
 
If you should answer the question of how many products you have in the Adventure Works cube and you should answere this with MDX you  have two opportunities.
 
Check this by counting the number of products in the natural hierarchy within the product dimension like this:
 
WITH MEMBER measures.X AS
   [Product].[Product Categories].[Product].members.count
SELECT Measures.X ON 0
FROM [Adventure Works
 
The answer is 397 products.
 
What answer will you expect if you use the attribute hierarchy instead? The same number of products?
 
WITH MEMBER measures.X AS
   [Product].[Product].Children.Count
SELECT Measures.X ON 0
FROM [Adventure Works] ;
 
The answer is 606 products!
 
What is correct then? If you use TSQL on the dimension tables you will get the same two different answers.
Select Count(*) from DimProduct
 
will return 606 products.
 
Since the product dimension have two other tables in the join, productcategory and productsubcategory you should join all three to check the result.
 
Select Count(*) from dimProduct p Join DimProductSubCategory sc On
p.ProductSubCategoryKey = sc.ProductSubCategoryKey Join
DimProductCategory pc On
sc.ProductCategoryKey = pc.ProductCategoryKey
 
This last SQL will return 397 products.
 
Conclusion: If you have a snowflaked dimension in SSAS2005 you will only see the number of members, in a natural hierarchy, that is the joined number of records in all the tables. If you use the attribute hierarchy you can get a different number of leaf level records if the table, connected to the fact table have records with no parents in the snowflaked dimensions above.
 
In the Adventure Works cube I suspect that this is by design and that the leaf level records without parents are spareparts.
 
Anyway I hope you get an idea of how to check the number of dimension members in a cube and the related numbers in the data source.
 
This started an idea of snowflaking dimension with a top level table that tells if a product or customer is still active or not…
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: