Materialize and Synchronize Power BI with a Data Warehouse

“Materialize” is a database concept that refers to storing the result of a query, often in conjunction with a SQL view. The advantage here is that when using the result set you can query stored values as opposed to having to run calculations with typically a significant improvement in response time.

Recently a customer asked us to “materialize” DAX calculations in a Power BI model to their data warehouse. In this blog I will cover a very simple approach to realize this.

A key requirement here is to understand the nature of DAX calculations: these are -contrary to common belief- not associated with table in Power BI but a “freestanding” semantic concept that can be associated with any table of the model (even a calculated one – more here later).
The results of a DAX calculation only exist in conjunction with a query or table, so we need to look at materializing that.

Typically, you have 2 options in Power BI: either create a calculated table or add calculated column with the measure(s) that you want to materialize.

1. Calculated Table

This approach will use a DAX measure like SELECTEDCOLUMNS() to create a measure table with columns from the source table that you want to use with the measure and the measure(s):

2. Calculated Column

This approach uses a straightforward addition of a calculated column to the table where you want to materialize:

Simple Export

A simple approach to materialize either result set is to export the table data file and load that to where you need the materialized results.

Automated loading to a Data Warehouse

A more elegant and automated way is available via the Acterys Power BI Sync External Tool which can be downloaded from this link: https://landing.acterys.com/power-bi-sync and only takes a few seconds to install.

Once installed you can just configure the access details of your target server where you want to store the materialized tables and then just point to it and click on export:

Power BI Sync also allows you to automate this task and run the load process in a chosen time interval:

Write-back & Planning

With an Acterys subscription this table is then also immediately “write back” enabled and can be used with all the planning features that are included in the solution: