Part II: Publishing a Dynamics AX 2012 R3 Default Entity

by | Updated December 29, 2017 | Dynamics AX, Set Up, SQL

This blog is a continuation from Configuring the Dynamics AX 2-12 R3 Entity Store.  This is part 2 of a 3-part series:

Part 1Setting up and Configuring the Dynamics AX 2012 R3 Entity Store

Part 2Publishing a Dynamics AX 2012 R3 default entity

Part 3 – Creating and publishing a custom Dynamics AX 2012 R3 entity

When we last left off we had configured the Entity Store but hadn’t published an Entity due to challenges with some of the default entities fields (namely the memo field). For this blog, I’ll be publishing the SalesTable entity since it does not contain the memo field. To do this, I selected the SalesTable entity and clicked Publish:

PublishingDynamicsAX2012DefaultEntity_JoshLee1

Then I selected my source and clicked publish . . . .DRATS! Foiled again!

PublishingDynamicsAX2012DefaultEntity_JoshLee2

Apparently, some of the tables that are referenced by the DMFSalesTableTargetEntity query (tables referenced in the data sources) do not have the ModifiedBy property set to Yes.  This means I’ll have to locate all the tables in all the data sources and verify that ModifiedBy is set to Yes.  To do this I did the following on the SalesTable and PriceDiscChangePolicy table.

  1. In the AOT go to Data Dictionary, Tables, [Table]
  2. Right click on table (if properties pane does not appear on the right) and select Properties
  3. Find the Modifiedby property and change it to Yes
  4. Click the Save button in the AOT
  5. Synchronize
  6. Restart the AOS (I found this was required for it to pick up the changes, your mileage may vary)

PublishingDynamicsAX2012DefaultEntity_JoshLee3

PublishingDynamicsAX2012DefaultEntity_JoshLee4

Once the SalesTable and PriceDiscChangePolicy reflected the change I went back and attempted to republish the SalesTable entity.

PublishingDynamicsAX2012DefaultEntity_JoshLee5

Success! Now to check I opened up SQL Server Management Studio and queried the table that was published (DMFSalesTableEntity).  As you can see there is no data because this only published the definition (schema) of the entity to the database but did not push the data over.

PublishingDynamicsAX2012DefaultEntity_JoshLee6

To populate the table with data, go back to AX, open the Data import export framework Area page and click on Manage refresh schedule.

PublishingDynamicsAX2012DefaultEntity_JoshLee7

Click New.

PublishingDynamicsAX2012DefaultEntity_JoshLee8

Create a group to hold the entities that you will sync.

PublishingDynamicsAX2012DefaultEntity_JoshLee9

Once a group is created, select it and then click Entities.

PublishingDynamicsAX2012DefaultEntity_JoshLee10

Select the SalesTable Entity we created before (ignore the other one, that will be in a later post on creating a custom entity)

PublishingDynamicsAX2012DefaultEntity_JoshLee11

Select the source

PublishingDynamicsAX2012DefaultEntity_JoshLee12

Click New schedule

PublishingDynamicsAX2012DefaultEntity_JoshLee13

Within the schedule form you have 2 ways to get data:

  1. On a schedule
  2. Ad-hoc

PublishingDynamicsAX2012DefaultEntity_JoshLee14

If you simply click OK, the dialog will close and the population process will kick off.  You can also schedule this to run on a batch server and on a set schedule (click Recurrence) for an automatic incremental refresh.  For now, I’m going to click OK. When the processing is complete you will see something like this:

PublishingDynamicsAX2012DefaultEntity_JoshLee15

Back in management studio I now see data!

PublishingDynamicsAX2012DefaultEntity_JoshLee16

And that completes publishing a default entity. For the final post, I will cover how to modify the existing entities that contain memo fields that are incompatible with SQL.  For now, you can hook up PowerBI to the table that was created and start reporting!

Related Posts

