Share via

Power BI integration with Entity Store in Dynamics AX "7" May update

  • Article

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".

New usage property in VS

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

  1. There may be large amounts of data that needs to be explored, analyzed.
  2. 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.
  3. 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.
  4. 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.

Entity store form

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.

schedule periodically using Entity store form

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.

Comments

  • Anonymous
    June 09, 2016
    Great!Any clarification on how aligned it is with the security framework of the AX application?Will the users be able to refresh/see only the data they have AX user access to?
    • Anonymous
      June 14, 2016
      Hi Alex - at this point in time, AX security is not reflected in Entity store. User (ie. report writer) has to apply security using PowerBI features. We do want to enable securing PowerBI datasets using AX security in the future. This is something in our roadmap
  • Anonymous
    June 13, 2016
    The comment has been removed
    • Anonymous
      June 14, 2016
      Not sure if I understood the question. Entity store is provisioned with new Dynamics AX instances created since May update. In order to create an Entity store - you do need to upgrade your existing Dynamics AX instance to May-update level. That may involve applying hotfixes etc.
      • Anonymous
        June 24, 2016
        We get a new setup Ax2012 R3 C U10. We might be very close to make it work. We get a message that says: Error executing code: DMFIsInEntityDBPublishAndPublishAll object not initialized. Do you guys experience this? How can we fix it? Thanks in advance :) :)
  • Anonymous
    June 17, 2016
    Where can I confirm the connection information in the Data management area page?
  • Anonymous
    June 30, 2016
    Hi Milinda - Great work and great post. I have a couple questions regarding the ability of storing "regular" entities in an external database. Firstly, would data be versioned (so to allow multiple loads) or will there be just a single version of the entity "staging" data. Secondly, do you have any indication of when will this functionality become available in AX 7? Thanks. Jesmond.
  • Anonymous
    July 21, 2016
    Very interested in this "bring your own database" option. Do you know when this might be available for use?
    • Anonymous
      July 24, 2016
      Hi Chloe, it is available with platform X++ (KB3175368) / binary (KB3175369) update, but there is no documentation on how to configure the connection yet. I tried I lot to get it working but I am not able for now...
  • Anonymous
    September 27, 2016
    Hi, we are an ISV trying to deploy a PowerBI report through LCS. It's based on a DirectQuery connection to an Entity Store which is based on a VM. The report works locally and will deploy to PowerBI.com but the connection is not correctly configured: "This data source can't be accessed by a data gateway". What do we need to do?
  • Anonymous
    October 09, 2018
    Hi, I'm currently working with Entity Store on a current project and have a question regarding the use of InMemoryRealTime and StagedEntityStore. If the Aggregate Measurement is configured as InMemoryRealTime then is there still a need to refresh or schedule the refresh of the measurement? Or will it just repopulate every time the view is called by Power BI?