Power BI Dashboards for Dynamics 365 Finance & Operations via the Common Data Service - Part 1

[Greg_Deckler]

Introduction

As Dynamics AX moves to the cloud as Dynamics 365 Finance and Operations, certain patterns used in the past, like "Bring Your Own Database" (BYOD) for reporting, must necessarily change to fit the Software as a Service (SaaS) model. This is the result of not having the same access to the underlying SQL database for Dynamics AX in Dynamics 365. Luckily, Microsoft has provided new tools such as the Common Data Service (CDS) and Power BI Dataflows that put a new spin on the BYOD pattern.

Old and Busted

Well, OK, not completely busted if you are still dealing with Dynamics AX on-premises but if you are a fan of the Men in Black (MIB) franchise, you may get the reference.

Dynamics AX On-Premises

With Dynamics AX on-premises, a common pattern that emerged for reporting was the concept of BYOD, which looked something like the following diagram:

In this pattern, an Extract, Transform, Load (ETL) tool, like SQL Server Integration Services (SSIS) was used to move data out of the transactional Dynamics AX database into another database configured for reporting purposes. This kept the load from users running reports from slowing down the transactional system as these SSIS packages were most often configured to perform incremental loads of data to this reporting database. Power BI Desktop could then be used to connect to the reporting database either via Import or Direct Query.

Dynamics 365

When Dynamics 365 came on to the scene, IT personnel lost the direct access to the underlying transactional SQL database for Dynamics and in its place were tools such as OData endpoints and the "Entity Store". Power BI Desktop could connect directly to the OData endpoints but this placed a load on the production Dynamics system. In addition, the OData endpoints could be challenging to work with as certain limitations arose when performing data ingest for millions of records.

The New Hotnesss

Continuing with our MIB theme, Microsoft released new tools, methods and patterns to address some of the shortcomings of the early days of Dynamics, "The New Hotness". Most notably, Microsoft released the Common Data Service, a sort of state engine for data across the Microsoft business applications landscape (Dynamics, PowerApps and Flow). In addition, Microsoft released Power BI dataflows, a way to create reuseable Power Query queries, which just so happened to also integrate with the CDS.

This pattern creates a powerful new spin on the BYOD concept, providing the ability to easily extract data from the underlying data store without the limitations of OData as well as keeping the load from reporting from impacting the underlying transactional database of Dynamics 365.

Why Use the Common Data Service?

There are numerous reasons why using the CDS is attractive, including:

• Easy to manage – Both the metadata and data are stored in the cloud. You don't need to worry about the details of how they're stored.
• Easy to secure – Data is securely stored so that users can see it only if you grant them access. Role-based security allows you to control access to entities for different users within your organization.
• Access your Dynamics 365 Data – Data from your Dynamics 365 applications is also stored within the Common Data Service allowing you to quickly build apps which leverage your Dynamics 365 data and extend your apps using PowerApps.
• Rich metadata – Data types and relationships are leveraged directly within PowerApps.
• Logic and validation – Define calculated fields, business rules, workflows, and business process flows to ensure data quality and drive business processes.
• Productivity tools – Entities are available within the add-ins for Microsoft Excel to increase productivity and ensure data accessibility.
• Avoid OData – It’s messy and hard to deal with

Why Use Dataflows?

As with the CDS, there are also numerous advantages to using using dataflows, including:

• Cloud-based – Eliminates Power BI Desktop as the intermediary, keeps data fully in the cloud
• Easy to Manage - Fully managed software as a service. You don't need to worry about the details of how they're stored.
• Reusable – Dataflows provide a single location for queries as a “single source of the truth” for importing entities.
• Leverage Power Query – Dataflows are built on the same technology as used in the Power BI Desktop
• Can be used in Power BI Desktop and Service– Dataflows can be leveraged in both the Power BI Desktop and the Service

How To Do It?

The process to link Dynamics 365 Finance & Operations to Power BI via the CDS involves seven main steps. These steps include:

1. Build the CDS Environment – Creates and new CDS environment and associated database
2. Create a Connection – Creates a connection using specific credentials to a type of application
3. Create/Edit Entity (OPTIONAL) – Customize or create entirely new entities in the CDS
4. Create a Connection Set – Creates a connection between two applications (in this case Dynamics 365 Finance & Operations and the CDS
5. Create and Run a Project – A project creates a task sequence to execute that synchronizes the environments
6. Create the Dataflow – Creates a link between the CDS environment and Power BI
7. Build the Report – Use the dataflow to create a Power BI report

The rest of the posts in this series will demonstrate in detail how to implement these seven steps to linking Dynamics 365 to Power BI via the CDS.

Summary

Moving from Dynamics AX on-premises to Dynamics 365 requires new tools, methods and patterns to perform old tasks, such as using BYOD for reporting. The CDS and dataflows are integral tools that facilitate the new pattern for BYOD in the cloud. Even better, this new method requires far less time, effort and technical skill to implement. In fact, everything can be done in a web browser with zero code! Read the rest of this series to find out how.