DirectQuery for Power BI Datasets – What does Chaining mean?

In my last blog post I introduced the new concept of DirectQuery for Power BI datasets. This feature allows you to extend and modify a (remote) published Power BI dataset with the help of a local model.

Conceptual architecture for DirectQuery for Power BI datasets (including the local model)

The local model does not contain a copy of the remote dataset but a reference to it. You, as Power BI developer, are able to extend the referenced model with new data sources (like the Excel file I used in my previous example) and/or extend the model with new measures, columns and so on. For a new data model, relationships between the two data islands can be created.

Dataset chaining

This approach is not limited to a one-hop: A Power BI dataset referencing another Power BI dataset using DirectQuery can be used as a source for another Power BI dataset.

Let’s imagine the following scenario:

  • Dataset #1: Sales containing Sales information plus describing dimensions (Stock Item, Date, City, Customer)
Dataset #1 – Sales data
  • Dataset #2: Sales with Employees references the Sales dataset plus enhances the model with an Employee list (Excel file)
Dataset #2 – Sales with Employees (referenced remote model plus Excel source)
  • Dataset #3 – Sales with Employees and Purchases – DirectQuery to Dataset#2 (Sales with Employees) plus DirectQuery to another Power BI dataset Purchases
Dataset #3 – DirectQuery to Dataset#2 plus DirectQuery to Purchases dataset

The overall picture of dataset chaining looks like the following:

Dataset chaining with DirectQuery references

Maximum chaining in Preview is three hops

In the current preview (December 2020), the chaining length is limited to three (blog post). If you try to build a longer chain, it results in errors. Not in Power BI Desktop – I could create a Dataset nr 4 file without a problem, but after the publish to the Power BI service, the report / dataset is not usable. As of today, there is no error message available. Maybe the Power BI adds a meaningful one in the future!?

Dataset #4 – Maximum chain length in preview reached.

Follow the chain – Data Lineage

Following my passion for data engineering and ETL processes, the data lineage feature in the Power BI service and Azure Purview are important information sources for my day to day work.

Let’s have a look at the Data Lineage support for DirectQuery datasets. And the result is, well – not the best looking one. To be honest, it’s also mentioned on the documentation page that there is work to do for better Data Lineage integration.

source for featured image: https://www.pexels.com/photo/sea-nature-sunset-water-119562/

About wolfgang

Data Platform enthusiast
This entry was posted in Business Intelligence, InformationSharing, PowerBI. Bookmark the permalink.

1 Response to DirectQuery for Power BI Datasets – What does Chaining mean?

  1. Pingback: Cahining with DirectQuery for Power BI Datasets – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s