Power BI integration with Entity Store in Dynamics AX "7" May update
We are announcing the availability of the Power BI integration with Entity store for Dynamics AX "7" with the May-2016 update. Dynamics AX (or AX7 for short) is a subscription based service and an Entity Store is a database that is automatically deployed with each Dynamics AX"7" environment.
We are continuing our investment in Business Intelligence and, with the May update, are making our Power BI integration much stronger with the help of Entity Store. There are several features that will enable great End-to-End (E2E) Power BI reporting experiences in Dynamics AX. You can see a list of features here: https://ax.help.dynamics.com/en/wiki/whats-new-or-changed-in-dynamics-ax-7/#may-2016-platform-features (scroll down to the Analytics area)
In this post, we are going to focus on How power BI integration is enabled by Entity Store. In a future post, we will go deeper into PowerBI authoring features.
Support for PowerBI direct query mode
In the February 2016 release, you could author Power BI reports using OData end points that are exposed via data entities (both Aggregate Data Entities as well as "detailed" or "regular" Data entities). Although this approach is still supported, with The Entity Store, power users can create Power BI reports by using Dynamics AX data that is staged in a SQL database optimized for Analytics. This provides 2 immediate benefits;
- You can leverage PowerBI DirectQuery capability and author reports that execute directly on the Entity Store database. DirectQuery based PowerBI reports reflect in the Entity Store
- You have the ability to author PowerBI reports over larger data volumes than what was possible with
Stage Aggregate measurements into The Entity Store
As you know already, Aggregate measurements are a star schema modeled for analytical scenarios (I've heard that you need to repeat something ten times before it gets noticed - I'm getting there). In the February release, we enabled real-time in-memory Aggregate measurements. Using Real-time aggregate measurements, you can enable embedded charts and KPIs that react to real-time operations on data. You can read the rationale and the journey undertook here: https://ax.help.dynamics.com/en/wiki/transition-from-ssas-cubes-to-in-memory-real-time-aggregate-models-in-ax7/
Real-time aggregate measurements leverage the in-memory Non Clustered Columnstore Index (NCCI) technology. Visuals and aggregate calculations built over Real-time aggregate measurements reflect transactions within seconds.
In the May update, we are enabling Aggregate measurements that can be staged in the Entity Store. Aggregate measurements staged in The Entity Store can be used for near-real time analytical scenarios that require exploring large volumes of data with PowerBI. As a developer, you learnt how to model an Aggregate Measurement for real-time analytics previously, https://ax.help.dynamics.com/en/wiki/modeling-and-using-aggregate-data/
In the May update, we are adding the ability to model Aggregate Measurements that can be staged in The Entity Store. In Visual studio, you can specify "StagedEntityStore " property as the usage property of an Aggregate measurement. This is a new property added in May update in addition to the existing property "InMemoryRealTime".
So why would you model an Aggregate measurement to be staged, why wouldn't you use In-Memory real time aggregate measurements all the time? There are several motivations for using the StagedEntityStore pattern
- There may be large amounts of data that needs to be explored, analyzed.
- If you have Analysis projects that you migrated from AX2012R3 (ie. Cubes), as part of the code upgrade process to AX7, due to complex views and joins present in the schema, query response times may not be acceptable for embedded visuals. And you may not want to refactor them to take advantage of NCCI technology immediately.
- Unlike the AX Operational database schema, because the schema in the Entity Store is modeled specifically for reporting, it is much easier to build new reports off of it.
- Your scenario may not require analytical data to be updated within seconds of an operation. Most PowerBI reports built to enable data exploration would fall into this category. If Data freshness of ten or so minutes is acceptable for your scenario, you could use the staged pattern
If your situation is one of the above, you would choose to stage your Aggregate measurement in the Entity Store and use for Power BI integration.
Scheduling refresh using The Entity Store form
The Entity Store form is a new addition in the May update. You can find the Entity Store form by following the Systems Administration > Setup path. This form may be familiar to some of you who were in the Technical Adoption Program (TAP) under a different name - we will get to that later.
You will see a list of Aggregate Measurements in this form. Is Deployable field indicates the Aggregate Measurements that are candidates for staging in the Entity Store. So Why are some Aggregate measurements not deployable?
If you have Aggregate measurements migrated from AX2012 R3 (Analysis services projects that got migrated as part of the upgrade process), they will remain as not deployable until a developer makes a decision to change the usage property to "StagedEntityStore". This is intentional - we have enabled best practice warnings and errors that are meant to capture some of the common upgrade issues that impact aggregate measurements. They should be addressed before integrating processing with staging to the Entity Store or using NCCI.
As of the May update, the Administrator has to schedule a periodic refresh by selecting the refresh button. The refresh option can be used for a one-time refresh (ie. demo) or to schedule periodically as shown below.
Under the covers, scheduling is done using the AX batch framework such that refresh jobs can be monitored, load balanced and prioritized using the batch framework capabilities.
We only support full refresh as of the May update and will enable incremental refresh in the near future. Eventually in a future update, the system will refresh the Entity Store based on actual usage patterns - so as an Administrator you would need to use this form only as an exception.
Why can't I stage "regular" Entities?
You may be familiar with the feature we introduced in AX2012R3 in May-2016. With this feature, an Administrator can create a new SQL Azure (or SQL Server) database and provide the connection information in the Data management area page. The Administrator was able to choose AX2012 Entities and stage them in the database. We called this database the Entity Store and it could be used for Power BI, Cortana Intelligence Suite integration. You could also access this database using any of the tools that supported T-SQL and it could be used for integrating with other BI tools or for application integration scenarios.
In Dynamics AX”7”, we want to look at integration scenarios closely and optimize the architecture pieces as suited for each scenario. As you are aware, we are embracing Power BI as a key Operational reporting capability. In May update the Entity Store is provisioned with every AX environment is pre-configured for Power BI and Cortana Analytics suite integration.
Aggregate measurements, a star schema (which I must mention again!), is best suited for re-shaping data for Power BI so we have enabled staging for Aggregate measurements into the Entity store.
If you are not using Power BI in your organization, or if you are using the Entity Store for integration with third party tools, you may prefer the “bring your own database” option similar to what is available in AX2012 R3. In an upcoming release, you will be able to stage one or more of the 1700+ Entities in AX7 into your own database. In the “bring your own database” option, you will have the ability to access the entity database using T-SQL and even extend the database by adding more tables yourself.
If you are a value added reseller (VAR) or a partner building PowerBI integration scenarios, you should plan to leverage Aggregate measurements staged in the Entity Store. If you are a VAR working on data integration scenarios or working with other BI tools, you should use the upcoming "bring your own database" feature.