Solving Datazen MDX Query Limitations
As I mentioned in a previous blog, Microsoft added Datazen to its arsenal of data visualization tools. Datazen, of course, comes with its own pros and cons. In this blog, you’ll see how you can get around a Datazen limitation when connected to an SSAS data model.
Scenario: You create a Datazen dashboard that sources data from an SSAS MD or Tabular model. As usual, you need a query that slices some measures by dimension attributes, such as the query below:
select {[Measures].[Sales Amount],[Measures].[Order Quantity]} on 0,
[Product].[Category].[Category].Members
on 1
from [Adventure Works]
While this query works fine in SSRS, when Datazen runs the query it silently drops the dimension attributes from the result. You might attempt to rewrite the query as follows:
select [Product].[Category].[Category].Members * {[Measures].[Sales Amount],[Measures].[Order Quantity]} on 0
from [Adventure Works]
But then you get:
The query cannot be prepared. The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension…
Solution: The problem is that Datazen doesn’t support dimension attributes in the query resultset. This limitation is overly restrictive but there is a workaround by changing the query to return the desired dimension attributes as calculated members:
with
member CategoryName as
iif ([Measures].[Sales Amount]=0, null, [Product].[Category].CurrentMember.Member_Caption)
select {CategoryName, [Measures].[Sales Amount],[Measures].[Order Quantity]} on 0,
[Product].[Category].[Category].Members
on 1
from [Adventure Works]
Notice that the calculated member uses the Iif operator to check if a product category has data so that the query doesn’t return all categories whether they have data or not.
UPDATE 7/23/2015
Microsoft released an update that fixes this limitation. Now when you go through the wizard, there is a step that allows you to select which dimension properties you want in the results.
I think the Microsoft decision to make Power BI open will be a paradigm shift for the BI industry. Besides the Push API which are already published, Microsoft has already indicated plans to make Power BI even more open, such as to allow developers to embed reports in custom web applications. Now, if only Power BI supports on-prem installation …
