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. 

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. 

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.

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.