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.
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.
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 #2: Sales with Employees references the Sales dataset plus enhances the model with an Employee list (Excel file)
- Dataset #3 – Sales with Employees and Purchases – DirectQuery to Dataset#2 (Sales with Employees) plus DirectQuery to another Power BI dataset Purchases
The overall picture of dataset chaining looks like the following:
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!?
Follow the chain – Data Lineage
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/