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.
How Artificial Intelligence and Data Add Value to Businesses
Knowledge is power. And the data that you collect in the course of your business
May
Databricks Vs Synapse Spark Pools – What, When and Where?
Databricks or Synapse seems to be the question on everyone’s lips, whether its people asking
May
Power BI to Power AI – Part 2
This post is the second part of a blog series on the AI features of
Apr
Geospatial Sample architecture overview
The first blog ‘Part 1 – Introduction to Geospatial data’ gave an overview into geospatial
Apr
Data Lakehouses for Dummies
When we are thinking about data platforms, there are many different services and architectures that
Apr
Enable Smart Facility Management with Azure Digital Twins
Before I started writing this blog, I went to Google and searched for the keywords
Apr
Migrating On-Prem SSIS workload to Azure
Goal of this blog There can be scenario where organization wants to migrate there existing
Mar
Send B2B data with Azure Logic Apps and Enterprise Integration Pack
After creating an integration account that has partners and agreements, we are ready to create
Mar