By “fixing” I mean “Lighting It on Fire and Starting Over”
Microsoft provides two Power BI content packs for Dynamics 365: Sales Analytics and Process Analytics.
As part of Cooptimize‘s open source initiatives, we are building better standard Dynamics 365 content, beginning with Sales Analytics. You can find the files and instructions in this GitHub repository.
This article will go into technical detail about the right way to build a data model by comparing our solution with Microsoft’s. If you don’t need all the nerdery: click here, Download the Power BI template file, and happy reporting!
Summary
GitHub Repository
The Power BI files (.pbit) are available for free!
What’s Wrong With the Originals
In short, everything. It uses the wrong technical approach, ignores BI best practices, hard codes values, overcomplicates simple things, under-complicates complex things…
Data Source – OData vs Dataverse/SQL
Microsoft’s files are using OData to retrieve Dynamics data.
The better Power BI connector is the Dataverse or SQL connector. Our open source file has parameters that allow the usage of either connector. The SQL connector could be used directly against Dataverse, but the more common use case is with Azure Synapse on Data Lakes.
Resolving Option Sets
Microsoft has hard coded Option Set Ids and Descriptions into the Power BI file.
When using the Dataverse connector, or our Cooptimize code with Data Lakes, this isn’t a problem. Option Set fields like “statuscode” will automatically resolve to a column with a “name” suffix (e.g. “statuscodename“).
Separate Table Connections vs Queries
In PowerQuery or Dataflows, it’s a good practice to separate the table connection from any transformation steps. Turn off Enable load on table connections, then click Reference to start the Query transformations.
Some advantages to this approach include
- You can repoint the tables to a different environment. For example, if you have to move from Dataverse to Data Lakes, this structure will be much easier to upgrade.
- Tables can be referenced in multiple queries.
- Selecting a table is a quick method of data exploration.
Star Schema
A Star Schema is a method of relating tables where the relationships are as simple as possible.
When reporting from CRM, it takes some creativity to put the tables into a Star Schema format. It’s not always clear if a table contains “transactions.”
Microsoft Model Structure
The structure provided in the template file doesn’t really have transformations – it’s 1:1 with the CRM tables.
Poorly designed data models make reporting harder. Many times users will write complicated DAX expressions to compensate for the shape of the data model.
One common symptom of a complex data model is when calculated measures repeat the same value. In the example below, fields from “Opportunity” and “Product” are in one table visual. The amount repeats because of the structure of the model.
Cooptimize Structure
Below is our diagram of a proper Star Schema model. The Fact tables are at the top and the Dimension tables on the left. It’s pleasing to look at!
It’s not simple to develop. For example, Opportunities and Opportunity Statistics are pulling different fields from the same Dynamics 365 Opportunity table. The opportunity table is both transactional AND dimensional in nature. These are counter-intuitive concepts; the important rule is “always default to star schema until you understand star schema”.
Minimalist Field Selection
The best practice when building a model is to be a minimalist. Add tables and fields to your Data Model as required for specific reporting scenarios.
The current Cooptimize model admittedly will need additional fields and measures to support template reports, but too few is better than too many.
Separate Data Model and Reports
It is impossible to separate the data model and reports when publishing a template App – I can’t fault Microsoft for going down this path with their file.
The Cooptimize “Dynamics 365 Sales Data Model.pbit” file does not contain any reports. It is designed to be published to PowerBI.com, then Reporting templates or new reports can be pointed to the model. In Power BI Desktop, click on Data Hub > Power BI datasets > Dynamics 365 Sales Data Model to explore your data.
What’s Next?
There are two more topics to be covered related to this fixing the content.
- Dealing with dates in CRM reporting.
- Standard Sales Power BI reports.
This Data Model is working, but is very much a “Version 1” file. If you see any issues, please log them on GitHub!
Joel Leichty 