Share via

Introducing Entity Store for Dynamics AX 2012 R3

  • Article

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.

NRT_PowerBI

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.

AX2012R3 Area page

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.

target to staging mapping

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.

Define refresh schedule

 

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

Comments

  • Anonymous
    May 08, 2016
    The comment has been removed
  • Anonymous
    May 25, 2016
    Can this feature be used with AX 2012 R3 CU 9
    • Anonymous
      June 12, 2016
      The comment has been removed
  • Anonymous
    June 01, 2016
    Will this functionality become available for Dynamics AX 2012 R1 and/or R2 as well?Greetings,Gertjan
    • Anonymous
      June 09, 2016
      We have no immediate plans to release the capability in R2 or R1 at this point in time
  • Anonymous
    June 03, 2016
    Seems very promising.How aligned is it with the security framework of AX application?Will the users be able to refresh/see only the data they have AX user access to?
  • Anonymous
    June 08, 2016
    Hi Milinda,Do you also have information regarding to the Entity Store DB and AX7?Will it also be available for AX7 in the near future?Kind regards,Gerrit
    • Anonymous
      June 09, 2016
      Hi Gerrit - We are releasing Entity store for AX7 focused on PowerBI integration scenario with May update. It's available now, you can see the "what's new" guide as well as our latest blog post for details
  • Anonymous
    June 24, 2016
    Will Entity Store also work on premises data.. I mean - if we do not have azure?
    • Anonymous
      July 04, 2016
      I could import a module and I can also pull/push data. Now.. there are some datatypes that are not allowed because the create index statement fails. I cannot unmap a field that I do not want to have with. Where is the forum where we write this kind of bugs and ideas?
  • Anonymous
    July 04, 2016
    Hi Milinda, Now I can import some tables from some modules. I just get an error for some of the data types when we are creating the table, because ColumnstoreIndex does not support certain datatypes. It is the MEMO field in contactPerson for the sales and marketing module. I am trying not include it where you can choose the fields, but - anyway.. it gets taken. How or where can I exclude this field?Thanks in advanceThe Martha Skjellerup
    • Anonymous
      August 01, 2016
      I also am disappointed that many of the pre-defined Target Entities can not be published to the Entity Store due to unsupported data types (e.g. varchar(max) for the MEMO field on customer), missing required fields (e.g. ModifiedDate), or other errors. It seems like the AX2012 R3 version of Entity Store is not ready for production usage yet without getting into customizations. I'd like to move towards this approach since I like the idea of having a near real-time repository for reporting and analytics, but seems like using the BI Properties with the Default Cubes may make more sense until these issues are addressed.
    • Anonymous
      August 25, 2016
      Martha I guess you can change the entity just like you do with DIXF, enter the target entity and remove the field or change the mapping to the entity.
  • Anonymous
    July 07, 2016
    Hi!! I'm following the documented procedure. If I try to publish Customer entity to an Azure SQL Database I have the following error. Is there anything else I have to setup? Create failed for Table 'dbo.DMFCustomerEntity'. at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl() at Microsoft.SqlServer.Management.Smo.Table.Create() at Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelper.DMFEntityDBHelper.PublishSchema(String targetConnectionString, String database, String server, String stagingTable, Dictionary`2 additionalColumns, Boolean isDataArea, Boolean forceUpdate, Boolean isColumnStoreIndex)
    • Anonymous
      July 07, 2016
      I have seen that my error is the database tier.. Thank you anyway 'COLUMNSTORE' is not supported in this service tier of the database. See Books Online for more details on feature support in different service tiers of Windows Azure SQL Database. The entity Customer is not published
      • Anonymous
        July 26, 2016
        Workaround for CCI issue: CCI is not supported if the column has Memo field in the table. So for these tables, you can uncheck the CCI option in "Define Stores" to proceed with publish.
  • Anonymous
    July 27, 2016
    Since SQL Server 2012 supports Columnstore indexes, has anyone tried installing the entity DB on SQL Server 2012. https://www.simple-talk.com/sql/database-administration/columnstore-indexes-in-sql-server-2012/
    • Anonymous
      December 21, 2017
      In the white paper they stated that the column store is introduced in SQL2012 but greatly improved in SQL2014..so for sure SQL2012 doesn't contain those improvement that are required for the entity store.
  • Anonymous
    July 24, 2017
    Can this functionality be delivered backported from MS to Dynamics AX 2012 R3 RTM?