Unlocking your data’s potential with the Dynamics AX 2012 R3 Entity Store and Cortana Intelligence Suite


For those interested in adding intelligence to their AX solutions, the release of the AX2012 R3 entity store today is a big deal. The AX2012 R3 Entity store is a feature that enables Administrators and Power users to export their entities to a data store dedicated for Reporting and Analytics. We call this database “Entity store” but you might call it awesome.

Entity store enables a user to choose “Entities” (that are built on top of AX tables) to be moved into the Entity store. During export the Entity store connector executes any X++ intermediate processing you have already setup and creates staging tables to maximize performance. Using the AX batch framework, you can schedule incremental refresh jobs. This makes it possible to feed data from the AX transactional database to the Entity store on a small batch basis in the background.

Entity store is a natural fit with the rest of the Dynamics Import Export Framework in AX2012 R3. Leveraging this tool, you can export different entities for different companies into separate Entity store database. This is useful if you want to have the power of an analytics database but want to control locality and co-location of your datasets. Or if you prefer, you can pool the data from multiple companies that use the same entity into a single table in the Entity store. This is great for analyzing and comparing performance across business units and divisions in a single dashboard.

You can create the database for an Entity store in Azure or within your data center using SQL Server 2016 or SQL Server 2014. The Entity store can be (and should be) hosted on a separate database server than the one that hosts the AX transactional database. This will isolate the load from your reporting and analytics requests and ensure that you do not need to upgrade the transactional database to the latest versions of SQL server in order to get the most from your Entity store.

When paired with SQL Server 2014 or later, Entity store can take advantage of the In-memory, Clustered Column-store Index (CCI) functionality to optimize reporting and queries. Customers can then use the Entity store with the reporting and analysis tool of their choice.  For instance, simply use Power BI direct query mode along with Entity store to enable high volume, near-real time analytical reporting over large volumes of data as shown below. 

A user can select multiple entities to use in a report from PowerBI Desktop using the Entity Store as a data source.

Create Dashboards in PowerBI via Direct Query to your Entity store, with data from your AX entities.

Or if you prefer, take advantage of the power of Azure Machine Learning with Entity Store by connecting a Reader module in your Azure Experiment to the Entity Store as shown below. 

Leverage AML's "Reader" module to read data directly from the entities stored in Entity store.

You could also use the Entity store to do more advanced processing on your company’s data such as periodically re-training and re-scoring data with machine learning models, or update your own analytics suite via Azure Data Factory.  In the screenshot below you can see the Entity store on the left, at the beginning of a pipeline in Data Factory to extract and compute a forecast of demand data using AX Entity store as an external dataset source.

Leverage the Entity store as an external dataset in Azure Data Factory to administer copy and processing jobs.

Finally, Entity store serves as a great place to land your company’s data for downstream merging with other data sets such as those found in a consolidated Data Warehouse, Data Lake, Analysis Cubes, Hive, Hadoop etc.  Once the data lands in the Entity store, any analytics and processing solution capable of connecting to SQL Server becomes available so that you can unlock the potential of your company’s data.

Entity store can benefit ISVs and partners who specialize in vertical solutions built on AX.  Instead of investing in ETL capabilities to manage and extract data out of AX, they can focus on making their vertical solutions intelligent and create new solutions built on top of advanced analytics and data insights.

All the products we’ve mentioned here (Azure SQL and SQL-DW, Azure Data Factory, HDInsight, Azure Data Lake, Azure Data Lake Analysis Services, and Azure Machine Learning) are part of the Cortana Intelligence Suite (CIS) offered by Microsoft to transform data into intelligent actions.  For an overview of capabilities offered by CIS please see here: https://www.microsoft.com/en-us/server-cloud/cortana-intelligence-suite/overview.aspx.

Learn more about the entity store here: https://blogs.msdn.microsoft.com/dynamicsaxbi/2016/05/02/introducing-entity-store-for-dynamics-ax-2012-r3/

