Dynamics 365 Marketing - Analytic reporting with Power BI

Find here your marketing analytic reporting for Dynamics 365 Marketing using Power BI

Developer, Framework, Marketing reports

Optimize marketing reporting performance and reliability


Update:In the most recent release 1.500.191002 the interaction model is embedded into the report template. Therefore the reports will not load the model.json file anymore, nor is the here described method of adding the file staticModel.json required.


Blob Storage Container - Root

Lets optimize marketing reporting performance and reliability by loading only data that is really necessary for your reports.

In a previous post I had introduced the data load control that you should apply when when you customize the reports from the gallery or if you build your own marketing analytics.
I had mentioned the model file model.json that contains both the model definition for all interactions that Dynamics 365 Marketing knows about and also a reference of all data files that contain the actual interaction data.
This file format has been chosen to match the way the Common Data Model can understand a data model model and corresponding data on Azure storage.

What the reports not need from the model file and what they do not need

The disadvantage of this format is that the one model file needs to keep up with the flow of data files that get emitted to your Azure storage. Chances are that the model file will be updated while your Power BI report at the same refreshes and reads the data from that file. Power BI will detect this and may abort the refresh due to the changing data. Also will the model file become quite large over time due to the growing size of the file name index in model.json. The reports do not require this file name index – because Azure blob storage provides a better way to access the index of actual files in the storage.
But there is a piece of essential information in the model file – the data model of all the interaction types!
Framework expands the interaction model to create empty interaction tables
The reporting framework will use the interaction model in the file to compose tables for all the interactions for which no data have been emitted (yet) to the Azure blob storage or for which you have configured your marketing analytics report not to load any data. The M-Code function GetDefaultInteractionTable will take care of this duty.

And why is this important?

Power BI report are built against a known tabular data model. But what happens if the data source does not provide a respective data table at report refresh time – for example because none of the data exists or is loaded from blob storage? Then the reporting framework takes care of providing an empty data table with the same schema from the model file.
With that help report pages can be built against any interaction data and the report will never fail, even if no data are loaded.
Generate default empty interaction data table

Optimize marketing reporting performance and reliability by reducing the data load

Have you ever experienced a refresh failure with an error message like this?

Failure details: The last refresh attempt failed because of an internal service error.
AzureBlobs: The data source appears to have been modified since it was last accessed. Refreshing may resolve this error. Table: %InteractionTypeIndex.

This can happen with large data volume that lead to high update frequency. The product team will provide a solution for that going forward. but for now we need a quick workaround that reduces the data load and assure that Power BI will not load the model file while it is refreshed at the same time. Here is the simple and also drastic measure. Please find in the following location on GitHub a static model file modelStatic.json.

Download this file and place it in your blob storage container root aside to the file model.json. All Power BI reports and templates on this site will now load the static model file instead.

Blob Storage Container - Root


Update:In the most recent release 1.500.191002 the interaction model is embedded into the report template. Therefore the reports will not load the model.json file anymore, nor is the here described method of adding the file staticModel.json required.


Happy marketing analytics reporting with Dynamics 365 Marketing!

Leave a Reply