Introducing Entity Store for Dynamics AX 2012 R3
We are excited to announce the immediate availability of Entity Store for Dynamics AX 2012 R3 today. So, what is an Entity store?
Entity store is a feature that will enable an Administrator or a Power user to create a data store dedicated for Reporting and Analytics. We call this database “Entity store” and it’s a database optimized for Reporting purposes.
As an existing customer of AX, you can create an Entity Store in Azure or within your data center using a SQL Server 2016 or SQL Server 2014. Entity store can be (and should be) hosted on a separate database server than the one that hosts the transactional database. Therefore, you do not need to upgrade the transactional database to the latest versions of SQL server in order to deploy Entity store.
Entity store enables a user to choose “Entities” (that are built on top of AX tables) to be moved into the Entity store. Entity store enables scheduling incremental refresh jobs with the use of AX batch framework. This makes it possible to feed data from AX database to the entity store on a small batch basis in the background.
Entity store uses In-memory, Clustered Column-store Index (CCI) functionality built into SQL server to optimize reporting and queries. Customers have the ability to use Power BI direct query models along with Entity store to enable high volume, near-real time analytical reporting over large volumes of data as shown below.
Cortana Intelligence Suite (CIS) is a collection of tools and services offered by Microsoft that transforms data into intelligent actions. CIS offers predictive capabilities and a set of tools focused towards business users and developers that enables learning from data. For an overview of capabilities offered by CIS please see: https://www.microsoft.com/en-us/server-cloud/cortana-intelligence-suite
CIS works on data – data in Azure stores such as Azure DB, Azure DW or Azure Data Lake as well as on-premise data sources such as SQL Server. Entity store enables AX customers to use their data effectively with CIS capabilities by removing the drudgery of developing ETL scripts, transforming and (re)staging data. The Entity Store, together with CIS tools enable customers to complement AX data with data from devices (ie. MES, Sensors) as well as other systems (ex. CRM).
Entity store also benefits a large number of ISVs and partners who specialize in vertical solutions built on AX – as opposed to investing in ETL capabilities to map and move data out of AX, they have the ability to focus on building vertical solutions.
This is a major investment are for us going forward and we love for you to start using the Entity store feature right away.
How to install
Entity store functionality is released as a hot fix immediately, it will be available with the next cumulative update for Dynamics AX2012 R3.
You can download the hotfix via the following link: https://mbs2.microsoft.com/Knowledgebase/KBDisplay.aspx?scid=kb;EN-US;3147499 (requires access to partner source).
You can download and install the hot fix using the steps described here: https://technet.microsoft.com/en-us/library/hh335183.aspx
A Quick overview of functionality
Entity store functionality can be accessed using The Data Import Export framework area page in AX2012 R3 client.
Using the “Define entity stores” option, the administrator can define one or more Entity stores in AX2012R3. Administrator needs to create a destination database and provide the Data Source Name (DSN) definition in the form – this option will not create a new database. At present following types of Data bases are supported for as destinations for Entity store.
- Azure SQL DB
- SQL Server 2016
- SQL Server 2014
It must be noted that Entity Store functionality leverages SQL Server Column store Index (CCI) technology which is intended for Data warehouse scenarios. Currently CCI is supported only in Premium editions of Azure SQL DB and BI editions or above of SQL Server versions.
AX2012R3 ships over 450 entity definitions in CU10. Any of these entities as well as entities created by partners and users can be staged in an Entity store. See here< > for a list of entities available in AX2012R3.
Entity store provides the ability to publish and map entities defined in AX2012 into the store. Once defined, the user has the ability to make changes to mapping where applicable.
System enables the administrator to create and manage refresh schedules to keep the data in sync with the AX operational database. At the time of creation of refresh schedules, user can choose whether to refresh data incrementally (ie. add/update only the records that got modified in AX) or to refresh in full. Data refresh schedule is triggered by the AX batch framework and the system provides options to review upcoming refresh jobs or to see the historical execution of jobs.
Complete functionality of Entity store can be found in our white paper published here: download.microsoft.com/download/2/4/2/242608B8-7F6A-4A59-8AAF-545EBE5166BA/Entity store Microsoft Dynamics AX 2012 R3.pdf