Umm… What’s the difference between entity store and BYOD?

 In Blog

I’ve been busy recently, so I haven’t had much time to blog. I was at the AXUG Summit, though, and something came up I thought I should write down because it originally confused the heck out of me and I don’t want to forget what I found.

Since AX7 or Dynamics AX was released 18 months ago, reporting and access to the data has been a concern of mine. Also, since the release, the terms “entity store” and “BYOD” have been bandied about and interchanged, so I’ve been confused. I got to the bottom of this at Summit, though, by confirming some of my assumptions in a great discussion with a couple of people on the Microsoft team and some other partners.

For starters, BYOD and entity store are two separate things. BYOD stands for Bring Your Own Database. While this could really mean any sort of database external to the D365FO database, it’s usually referenced in terms of data management. Entity store is something completely different. The entity store is a name for an internal, inaccessible data set within the D365FO product. For all intents and purposes, it’s part of D365FO and is treated the same way as the underlying transactional database – it’s there and behind the scenes, but an end user can’t see it, can’t query it, and can’t use it. The only way the entity store is used is for embedded Power BI reports.  To learn more about Power BI, click here and reach out!

I had heard discussions at the Tech Conference last year about using BYOD as a data warehouse. Staging data from D365FO, including external data, and then building Power BI reports on top of it. While this is technically possible, it doesn’t seem like this is the intended use. The main reason I say that is the way that it’s populated. The only way to dump data out of Dynamics 365 for Finance and Operations en masse and into the BYOD database is to use the data management framework (DIXF.)

–edit– removed based on additional feedback from Milinda Vitharana at Microsoft —This is fine, but this means the BYOD database needs data entities created for each set of data needed. Again, not a problem, but the out-of-the-box entities don’t include any relevant transactional datasets. They’re mostly setup, master, and reference data. To me, these facts make it seem like the intended use of BYOD is for master data management or providing some master data access to other external systems in an enterprise. To keep them refreshed, you setup a recurring export project and periodically push over datasets.  –end edit–

After posting, I received some feedback from Milinda Vitharana about the true intention behind the BYOD database.  Here are his comments:

BYOD is intended mainly for the following scenario

  • Export Entities to your own data warehouse (ie. you may have accessed the AX2012 DB directly for this in the past)

The choice of Entities available for export into BYOD are 1700+ and counting. It is true that Master and Reference entities were the only ones available in the past. However, we have added transactional entities as part of creating PowerBI content. As of spring-2017 release, transaction entities built for the PowerBI content are available for export using BYOD as well.

BYOD may help with Master data management (MDM) as well as bulk data integration use cases, but it’s not optimized for MDM. While you can use BYOD in an integration pipeline, you may use export to CSV as well.

–edit– Based on that feedback, it seems like the true intention from the beginning was really to use BYOD for data warehousing, but some of the necessary entities were missing from the first couple releases.  What great news!  Things certainly change fast these days! –end edit–

The entity store is completely different. It’s a transformed data set built on aggregate data measurements. These are created using views and perspectives that sort of mirror how the OLAP cubes in previous versions of AX worked. While this seems like a logical place to connect to for access to this optimized data, it’s 100% invisible to the outside world and not accessible by a D365FO user.  If you have more questions about entity store, you can look at this post.

Now, to get around this, Microsoft showed a new set of solution templates you can download from AppSource (edit – these have been deprecated) I had the hardest time understanding what these were for, but I think I finally wrapped my head around it. If a BI developer or Power user needs access to this analytical data for a Power BI report that isn’t going to be embedded, there’s no real way to do that right now. These solution templates, though, basically extract the data from the entity store using an Azure Logic App and dump them into a standalone Azure SQL DB. This way, you’re basically replicating the entity store in a standalone Azure SQL DB that you can use for a source in your reports. If you have external datasets, you can also plug those into the Azure SQL DB that you’ve created and basically make a data warehouse. This all requires additional Azure consumption, though, so you’re going to have to pay a little extra for this new feature.

Long story short, the entities used in DIXF for the BYOD population have nothing to do at all with the entity store. Additionally, there’s no real great way to get access to these aggregated data sets without using the solution template to push them to a separate Azure SQL DB.  Hope this helps!

