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.
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.
Now, to get around this, Microsoft showed a new set of solution templates you can download from AppSource (https://appsource.microsoft.com/en-us/.) 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!