Discover the New Dynamics AX 2012 R3 Entity Store
In practically every Microsoft Dynamics AX installation, in any of its versions, we face the need to expose certain application data so that other systems can use it. Most often, though not only, reporting and Business Intelligence tools. To achieve this, we design more or less complete data warehouses and ETL processes to transform the transactional and normalized Microsoft Dynamics AX database into a data source better suited for efficient data reading.
To avoid this extra work and let us focus our time on the final solution, a new feature has been released for Microsoft Dynamics AX 2012 R3 called Entity Store, allowing us to expose Microsoft Dynamics AX entities and use all the new capabilities in Microsoft SQL Server 2016, such as in-memory columnstore indexes and tools designed to optimize reporting and enable “near-real time” analytics.
This entity store is a new database independent from the transactional one, so besides enabling independent optimization and scaling, it also allows us to always run the latest SQL version without migrating the AX transactional server, making it easier to adopt new features. It is currently compatible with SQL Server 2014 and 2016, as well as Azure SQL.
The tool is integrated and configured in the application (currently as a standalone hotfix, but it will be part of the next cumulative update for AX 2012 R3) and uses Data Import/Export Framework (DIEF) to coordinate entity refresh, which can be configured with independent schedules.
This is a first version to introduce the feature and provide the functional foundation, but Microsoft has already announced that they are investing in this Data Entity, so we can expect future improvements. It promises to become the integration gateway between our systems and the rest of Microsoft’s reporting and business intelligence technologies, which are rapidly evolving together with Azure and SQL Server.
For example, Microsoft already suggests these entities are ready to work with Azure Machine Learning, Power BI, Azure Data Factory and classic BI tools (such as traditional data warehouses, data lake for AX, …) and Big Data (Hadoop). All these tools, already available in Azure (Azure SQL, SQL-DW, Azure Data Factory, HDInsight, Azure Data Lake, Azure Data Lake Analysis Services, and Machine Learning), are also part of the Cortana Intelligence Suite (CIS), so this new consolidated data store is just the beginning ;)
All available information in the following Microsoft white paper:
The patch can be downloaded from LCS by searching for “Entity store” or KB3147499.
* Images in this post are taken from the Dynamics AX Business Intelligence blog!!