Thomas.I Microsoft BI & Analytics

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

SSAS Cube Testing

Posted by thomasivarssonmalmo on January 20, 2010

We seldom mention testing in SSAS development but that is a common requirement in larger companies to document and verify the values in the cubes.
 
Can you mention any SSAS book published that discuss testing? I cannot. Still in everyday life of SSAS projects the results needs to be tested and verified.
 
How can we do that with an independent tool? Time for some TSQL code that has been part of SQL Server since the 2005 version. I will use the AdventureWorksDW database that is the source for the SSAS 2008 Adventure Works cube project.
 
The common way is to use the Group By clause and write something like:
 
Select pc.EnglishProductCategoryName as ProductCategory,ps.EnglishProductSubCategoryName as ProductSubCatgory,
p.EnglishProductName as Product, SUM(f.SalesAmount) as Sales, SUM(f.OrderQuantity) as OrderQty
From dbo.FactInternetSales f
Join dbo.DimProduct p
On f.ProductKey = p.ProductKey
Join dbo.DimProductSubCategory ps
On p.ProductSubcategoryKey = ps.ProductSubcategoryKey
Join dbo.DimProductCategory pc
On ps.ProductCategoryKey = pc.ProductCategoryKey
Join dbo.DimTime t
On f.OrderDateKey = t.TimeKey
Where t.CalendarYear = 2003
Group By pc.EnglishProductCategoryName,ps.EnglishProductSubCategoryName,p.EnglishProductName
Order By pc.EnglishProductCategoryName,ps.EnglishProductSubCategoryName,p.EnglishProductName
 
You can see the result in the picture below. All the values for sales and orders have the granularity of the lowest level, product.  Probably you will export this result into Excel and build the aggregations there.
 
 
 
If you prefer to have some aggregated values, in TSQL, for all products, product category, product subcategory and product you can use another option, OVER and Partition By with the SUM function. The first Empty Over() will return the aggregated value for all products. For each other level you enter the attributes in the Partition By part.
 
 
Select  pc.EnglishProductCategoryName as ProductCategory,ps.EnglishProductSubCategoryName as ProductSubCatgory,
p.EnglishProductName as Product,
SUM(f.SalesAmount) Over() as TotProduct,
SUM(f.SalesAmount) Over(Partition By pc.EnglishProductCategoryName) as SalesProdCatTot ,
SUM(f.SalesAmount) Over(Partition By pc.EnglishProductCategoryName,ps.EnglishProductSubCategoryName) as SalesProdSubCatTot,
SUM(f.SalesAmount) Over(Partition By pc.EnglishProductCategoryName,ps.EnglishProductSubCategoryName,p.EnglishProductName) as SalesProdTot
From dbo.FactInternetSales f
Join dbo.DimProduct p
On f.ProductKey = p.ProductKey
Join dbo.DimProductSubCategory ps
On p.ProductSubcategoryKey = ps.ProductSubcategoryKey
Join dbo.DimProductCategory pc
On ps.ProductCategoryKey = pc.ProductCategoryKey
Join dbo.DimTime t
On f.OrderDateKey = t.TimeKey
Where t.CalendarYear = 2003
 
The result is the one below. I can only show a part of the result set
 
. 
 
If you do not like to have repeated values for the product level you can add the Distinct key word after the select clause.
The result is the following below.
 
 
 
 
In the SSAS cube you can run this MDX:
Select {[Measures].[Internet Sales Amount]} On 0,
Descendants([Product].[Product Categories],[Product].[Product Categories].[Product],SELF_AND_BEFORE) On 1
From [Adventure Works]
Where ([Date].[Calendar Year].&[2003])
 
The result for helmets are the following:
 
 
The Over Clause with Partion By should also be a great tool if you do not have a cube but would like to add aggregates to Reporting Services reports.
Advertisements

3 Responses to “SSAS Cube Testing”

  1. Miky Schreiber said

    Indeed, it’s a common task. After you’re doing it over and over again I wish I would have a tool that does that.Miky Schreiber

  2. Unknown said

    What we do is that we build a report that gets the values from SQL and SSAS.The values from SSAS are substracted from SQL. if the absolute difference is less then 0.01 the value is correct other wise the value gives an error. Now we use in Visual Studio the Web test project to test the reports. The web test opens the RDL file and checks if the word ERROR is on it. If not the test succeeds other wise it fails. You can always open that report by hand to check if all is correct.We also deploy this report so that administrators can check if the cubes and databases are in sync.

  3. Thomas said

    Miky: Do you need a tool or can the new additions to TSQL since the 2005 release help?

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: