Enhanced Analysis Services Multidimensional Support in Datazen

Datazen is a great new addition to the MS BI stack which has been greeted with much enthusiasm since the announcement from Microsoft they had acquired it and it would be freely available to use for customers with SQL Enterprise licenses.

However there have been a few complications around how the interface with Analysis services worked – the main one being that only measures on the first axis were included in the query.

For example executing the following basic MDX query:

1 SELECT 
2 NON EMPTY {[Measures].[Internet Sales Amount]} ON 0, 
3 NON EMPTY {[Product].[Product].[Product]} ON 1 
4 FROM [Adventure Works]

Until recently the workaround to get around this involves creating a calculated member which returns the Member_Caption and then placing this on the same axis as the measures. Such as in the example below.

1 WITH MEMBER [Measures].[ProductName] as [Product].[Product].CurrentMember.properties("Member_Caption") 
2 
3 SELECT 
4 {[Measures].[ProductName], [Measures].[Internet Sales Amount]} on 0, 
5 nonempty({[Product].[Product].[Product]}, [Measures].[Internet Sales Amount]) on 1 
6 FROM [Adventure Works]

Firstly you need to connect Datazen server to your SSAS instance – there is a great article on the Microsoft site here which explains the process.

1. When you connect to Analysis Services to create a new dataset you are now presented with the following options. Note the MDX is from the first example which would not have worked previously.

2. In the second screen you need to select the data types and display names for each of the measures. I suggest that it is a good idea to change the names to something which should be as clear as possible during report creation (by default the display name is the same as the Column Name).

3. There’s not much to do on this screen provided that the preview looks correct – you just click Next 🙂

Now the data set is ready to be used in reports.