SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

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.