Thomas.I Microsoft BI & Analytics

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

Archive for the ‘SSAS 2008’ Category

SSAS MemberNamesUnique Property

Posted by thomasivarssonmalmo on June 29, 2010

In the SSAS newsgroup there was a question about removing the & character that will appear when you work with attributes in Management Studio/directly in MDX.
 
The reason why the & character appears is about the MemberNamesUnique-property of the dimension attribute that you can find in BIDS here. That property is set to false as a default and that is why you will see the & before the attribute in the MDX code.
 
I have choosed to use the Sales Reason dimension in the Adventure Works demo cube and the Sales Reason Type attribute since it only has a few members.
 
 
 
In the Adventure Works cube I have changed that property to false in the two last examples when you drag an attribute member to the MDX query window in Management Studio.
 
 
 
 
The two first examples(1-2) shows the default behaviour, MemberNamesUnique = False. The two last(3-4) what will happen if you set that to True.
 

1.   [Sales Reason].[Sales Reason Type].&[Promotion]

2.   [Sales Reason].[Sales Reason Type].&[Other]

3.   [Sales Reason].[Sales Reason Type].[Marketing]

4.   [Sales Reason].[Sales Reason Type].[Other]

I would recommend you to keep the default behaviour with that property set to False because it is harder to have unique member names than key.  City names in Uinited States are not unique on the country level.

Advertisements

Posted in SSAS 2008 | Leave a Comment »

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.

Posted in SSAS 2008 | 3 Comments »

Nothing new in SSAS 2008 RC0 regarding mulitiselect ?

Posted by thomasivarssonmalmo on June 29, 2008

I recently installed SQL Server 2008 RC with SSAS 2008. My first idea was to check if anything have changed regarding multiselect in  SSAS 2005. I have tried many of the examples that Mosha has on his blog but without having seeing any changes compared to SSAS 2005.
 
Mosha also says here that he would return with a solution i february 2008.
 
Edit: Mosha has a comment on this blog post that will tell you more about this subject.
 
Dynamic sets in SSAS 2008 can be a solution. Anyone else out there that have found any news regarding multiselect in SSAS 2008?

Posted in SSAS 2008 | 2 Comments »