web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Exporting AX data into...
Microsoft Dynamics AX (Archived)

Exporting AX data into Azure SQL Database for Power BI reporting - best approach (that actually works, as of Sept 2017)?

(0) ShareShare
ReportReport
Posted on by TrevorG

I'm working on a project to develop a PowerBI platform to perform analytical reporting on industry verticals.  One of the main data sources we would like to support for this platform is Microsoft Dynamics AX (2012, 365).

I should note up front, I have many years of experience in IT on various Microsoft products, including years of being a developer (C#, SQL Server, etc), but I am not overly familiar with the Dynamics AX platform itself.  I do have access to a Dynamics 365 test instance (Contoso).

When first looking into this, it seemed like the recommended approach to this would be to use the new Entity Store functionality.  Based on reading the various blog posts that were written when the feature was released as well as what can be found in official documentation, this looked like a pretty straight forward and common sense way to go about it, plus it seemed to be inline with Microsoft's product road map.

However, after working through some different tutorials from the internet and encountering a variety of issues, which led me to blog posts (some written by people on these very forums) pointing out gymnastics that people have had to go through to get the features advertised by Microsoft to actually work, I'm really starting to wonder if Entity Store is actually ready for prime time in a meaningful way (say, beyond a simple hello world demo at a conference), and if anyone is actually using it in the real world.

In case it might be helpful, I will try to reproduce and post some specifics of the various issues I have been having.

But at this point, I thought it might be worthwhile to just ask in general:

a) Is anyone actually using Entity Store for anything non-trivial?

b) Any recommendations on what one could read the get the real story on how to use it?  As it is now, I feel like I am on some sort of a treasure hunt, trying to find little clues here and there in blog posts, youtube videos, blog posts (some Microsoft, some end-users reporting on workarounds to bugs).  Is there really no comprehensive and authoritative reference for Dynamics?

