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.

Comments

• Anonymous
  May 09, 2016
  The comment has been removed
  • Anonymous
    June 20, 2016
    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.
• Anonymous
  July 04, 2016
  Hi!Can we see more detail on how to create the Reader for Machine Learning?
  • Anonymous
    July 18, 2016
    Sure,Here's a link about the import data module itself: Import Data ModuleYou'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.
    • Anonymous
      January 13, 2017
      Hello, Is this weekly sales projection machine learning experiment available as a template in samples?
      • Anonymous
        January 13, 2017
        My last question would be if we can connect the entity store database from Dynamics 365 or this is just for Ax 2012?
• Anonymous
  January 13, 2017
  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
  • Anonymous
    January 13, 2017
    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.
  • Anonymous
    January 13, 2017
    The comment has been removed
    • Anonymous
      January 17, 2017
      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!
• Anonymous
  March 28, 2017
  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