About MDXMissingMemberMode in SSAS

I just wanted to write about the important Dimension property called “MDXMissingMemberMode” in SSAS 2005 and 2008.

In SSAS 2000, it was raising “Can’t find dimension member” errors. It used to be a difficult task to handle those errors. The only workaround was to use ISERROR function. Even this function can’t be used in many cases.

Starting from SSAS 2005, new property is introduced to ignore the errors or to raise the errors.

We’ve to be very careful in utilizing this property. Because it is very common tendency to set the property to set to ignore the errors and this case is valid only when you are running the reports.
But this case again bothers you if you have any interface to setup MDX formula. As a common practice in any MDX formula editors or setup interface, Syntax needs to be checked. If it is ignoring the Missing key errors you can’t image the impact on accuracy of results. Because it does not even show you the MDX syntax errors. Hardest part is that anything that you type is considered is taken as the correct MDX.

Example is that instead of [Account].[Net Income] even if you put [Account].[Net Income Net income] or even if you give [Account].Net Income] (Starting bracket is missing) it would accept the MDX and does not raise the error.

I’ve seen so many complaints around this and end user can’t image what’s going on with this.
This property can be setup in 2 places.

1. At Dimension level ( as shown below)
If you setup the MDX Missing Member Mode property at dimension level then it ignores the errors only on this dimension.

2. As a part of connection string
If you specify the below property in your OLAP connection string then it would raise the errors related to missing keys / formula errors in all the dimensions.
MDX Missing Member Mode=Error;

So the best practice is that always setup the Missing member mode property as a part of connection string. In addition to this, wherever you are allowing for formula setup – By default set to raise the error in connection string (Not at dimension level).
Wherever you are displaying the reports – By default set it to ignore the errors but provide flexibility in your OLAP clients or reporting interface to user to click on that option and see if there are any cells which are having formula syntax errors or missing keys.

Hope fully this is helpful.

Strategy Analyzer

I got to review one of the SSAS reporting application called Strategy Analyzer. Strategy Analyzer was developed to provide reporting capabilities on SSAS 2005 / 2008 with a very good User Interface. I would say it is a great product for generating OLAP reports. Mainly for Adhoc analysis and to create good looking Dashboards.
This product is available in 3 modes.
1) On-premise version
2) SaaS version
3) OEM/Web Services version

Those who are looking for buying a OLAP client within their corporate and use it for reporting, this is a good option. Those who do not want to spend the upfront cost & manage the Infrastructure then SaaS option is better. For the vendors who are looking to integrate this application in their application (Involves programming) , OEM version is better.

Usually we do not find the applications, which offer Dashboard setup & rendering in the same version of the application. Mostly Dashboard setup is available in desktop versions and rendering is provided in web version. But they have an excellent UI to do perform both the steps.

They are supporting 5 report types
1.Pivot table (Abilty to generate Charts on top of it)
2. KPI Report (KPI Viewer to see SSAS KPIs)
3. Intelligent Map ( Map based reports along with its performance shown in indicators)
4. External web link can be added to its dashboard and get the output Ex: company stock news.
5. External Reporting services link to render the reporting services report in Dashboard.
6. Process Diagram to attach KPIS in a scorecard.

Other technical highlights are:

1. Support to Load Balancing (Uses Sticky session to ensure tha same server is contacted)

2. Support for Multi-language: It supports around 10 languages now.

3. Read SSAS 2005 functionality such as Perspectives, Measure groups and KPIs

4. Authentication (Anonymous or Basic or Integrated authentication).

5. Attach documents to KPIS.

Thanks,
Ashok

Calculation difference betwen SSAS 2000 and SSAS 2005&2008

I've noticed calculation difference betwen SSAS 2000 and SSAS 2005&2008.
Basically in a parent-child dimesinon, when child member refers its parents in Custom Memebrs
It is returning null in SSAS 2005 & 2008. But it works fine in SSAS 2000.

Case #1 : Created a new record in [AdventureWorksDW2008].dbo.[DimAccount] table with name called “Check This” and its Account Key is 100 (Identify column)

a) Updated the Rollup Operator to ‘~’ (tilde) character

b) Updated the Custom Members field to refer to Notes payable member

Update DimAccount
Set CustomMembers='[Account].[Accounts].&[28]'
Where AccountKey='100'
Here Member formula '[Account].[Accounts].&[28]' refers to Notes Payable

c) Processed Account dimension (Full Process) & Adventure Works cube (Full Process)

Case #1 Result:
a) As expected it displayed the amount of Notes payable against my new member called ‘CheckThis’
Case #2: Updated the Custom Members field to refer to its parent member called ‘Current Liabilities’
Update DimAccount
Set CustomMembers='[Account].[Accounts].&[27]'
Where AccountKey='100'
Here Member formula '[Account].[Accounts].&[27]' refers to ‘Current Liabilities’

Case #2 Result: You would notice that it returned Null value against my member ‘CheckThis’. I am expecting Amount $3,588,650.00

Issue that I've posted & corresponding Microsoft response can be seen from below URL

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=436036

Expert Cube Development with Microsoft SQL Server 2008 Analysis Services

I got to read the newly published book called "Expert Cube Development with Microsoft SQL Server 2008 Analysis Services " by Marco Russo, Alberto Ferrari, Chris Webb".

This is a very good book. this book does not talk about SSAS 2008 concepts but practical experience & expertise of Marco Russo, Alberto Ferrari, Chris Webb.
Most interestingly, they discussed about Calculation dimension to provide flexibility to define the MDX expressions and use rest of the all the dimensions on rows/columns or pages. This is something that I have been using for a long time in my Reporting cube and I am exicited to see about the same in their book.
Another intesting topic is that with SSAS 2005 , it is quite confusing whether to use "CustomRollupColumn" or "MDX Scripts" for custom members. They have niced touched based about the same.
Planing to read further and planning to update the blog with some more intesting things that I find.