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.

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: http://download.microsoft.com/download/2/4/2/242608B8-7F6A-4A59-8AAF-545EBE5166BA/Entity store Microsoft Dynamics AX 2012 R3.pdf


Comments (17)

  1. Zeeshan says:

    Hi,
    Can we use same entity store DB to be populated from different AX databases? like we are running AX in distributed environment, on AX DB is in US location and other in UK location, both works independently as both have their own DB and AOS. can entity store DB help in consolidating two DBs.

  2. Bharat Patel says:

    Can this feature be used with AX 2012 R3 CU 9

    1. Hello Bharat,

      This feature requires both Kernel and application Hot Fixes available post CU10, so they will be in the next CU11.

      Regards,
      @BertrandCaillet

  3. Gertjan van der Linden says:

    Will this functionality become available for Dynamics AX 2012 R1 and/or R2 as well?

    Greetings,

    Gertjan

    1. We have no immediate plans to release the capability in R2 or R1 at this point in time

  4. Alex__F says:

    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?

  5. Gerrit van Olst says:

    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

    1. 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

  6. Will Entity Store also work on premises data.. I mean – if we do not have azure?

    1. 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?

  7. 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 advance
    The Martha Skjellerup

    1. SHietpas says:

      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.

    2. AndersDahlin says:

      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.

  8. 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)

    1. 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

      1. VijayKumar K says:

        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.

  9. Diliprnair says:

    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/

Skip to main content