Dynamics 365 Finance and Supply Chain integration with Power BI

Dynamics 365 Finance and Supply Chain integration with Power BI through AxDw or BYOD

Why integration?

Business Intelligence (BI) tools are fantastic, aesthetically pleasing, big-picture producing tools that allow users in companies to see what their data looks like, and what trends their data is following. These tools reduce miles and pages of raw, mechanical data to useful, dashboard styled information that helps companies steer themselves in the right direction.

However, often, at the most inefficient side of the scale, people collect data for their BI analysis clunkily from several scraped-together sources that need to be updated manually and regularly, which often happens erroneously. Ideally, data should be pushed directly out of a system, en masse, automatically, speedily, reliably, consistently.
Enter integration.

Integration is the process of moving data from one system to another, or how two systems share data with each other.

Transformation

With respect to integration between Power BI and Dynamics 365 Finance and Supply Chain, all the data lies raw and unprocessed in Finance and Supply Chain as a gold mine of data ready to be extracted and refined into useful information, and so it needs to be shaped and engineered into blocks that are useful for Power BI to use.

Imagine Bob has a record in a book of every single hot dog sale he’s made at sporting events over the last year. The book would make for dull reading, but if he could tally up the hot dogs sold, how many people he’d sold to, on which nights he’d sold most, he’d be able to use Power BI to tell him quickly when his best time to sell is, who his most receptive customers are, and even what his most popular hot dog sauce is.

These tallied-up or aggregated data chunks are called measurements which serve as the building blocks for data analysis in Power BI.

Using this data, Power BI could cut across the fine detail and tell Bob that he should cease selling hot dogs with broccoli sauce and spend more time selling hot dogs at children’s events on Friday nights.

The two best ways to integrate and transform data between Finance and Supply Chain and Power BI are AxDW and BYOD.

AxDW

AxDW is a Microsoft managed, OLAP styled database that exists in the same subscription alongside the standard Finance and Supply Chain database, used exclusively for BI. It’s completely inaccessible to end users, but Power BI developers can connect to this database to create reports. This database contains ‘already aggregated’ data because all the mathematical chugging and transformation is done by a Finance and Supply Chain developer at design time, and then as the data is pushed to the database.

This is the standard development process flow for using AxDW:

1. Using Visual Studio, Finance and Supply Chain developers create new tables and views, and then aggregate measurements from those.
2. These measurements appear in the entity store in Finance and Supply Chain.
3. From the entity store, measurements are published to AxDW which both creates table structures and pushes relevant data through.
   (So far, all development has remained within the realm of Finance and Supply Chain.)
4. From here, using Power BI Desktop, a Power BI developer builds reports using data from AxDW.

The Power BI Developer will have the advantage of having data organised and aggregated already. There won’t be much transformation work left to do in the report as most of it will have been done by Finance and Supply Chain.

BYOD

BYOD stands for Bring Your Own Database. Microsoft provides the ability for software engineers to set a normal, independent database up to which they can push data to, and on which Power BI developers can build BI reports. Data is pushed here using data management in Finance and Supply Chain . Once the data is in this database, Power BI developers can build reports that connect to this database.

1. Using Visual Studio, Finance and Supply Chain developers create new tables, views, and data entities.
2. These entities appear in data management.
3. From data management, entities are published to BYOD which both creates table structures and pushes relevant data through.
   Extra transformation can happen on this database at this stage.
4. From here, using Power BI Desktop, a Power BI developer builds reports based on the table structures in BYOD.

AxDW vs BYOD: the Pros and Cons of each

The concept behind both methods is primarily the same: take data from Finance and Supply Chain, push it through some medium to another database, and build a Power BI report on top of that. Neither is superior to the other overall; they both have specific purposes. AxDW must be used for embedded reports in Finance and Supply Chain and has ‘already aggregated’ data, but BYOD offers more flexibility in other scenarios. As such, functional and technical consultants are still left to evaluate the pros and cons of each option case by case where Finance and Supply Chain/BI integration is implemented.

Data lakes

But superior to both of these is the third option in the pipeline, likely to succeed or at least envelope both these options. But data lakes is a discussion for another day.