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]
Would result in a dataset which did not include the members from the product dimension (not great!).
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]
However with the latest version of Datazen, this is no longer required! This means that you can write the query above in the original format and it will work as expected. For people that use management studio or report builder to create their MDX queries rather than hand coding it this now means it is possible to use these statements in your Datazen MDX queries.
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.
