Daniel Wikar Personal blog about Analytics, Big Data, Microsoft and IT

Dynamics AX7 (Rainier) - BI/Analytics Preview

It has been known for a while that Dynamics AX7 (Codename Rainier) would come with significant changes to the BI & Analytics features. Not much has been known about the details but about a week ago Microsoft released a video revealing some of the upcoming news. Thanks to Nurlin Aberra, DynaAX, for the tip.

You can find the actual video here, www.youtube.com/watch?v=jepSqjGkuxE. I extracted some of the most interesting bits into snippets below together with some personal comments.

Perspectives

dax7-bi-preview-02

Personally I only have experience with BI in AX2012 R3 but have been working with the Microsoft BI stack outside of AX since 2007. The experience of AX BI has been a bit so-so especially since we often want to combine the data from AX with historical data and/or data from other sources. I still see the BI features in AX mainly as operational, analytics closer and more integrated to the business processes.

Workspaces

dax7-bi-preview-01

Moving from the role centers into more personalized and process-centric views - a welcome change.

Flavors of reporting in Dynamics AX

dax7-bi-preview-03

Reporting still feels a bit scattered. Interesting to see whether the new BI features in SQL Server 2016 (such as Datazen) will be leveraged in the on-prem version of AX7.

How a developer consumes aggregate data in AX7

dax7-bi-preview-04

“Aggregate Measurements” becoming an alternative to the SSAS cubes. Initally it was unclear which underlying technology is actually used (In-memory OLTP, Tabular Models, etc) but during the Q&A it was “demystified” by talking about the SQL Server columnstore indexes and read-only secondaries. I have a feel this can’t be all to it (same Aggregate Measurements deployed twice to enable Excel self-service analytics?) and it require further detailing ahead.

In-Memory Real-time vs SSAS Cube

dax7-bi-preview-05

Developer view in Visual Studio. Notice the options for both InMemoryRealTime and SSAS Cube.

AX 2012 BI capabilities are GREAT - but hard to implement

dax7-bi-preview-06

Agree to all the boxes in red. Good to see the AX team acknowledging and aiming to fill the gaps.

Aggregate models - Deployment choices

dax7-bi-preview-07

dax7-bi-preview-08

Here we can see the difference between the two deployment options. Nonclustered Columnstore Index (NCCI) or SSAS. Multi-dimensional or Tabular not mentioned - referring to MDX a give-away for MOLAP?

AX7 - Power BI

dax7-bi-preview-09

dax7-bi-preview-10

dax7-bi-preview-11

dax7-bi-preview-12

dax7-bi-preview-13

Not much to mention here apart from it becoming a two-way integration of Power BI. Data out from and reports/dashboards back into AX.

Data extraction / Data mashup

It was once again expressed that if you want to extract data from AX to integrate into an external data warehouse DIXF is the way to go. If you want to consume data irregularly for data mashups exposing queries through OData will still be available much like today.

Comment

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]

create-new-mdx-query-without-measure-member-expression

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

data-view-resultset-without-measure-member-expression

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

datazen-publisher-without-measure-member-expression

#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]

create-new-mdx-query-with-measure-member-expression

And voilà!

data-view-resultset-with-measure-member-expression

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

datazen-publisher-with-measure-member-expression

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)

Comment