Implementing Best Practices For Large Analysis Services Databases

An Excellent demo by Scalability Experts & Microsoft on Implementing Best Practices For Large Analysis Services Databases. I really like this presentation as it suggests best practices at RDBMS level as well as at SSAS level.
http://www.microsoft.com/winme/0905/1000759/default.html

The OLAP Report is now The BI Verdict

OLAP Report is now named as BI Verdict. May be this is to conduct the research on entire BI not just OLAP market place ??
New URL: http://www.bi-verdict.com/

Configuring HTTP Access to SQL Server 2008 Analysis Services on Microsoft Windows Server 2008

A good article from Mike's blog on configuring HTTP Access to SQL Server 2008 Analysis Services on Microsoft Windows Server 2008.
http://bloggingabout.net/blogs/mglaser/archive/2008/08/15/configuring-http-access-to-sql-server-2008-analysis-services-on-microsoft-windows-server-2008.aspx

This article is very detailed & self explanatory.

Analysis Services Roadmap

A good presentation by Siva Kumar Harinath (Senior Test lead from Microsoft) Analysis Services builds confidence that SSAS 2008 Scales better than SSAS 2005.

http://download.microsoft.com/download/4/9/0/4906f81b-eb1a-49c3-bb05-ff3bcbb5d5ae/SQL%20SERVER%202008%20-%20BUSINESS%20INTELLIGENCE%20AND%20ADMIN/Analysis%20Services%20redefined%20with%20SQL%20Server%202008%20-%20Sivakumar%Harinath.pdf

I am excited to see the below slide, which provides high level Road map of SSAS.






In the above image they mentioned that the mission of SQL 2005 has been completed in SSAS 2008. This seems to be true with the below information.

Microsoft Adcenter orignally built their Business Intelligence solution for their online advertising compaings on Microsoft Bing. They shifted their current application from SSAS 2005 to SSAS 2008 and noticed significant performance improvements without any changes and they got more opportunity to tune it further. Following URL contains the complete information on the same.

http://sqlcat.com/whitepapers/archive/2009/09/19/accelerating-microsoft-adcenter-with-microsoft-sql-server-2008-analysis-services.aspx



Below Image clearly confirms that shifting their current app from SSAS 2005 to SSAS 2008 cut down their half oftheir processing time.

Parent-Child dimension hierarchy in SSRS 2008

When I first worked on SQL Server Reporting Server 2000 about 4 years ago. I did not like 2 items.
1) Ability to develop a report on Parent-Child dimension (Ragged hierarchy)

2) Ability to type the MDX and see the output in RS report immediately (Without need to get the field list).

Though my 2nd item is still not possible, but SSRS 2008 allowed flexibility to achieve #1.

Usage of Parent child dimension for Chart of Account or Employee or Company dimension is a very common case. But unfortunately there was no direct method to get the Parent-child dimension hierarchy displayed in single column in SSRS 2000. It is hard to build balanced hierarchy all the time and group them to have drill down from column1 (level 1) to columns 2 (level 2) I am excited to see this feature SSRS 2008 ( I guess 2005 as well) .

Here is the OUTPUT

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.

Display currency symbol based on country

Last week, I got to do a little bit research on displaying different currency symbols based on a country or currency member.

I learnt that SSAS 2005 supports Language () MDX function, which takes care of displaying measures database based on language (currency) settings that are setup in SCOPE() Expression.

1) Hard code language to locale mapping
Using BIDS, I've opened my cube and navigated to Calculation tab Switch to "Script View" and put the following expression

Optin #1: Using Scope expression, in this approach you can include multiple company members.

SCOPE ([Company].&[Germany]);
Language(This) = 2067;
END SCOPE;

SCOPE ([Company].&[UK]);
Language(This) = 2057;
END SCOPE;

Optin #1: Without using Scope expression
In this approach you can specify the locale to language mapping for only one company.
Language([Company].&[Germany]) = 2067;
Language([Company].&[UK]) = 2057;

Option 2: Dynamically provide mapping between language to locale
Option #1 can be made dynamic by creating an attribute on company dimension and storing the Locale ID.

Create an attribute on Company and assign the attribute to Language expression in Calc script

Language(This) = [Company].[Currency Locale ID];

Or
Language(This) = [Company].Currentmember.Properties("Currency Locale ID");

MDX to get the Lineage and All ancestors

Following is the MDX written to get the Lineage (Parents path in terms of key separated by slash) and parent path with name of all members in the dimension.

WITH
MEMBER [Measures].[AncestorNames] AS
Generate
(
Ascendants([Employees].CurrentMember)
,[Employees].CurrentMember.Name
,"->"
)
MEMBER [Measures].[Lineage] AS
Generate
(
Ascendants([Employees].CurrentMember)
,[Employees].CurrentMember.Properties("key")
,"/"
)
SELECT
{
[Measures].[AncestorNames]
,[Measures].[Lineage]
} ON COLUMNS
,{[Employee].[Employees].MEMBERS} ON ROWS
FROM [Adventure Works];

MDX for searching dimensions members

I wrote a simple MDX to list out the Employees whose name contains 'RA' and get the corresponding Parent Key as well.
*************************************************************************
Select
Generate({Filter({[Employee].[Employees].members},Instr([Employee].[Employees].currentmember.Name,"RA")>0)} ,Ascendants( [Employee].[Employees].currentmember))
DIMENSION PROPERTIES [Employee].[PARENT_UNIQUE_NAME] on rows,
{} on columns
From [Adventure Works]
*************************************************************************