Showing 11 comments
  • Clark Walliser
    Reply

    Good summary Jacob. You saved me many hours of trying to figure out how it all works together, or not.
    I’m all ears for anyone that comes up with a practical solution for D365 external reporting via Power BI or other BI tool.

  • Clark Walliser
    Reply

    I went to the app store, but did not find any apps I recognized as solution templates to pull D365 data.
    Do you have an example of one you found?

    • Jacob Roder
      Reply

      Hey Clark,

      Thanks for being one of the only non-spam comments I’ve ever gotten on this blog! There’s a handful of them out there – here’s a link: (edit – removed the link because it’s dead)

      Jake

  • Trevor
    Reply

    Jacob, thanks for writing this post, it’s exactly the area I’ve been struggling with for quite some time now.

    “Based on that feedback, it seems like the true intention from the beginning was really to use BYOD for data warehousing, but some of the necessary entities were missing from the first couple releases. What great news! Things certainly change fast these days!”

    This does sound like good news indeed, but my impression so far is that these promises are fairly empty from a real-world practical standpoint. For example, one set of data I need to work with is Purchase Orders. I had been working with an older release of D365 and when trying to do a BYOD export of Purchase Order Headers, I was getting the error: “Purchase Order Headers is not supported for Change Tracking as it doesn’t have any primary index or alternate key.”

    I finally managed to speak with an experienced AX developer (I’m doing some PowerBI reporting, am inexperienced with AX itself) and he said that a new version was released with new “v2” entities that should clear up that issue. So, we got that installed and Purchase order headers does now actually work, but the very next entity I tried, Purchase order lines, still suffers from the very same error. https://i.imgur.com/sjItLgX.png

    So this leaves me thinking: if Microsoft finally fixes the bug in Purchase Order Headers so it is compliant with BYOD exports, but didn’t simultaneously fix Purchase Order Lines, how long are we going to have to wait until they get organized enough to release something that is actually usable?

    Another big problem that is glossed over: lack of support for deletes in incremental exports. I don’t understand how things like this can be simply disregarded in a world class ERP and no one seems to care. Am I missing something?

    In the meantime, what is the recommended (by real world users, not Microsoft’s recommendation obviously) approach for data integration with D365, for both new projects as well as legacy projects with large numbers of external (SSRS) reports or ETL packages that directly access production tables?

    Realistically, does a person need to just custom design all of the data entities that they need? And if so, might you happen to know where one could look to learn how to do that (on a publicly available website rather than behind a PartnerSource or CustomerSource paywall)?

    On the off chance that you’d be open to a voice chat on Skype some day, please contact me by email.

    Thanks!

    • Dynamic Consulting
      Reply

      Hey Trevor,

      Sorry about taking so long to respond.

      I think it depends on what you want to do. The standard entities work pretty good in data management scenarios, but I’ve seen some issues with them too. While we can raise bugs to MS and have them resolve it, that all takes time. It seems like sometimes it’s better to have your own entities, because than you have total control over them. If you have issues with them, though, it’s especially hard for MS to help with.

      They keep increasing new versions and obsoleting old ones, too, so you have to keep on your toes.

  • bob
    Reply

    TEST 132

    Good summary Jacob. You saved me many hours of trying to figure out how it all works together, or not.
    I’m all ears for anyone that comes up with a practical solution for D365 external reporting via Power BI or other BI tool.

    • Dynamic Consulting
      Reply

      I think for now, it’s exporting to Azure SQL and then doing whatever you want with it from there.

  • Nedim Delic
    Reply

    Hi Jacob,

    Thank you for sharing your knowledge on this topic! I believe that the choice for the one (Entity Store) or the other (BYOD), is a fundamental one having serious consequences for BI projects. It has been a while since you have written this blog and Microsoft has continued development in this area. Hence, I wonder whether you have changed your thoughts about this topic?

    The reason I ask this question is because I still cannot find a clear statement from Microsoft about it. That is, there is a Microsoft doc available which, amongst other, addresses the following question:

    Do customers have to purchase a separate Power BI license to use the new embedded analytics?

    And the answer they provide is as follows:

    “No, customers don’t have to purchase a separate Power BI license to use the new embedded analytics.
    However, a Power BI Pro license is required in order to connect to Entity Store from PowerBI.com by using DirectQuery.”

    This is the link to the doc (created on 9 february 2018): https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/analytics/power-bi-embedded-integration

    So, this seems to me that we actually will be able to approach the Entity Store from PowerBI.com. How do you interpret this?

    Besides the abovementioned document, I also came across the following Microsoft doc (created on 1 may 2018):

    https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/analytics/entity-store-maintenance

    It says “If you have deployed entity store-based reports to PowerBI.com and are using the reports within PowerBI.com”. This again seems to me as an indication that it will be possible to approach the PBIX files deployed via LCS with PowerBI.com. If this will be possible, then we could actually approach the entity store as a Power BI Dataset and build reports directly on the Entity Store. Do you agree with me?

    Regards,

    Nedim

    • Dynamic Consulting
      Reply

      Hey Nedim,

      Yeah. There’s still some confusion on the best approach here and I can’t get a great answer yet, either.

      The embedded analytical workspaces do not require any additional licensing – access comes as long as you have access to D365. By dfault, these are only installed on Production and Tier-2 sandbox machines and up. The standard Dev machine that comes with a D365 subscription doesn’t have Power BI installed on it, but it’s possible to set on up using a solution template that’s floating around somewhere.

      You can connect to the entity store in production and present that data in PowerBI.com, but that requires additional licensing and help from a developer. Additionally, only entity store data can be included – no data mash-ups are allowed at this point.

      There’s still more changes coming out and MS knows there’s some shortcomings with Power BI, so I’m planning on staying tuned!

  • Chady
    Reply

    Hi Jacob

    >>These solution templates, though, basically extract the data from the entity store using an Azure Logic App and dump them into a standalone Azure SQL DB

    Do you know who the vendor is for these solution templates? I can’t find any in the Appsource.

    Looking at something more than just data entities. Prefer the entity store extraction instead.

    • DC Admin
      Reply

      Hey @chady,

      Microsoft is the provider. Looks like they yanked them from AppSource, though. Deprecated. If I see them pop-up again, I’ll update the post.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Contact Us

Have a question? Want more information? Let us know!

Not readable? Change text. captcha txt