You can locate the hotfix using Microsoft Dynamics Life Cycle Services (http://lcs.dynamics.com) by searching for issues with the keyword “Entity store” or by entering the knowledge base (KB) article number “3147499”.

You can download and install the hot fix using the steps described here: https://technet.microsoft.com/en-us/library/hh335183.aspx

This is a major investment for Microsoft going forward and we would love for you to start using the Entity store feature right away. 

Comments (11)

  1. Zeeshan says:

    Hi,
    Can we use same entity store DB to be populated from different AX databases? like we are running AX in distributed environment, on AX DB is in US location and other in UK location, both works independently as both have their own DB and AOS. can entity store DB help in consolidating two DBs.

    1. RJHanson says:

      Yes, the choice of the Entity Store DB server in AX2012r3 with hotfix is entirely up to you. What you might consider is running two databases within the same Azure SQL Server, or within the same sqlserver on an Azure VM, or within your own premises SQL-server instance. Then you prevent risks and potential problems of altering schemas in one AX instance (in-congruent with the other instance), accidentally dropping the wrong or old data from another instance of AX, and retain full control of any legal requirements you might have for data storage, retention, encryption etc.

      In this environment you’d have two databases on one server, with two entity store DBs. Then for doing the joins, you’d connect to both databases within the same server to perform joins / aggregate data etc. I would recommend performing these joins either within the SQLServer itself (I highly recommend SQLServer2016 or Azure SQL), or via extracting the data out of the entity store into HDInsight etc. It all depends on what you want to do with the exported data downstream. To organize the extracts, joins, and downstream processing, I would recommend using Azure Data Factory to manage, monitor, and coordinate the data paths.

  2. Marcia Pelaez says:

    Hi!
    Can we see more detail on how to create the Reader for Machine Learning?

    1. RJHanson says:

      Sure,

      Here’s a link about the import data module itself: Import Data Module

      You’ll notice that the sample database I created was an Azure SQL database, although you can use a on-premises database as well with a gateway installed to allow AML to “reach down” into your datacenter to run the SQL query you enter into the module itself. In my example I also used the simplistic SQL Server authentication instead of Azure Active Directory (which could be used as well) to authenticate the connection. The reason I did so was to keep the complexity down for my example, but there are lots of ways to secure your connection strings and accounts (such as Azure Key Vault) that can access the database that you provision and establish for use as the entity store.

      1. Marcia Pelaez Gonzalez says:

        Hello, Is this weekly sales projection machine learning experiment available as a template in samples?

        1. Marcia Pelaez Gonzalez says:

          My last question would be if we can connect the entity store database from Dynamics 365 or this is just for Ax 2012?

  3. Peter Nordin says:

    This is really interesting. I Would like to hear from you about use cases for integration with entity store and a Data Warehouse.
    My business case is the following.
    We’re going to use Dynamics AX as our business system along with a separate Data Warehouse with information from both Dynamics AX and other source applications. The DW is based on data vault, star schemas and tabular models, using SQL Server 2016 Enterprise on premise. On top we’re using Power BI.
    Data entities in Dynamics AX need to be used for both reporting in Dynamics AX and in the DW. For example dimensions like Products, customers, fact as sales etc.
    My approach have been up to now is reporting in Dynamics AX on a separate entity store and to use application integration, message based event driven architecture from Dynamics Ax to store data in the DW for other reporting needs. But, I see a lot of use in modelling entitys on data from Dynamics AX and then use it for several purposes, not just for reporting on data stored in Dynamics AX. For example, modelling an entity for dimension product in the entity store and then use it as a conformed dimension in the DW.
    A solution could be to use entity store to integrate on a database level between Dynamics AX and the DW. Near real time, transparency and agility (time-to-market) is the pros I can see for the moment.
    Could you collaborate on that? Is this a pattern that are meant to be used? maybe my question is too vague, feel free to ignore it in that case.. 😉
    Kind regards!
    Peter Nordin

    1. AksheyGupta says:

      Peter, have you heard of common data service? https://powerapps.microsoft.com/en-us/blog/announcing-powerapps-common-data-model/  and https://www.youtube.com/channel/UCGfWR2ekfRFckLjev6eQYLg. We are doing exactly what you describe – modeling AX entities and then using it for reporting and integration etc.

    2. RJHanson says:

      Hi Peter,

      Yes, your use case makes perfect sense and is one of the reasons the entity store is useful. If you’re using SQLServer as your technology for the DW in your solution, you may want to have the entity store be one of the databases on that server. Then you can treat the entity store’s tables as “staging tables” for your DW. With both DBs on the same server, you can run your transforms as you import entity store data into your DW at high speed (since the DBs will share a server). Your thinking around exporting entities from AX into entity store, then leveraging those tables as dimension tables in a star schema is definitely one of the scenarios we intended for the entity store.

      As Akshey points out, you can think of the CDM is an expanded Microsoft-managed, better-connected, extensible entity store, scaled-up and with connections to PowerApps, Dynamics 365 for Operations, Dynamics CRM, and a *lot* more. When you “create a database” in CDM, it’s like creating a entity store under the covers. You can then wire up Flows to copy data and updates into CDM from a lot of places. You can see more about Microsoft’s approach to the CDM here: https://blogs.msdn.microsoft.com/rojythomas/2016/10/20/pa-flow-cdm/.

      But if you prefer to go the manually managed route and write your own code, yes, using entity store to stage to a DW, decorating with add’l data, caching with tabular models, and visualizing with PowerBI is a great way to build a modern BI solution.

      Hope that helps.

      1. Peter Nordin says:

        Hi,
        Thanks’ for your valuable comments. I will certainly look into it (CDM and and/or using entity store as data stage tables in the DW). The integration is going to be hard-coupled integration (data integration vs. more lose-coupled message based integration), but the benefits all together seems to be greater. Thanks’ again!

  4. Hemanth says:

    Hi,
    I want to go with the EntityStore created from the AX and use it as source fro my reports.
    Since we incrementally refresh and load the target can I use this aggregate data for reports.
    Will they act as DW.

    Thanks,
    Hemanth

Skip to main content