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. The database is called an “Entity store” and it’s a database optimized for Reporting purposes.
An existing customer of AX, can create an Entity Store in Azure or within a data center using a SQL Server 2016 or SQL Server 2014.
An 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.
An Entity store enables a user to choose “Entities” (that are built on top of AX tables) to be moved into the Entity store.
An 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.
An 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.
https://www.microsoft.com/en-us/server-cloud/cortana-intelligence-suite
CIS works on 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.
An 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).
An Entity store also benefits 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.
Entity store functionality is released as a hot fix immediately, it will be available with the next cumulative update for Dynamics AX2012 R3.
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
Access the Entity store functionality using The Data Import Export framework area page in AX2012 R3 client.
Use the “Define entity stores” option, to define one or more Entity stores in AX2012R3.
The Administrator needs to create a destination database and to provide the Data Source Name (DSN) definition in the form – this option will not create a new database.
At present the following types of Data bases are supported as destinations for Entity store.
– Azure SQL DB
– SQL Server 2016
– SQL Server 2014
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.
.
Entity store provides the ability to publish and to map entities defined in AX2012 into the store. Once defined, the user has the ability to make changes to mapping where applicable.
The Ax system enables the administrator to create and to 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.
