06 Jan 2016
Ever since the possibility to create a data source connecting Power BI to an on-premises SSAS Tabular instance the same was requested for Multidimensional (MOLAP) cubes and now the time has finally come!
Details on how to connect it through Power BI Desktop can be found here: Connect to SSAS Multidimensional Models in Power BI Desktop
Setting up the Power BI Gateway: Manage your enterprise data source - Analysis Services
14 Dec 2015
Since the latest release Datazen is now able to use MDX queries generated through SQL Server Management Studio. Christopher Finlan wrote about it is his blog post New Datazen Server Build Brings some MDX Magic and no use repeating the same here so I’ll just post the link as a reblog.
Happy dashboarding!
12 Dec 2015
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
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
Moving from the role centers into more personalized and process-centric views - a welcome change.
Flavors of reporting in Dynamics AX
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
“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
Developer view in Visual Studio. Notice the options for both InMemoryRealTime and SSAS Cube.
AX 2012 BI capabilities are GREAT - but hard to implement
Agree to all the boxes in red. Good to see the AX team acknowledging and aiming to fill the gaps.
Aggregate models - Deployment choices
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
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.
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.
08 Jul 2015
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)