4 Comments

  1. metin emre

    Hi Josh,

    What do you think? We may go with entity store or create our own DWH for reporting would be better or painless?

    Thank you for sharing,

    Metin

  2. Michael Tweiten

    Hi Metin,

    The Entity Store is a great option for an organization that is planning on reporting off of data coming directly out of AX and not any other data source. If you don’t have the need to integrate data with other data sources such as another ERP system or CRM (or other sources) then you can use the entity store. However, if you have information requirements where you need to extract data from AX and other systems and integrate that data then a data warehouse is the way to go.

    Another aspect to note about the entity store is that it can only be accessed with Power BI DirectQuery mode. So if you have any requirements that can’t be satisfied with Power BI, then you will want to consider building a data warehouse.

    The least painful method is probably the entity store, but it does have its limitations as mentioned above. If you are considering a data warehouse, we would suggest building out a roadmap first of the data and KPI’s you want to prioritize and bring in the data warehouse. This will enable you to break the DW implementation into manageable pieces and make it less painful.

    Regards,
    Michael

  3. Erik Liffner

    Hi!
    What a great blog post. We are in the process of changing our way of getting data out of AX and into our DW. I have tried to read as much as possible about Entity Store but have also come across DIXF. What is you take on this? Is one better then the other or can they be combined somehow?
    Regards,
    Erik Liffner

  4. Dalton Roll

    Hello Erik,

    Either option can work, but it depends on what you need for your data warehouse. If you want a direct connection like the entity store functionality provides, then that’s what I would attempt to use first. If there would be requirements to rip out a file and import that file to the data warehouse that’s when I’d go down the path of using the DIEF Export functionality.

    Regards,
    Dalton

Submit a Comment

Your email address will not be published. Required fields are marked *

Upcoming Events

september

01sep10:00 am10:30 amEnsuring Quality and Compliance for Batch Manufacturers in Life Sciences

01sep12:00 pm12:30 pmIs it Worth It to Move to the Cloud? A Look at Considerations for Current Agribusinesses Using Dynamics GP

01sep2:00 pm2:45 pmWhat’s New in Dynamics 365 Finance and Supply Chain

09sep11:00 am12:00 pmConfab Live with Stoneridge – Data Strategy and Reporting – Mining Decision Making Insights

15sep10:00 am11:00 amSolving the Biggest Challenges in Agribusiness Through Innovation and Technology

22sep12:00 am12:30 pmSimplifying Payroll and HR Management with ADP Workforce Now

22sep10:00 am10:30 amStreamlining Batch Manufacturing with Technology

22sep2:00 pm2:30 pmProcess Automation for Microsoft Dynamics D365 for Business Central, Finance and Operations and GP

23sep11:00 am12:00 pmConfab LIVE with Stoneridge - Riding the Wave 2 Release – Key Features Coming to Dynamics 365 this October

29sep10:00 am11:00 amTop Five Reasons Why NOW is the Right Time to Move from Salesforce to Dynamics 365 Customer Engagement

29sep12:00 pm12:45 pmUnderstanding Job Costing and Tax Management in Business Central

29sep2:00 pm3:00 pmDigitalizing Horticulture & Agriculture - How to Sell Plants Online and Simplify Business Management

30sep12:00 pm4:00 pmSecurity and Permissions Training for D365 Business Central or Dynamics NAV

october

06oct10:00 am10:30 amPreview of D365 Business Central Fall Release Features and Functionality

06oct12:00 pm12:30 pmInsider's Guide to New Features Available in the Fall Release of D365 Finance and Supply Chain

07oct11:00 am12:00 pmConfab LIVE with Stoneridge - Dataverse and Dynamics in Review – Let’s Get Technical

21oct11:00 am12:00 pmConfab LIVE with Stoneridge - Dataverse and Dynamics in Review – Let’s Get Functional

26oct(oct 26)9:00 am28(oct 28)5:00 pmStoneridge Connect Leadership and Community Conference

About Stoneridge
Stoneridge Software is a unique Microsoft Gold Partner, with emphasis on partner. With specialties in Microsoft Dynamics 365, Microsoft Dynamics AX, Microsoft Dynamics NAV, Microsoft Dynamics GP and Microsoft Dynamics CRM, we focus on attracting the most knowledgeable experts in the field to our team, and prioritize delivering stellar solutions with maximum impact for your business. At Stoneridge, we are deeply committed to your results. Each engagement is met with a dedicated team, ready to provide thorough, tailored, and expert service. Based in Minnesota, we intentionally “step into your shoes,” wherever you are. We focus on what you care about, and develop trusting, long-term relationships with our clients.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!

X