Datazen and Analysis Services - How to create a Data View

Background

Datazen supports Analysis Services as a data source but creating the data views isn’t as straight forward as one could expect. Details on how to write queries against Analysis Services are pretty scarce in the official documentation but the following is written in the section about Data Providers.

Data View Query -The MDX query to perform. Must return a single table of data. Any Dimension hierarchy which needs to be returned as part of the view must be expressed as a defined Member in the MDX Query.

This was still not crystal clear to me and it took a couple of tries to achieve the expected result set. Based on my experiences I wrote a brief tutorial on creating a working query below using Adventure Works.

Basic query

I started off by using a regular query showing the sales by month with the measure on the column axis and the dimension on the rows.

SELECT { [Measures].[Reseller Sales Amount] } ON COLUMNS
,  NON EMPTY { [Date].[Fiscal].[Month] } ON ROWS
FROM [Adventure Works]

The resulting result set only contains the measures and hides away the dimension members.

And as a result the dimension members isn’t accessible as a column in Datazen Designer either.

Query with Member

As stated in the documentation Datazen expects to receive everything as members, even the dimensions. In order to include also the dimension members in the result set we’ll have to rewrite the query as below.

WITH MEMBER [Measures].[Month] AS [Date].[Fiscal].CurrentMember.NAME
SELECT { [Measures].[Month], [Measures].[Reseller Sales Amount] } ON COLUMNS
,  NON EMPTY { [Date].[Fiscal].[Month] } ON ROWS
FROM [Adventure Works]

And voilà!

Now we also have the months in Datazen Designer ready to be used in the dashboard element data properties.

I hope this will help someone get started using Datazen with Analysis Services.

For more information on defining query-scoped members see this article on MSDN: Creating Query-Scoped Calculated Members (MDX)