Bring Your Own Database (BYOD) for Dynamics 365 Finance & Operations: Part 1

by Masood Pehlvi 5 minute read

This is the first post in a 3-part series. In this article, I talk about the Entity store and the concepts behind BYOD. In Part 2, I show how to set up BYOD, and in Part 3 I show how to search the system logs for error messages as a result of BYOD runs.

Introduction

Before we delve into the mechanics of setting up a BYOD within Dynamics 365 for Finance and Operations (D365 F&O), let us understand the data model in more detail.

Referring to the above diagram, we see that D365 F&O has 3 data stores:

  1. The Primary DB
    • This is the operational database and is inaccessible for direct use by any other toolset.
  2. The Secondary DB
    • This is a real-time replicated database, which is used to support the application and is also inaccessible for direct use by any other toolset.

What’s not shown in the diagram is the concept of Data Entities. These are abstractions of the underlying tables into entities, which you can think of as “views on top of the tables”. For example, if you think of what tables are needed to store information for a vendor, the vendor id will be found in a main table and the vendor address will be found in a separate table and the two tables will have foreign-key relationships between them. However, the vendor data entity will show all the vendor information in one entity (view), having done the joins between the various underlying tables automatically. What this means is that the underlying tables and relationships are used to group data into entities.

  1. The Entity store
    • This database is composed of aggregations of the primary data entities into a reportable grouping called aggregate measurements. The Entity store is made available for analytics using Embedded Power BI.

The Entity store and BYOD

Think of data entities as an extensible data store meant for use in advanced analytics on the operational data from D365FO. Because it is extensible, we have the ability to enhance existing entities (i.e. add new attributes or columns) or create new entities. Some of the reasons and benefits of having a separate data store for analytics purposes are:

  • near real-time data;
  • the ability to model that data;
  • the ability to use advanced Azure cloud technologies, methodologies, and toolsets.

The Entity store can be thought of as a database attached to the primary operational database(s) but whose function is to expose the data entities in an aggregated way – note that the Entity store resides within the D365 F&O system. When you export data entities out of D365 F&O into an external Azure database, this concept is known as BYOD. You can think of BYOD as a database residing outside of the D365 F&O system but reflecting the state of data in D365 F&O at a certain point in time (depending on the refresh strategy) – this external database can then be used for analytics combining data from D365 F&O with data from other systems. Keep in mind that once entity data is exported outside of D365 F&O, then the updating of that data with newer operational transaction activities becomes a task requiring the setting up and scheduling of a refresh process – the Entity store is updated automatically by the system but the BYOD data needs to be configured to stay up-to-date.

What does this mean?

Using the BYOD concept, you have the ability:

  • to replicate D365 F&O data into an Azure SQL database …or to flat files (such as Excel, CSV, etc) that you can then import into a database. These are the 2 main options at this time.
  • Once in a database, you can aggregate/integrate the data to ready it for reporting use. Once again, remember that this data is a snapshot in time of the D365 F&O data and needs to have processes to keep the data up-to-update.
  • Then, when the data is readied, you can create reports using Power BI or other toolsets (i.e. SQL Server Reporting Services) if they make more sense within your architecture.

The following diagram shows, in a simplistic manner, how BYOD fits within a modern enterprise BI architecture:

The details of the ETL/ELT are left out of the architectural diagram above, as is the semantic layer (or Data Hub), and only Power BI is shown for the reporting layer. Thus, you can clearly see where and why BYOD fits within an enterprise BI architecture.

Conclusion

In this article, I talked about the Entity store and the concepts behind BYOD. In the next article, I will show how to set up BYOD and in third article of this series, I will talk about searching the system log files for errors in BYOD jobs. Perhaps in subsequent articles, I will talk about the key features of a modern enterprise BI architecture, in which I can present why we need a semantic layer. (Hint: we take data out of D365 F&O and into Azure SQL database where we integrate it with other data. To report from that data, we need to do some model and performance tuning – this is where the semantic layer comes into play.)