c) Did you have major issues getting it to work in your environment?  (Maybe I'm somehow just Doing It Wrong?)

d) When one encounters "bugs", is there anywhere to report to Microsoft (that they actually monitor?  I read one blog post mentioning MS Connect for AX, but having been a SQL Server user for years, I know that doesn't get a lot of attention.)

*This post is locked for comments

I have the same question (0)
  • TrevorG Profile Picture
    TrevorG on at

    I'll try and give a bit of an idea of the *type* of thing I'm talking about....

    If one logs onto LCS you will see:

    i.imgur.com/EGP2iYE.png

    Under "Resources for this task" we see four separate articles on Power BI integration - excellent, this looks well organized and very encouraging.  Until you try clicking through some of the links.  The first two appear to be dead and simply redirect to docs.microsoft.com/.../fin-and-ops (an extremely likely outcome when clicking any link in the Dynamics ecosystem).  The next two are:

    mix.office.com/.../wdl1dquy2tve  - Advanced Reporting Solutions (Intro) May 2015

    mix.office.com/.../1hkvtnc8sc7l6  - Advanced Reporting Solutions (Demo) May 2015

    Now, I know one shouldn't be so petty as to complain about some out of date blog posts on the internet, however, these are articles linked directly from LCS, shouldn't one be able to have some faith in those?  

    Maybe not I suppose, but it just brings me back to my original question:  is there any documentation on Entity Store that is comprehensive, up-to-date, and honest (in that when it says something is possible, it actually actually works generally as described).

    I will try to continue posting more examples to documents & blogs that I have tried and had problems with.

  • TrevorG Profile Picture
    TrevorG on at

    Hi Sukrut,

    Thanks for your reply.  I actually emailed a Microsoft departmental address and got some feedback as well.  At least part of the confusion here is that in documentation Microsoft has used Entity Store and BYOD interchangeably, also the messaging is small bits and pieces here and there.  It's hard to know what the actual functionality is, how to use it, what the limitations are, etc.  Especially for someone not overly familiar with AX in the first place.

    I need the ability to perform fairly efficient incremental pushes of data from Dynamics (both on-prem and cloud) into an Azure SQL database, including the handling of deletes.  I don't think this is too elaborate of a design to pull off in the year 2017, but at the moment it's far from clear to me whether it is even technically possible, and I have no idea how one might resolve this uncertainty.  Reading the manual doesn't help.

    > Whenever we need any data for reporting for custom functionality we just create entity and publish it to our own Azure DB and do reporting on that. Its pretty simple and easy to configure.  You can set up recurring jobs according to your need to export full as well as  incremental data. Only problem we see with this is it doesn't support delete operation but its in Microsoft roadmap .

    Again, these deletes are the big risk factor for me, surely there must be *some* way to accomplish this.

    You mention a roadmap, is that referring to specific feature guidance Microsoft has officially posted somewhere, or is that more a case of reading tea leaves and hints dropped in blog posts or technical demos?

    Thanks again for your help on this.

  • TrevorG Profile Picture
    TrevorG on at

    Thanks for that, I wasn't aware a roadmap had been published, will give it a read.

    30M records in < 5 minutes seems probably good enough, for starters anyways.  Hopefully there's no data transfer charges.

    Repeatedly pushing 30M rows in order to detect deletes is obviously a less than perfect approach though.  I honestly wonder how much of this is a genuine technical problem and how much is a business model problem.

    Did you happen to investigate whether Logic Apps might provide an alternative approach?  But once again, that gets into more reading of not-necessarily-trustworthy documentation, limitations of what we're allowed to do (as opposed to what is technically possible) in the on-prem data gateway, etc.

  • TrevorG Profile Picture
    TrevorG on at

    I'm still curious to hear if anyone knows definitively whether it is technically possible to handle deletes in some alternative way in AX7.  In the past, I believe it was possible to directly access the production AX database, but with AX7 running in the cloud, my understanding is that it is no longer possible to access the AX database directly nor the Entity Store database.  If this is true, incrementally handling deletes is not possible at all, and doing a full push is literally the only technically possible approach.

    Is this correct, or am I misunderstanding something?

    (NOTE: This was the only roadmap reference to deletes that I could find, no mention of when we might be able to expect support for this:

    https://roadmap.dynamics.com/features

    Bring your Own Database (BYOD) support for delete operations
    Bring your own data store (BYOD) is a feature that’s used by customers to integrate data from Finance and Operations with existing data warehouses. BYOD allows you to incrementally export data into a customer’s SQL Azure database. While an incremental export feature is ideal for propagating changes, we will also support full export, which is typically used for initial data population. Incremental operation propagates insert and update operations to the destination database. With this addition, BYOD incremental refresh operations delete records in the destination database if corresponding records are deleted in source.)

  • Martin Dráb Profile Picture
    Martin Dráb 237,795 Most Valuable Professional on at

    I'm not aware of Entity Store and BYOD being used interchangeably, but be aware of the fact that the term Entity Store is used for different things in AX 2012 and AX 7. What's called Entity Store in AX 2012 is BYOD in AX 7 (and Entity Store in AX 7 is a different thing).

  • TrevorG Profile Picture
    TrevorG on at

    It's not overly important other than it somewhat complicates an already somewhat confusing conversation, but the names have been used interchangeably, one example being:

    blogs.msdn.microsoft.com/.../export-dynamics-ax7-entities-to-your-own-azure-sql-database

    "You may be familiar with the feature we introduced in AX2012R3 in May-2016. With this feature, an Administrator can create a new SQL Azure (or SQL Server) database and provide the connection information in the Data management area page. The Administrator was able to choose AX2012 R3 Entities and stage them in the database. We called this database the Entity Store and it could be used for Power BI and Cortana Intelligence Suite (CIS) integration. You could also access this database using any of the tools that supported T-SQL and it could be used for integrating with other BI tools or for application integration scenarios."

    What is important though, and what I'm still unsure of, is whether the BYOD approach is or is not the *only* way to access AX7 data from external tools like T-SQL, SSIS, etc (Power BI seems to be an exception in that it is able to directly access the Entity Store, and by "Entity Store" I am *not* referring to the BYOD database but rather the [AxDW] Entity Store database).

  • Martin Dráb Profile Picture
    Martin Dráb 237,795 Most Valuable Professional on at

    I don't see any problem with the quote. It talks only about the Entity Store in AX 2012; not about BYOD. The paragraph following your quote describes Entity Store in AX 7 and the next one mentions BYOD.

    BYOD is the way provided by Microsoft for this purpose out of the box, which doesn't mean that no other solution is technically possible. You could build something custom, if you really want.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans