Benefit of using Reference Query

[Anonymous]

Hello,

I have seen in some of the demos the PBI expert uses a reference query to create dimension tables (example https://www.youtube.com/watch?v=4k73xCyki-k&t=1345s). The only benefit I know is that it makes the dimension tables dynamic. However what could be other benefits especially for performance. Also the below questions:

1) Does referencing queries increase the size of the pbix file?

2) Does refresh take a longer time?

3) What do I do with the same columns in the fact table? Do I relate them to columns in the dimension tables?

4) Do I create a key column in the fact and dimention table and relate them? Thereby I can delete some of fact table columns? Will that even work?

[Greg_Deckler]

@Anonymous This is the official answer to referencing queries: Referencing Power Query queries - Power BI | Microsoft Learn. Referencing prevents duplicating logic but can result in slower data refreshes.

[Anonymous]

@Greg_Deckler Haha. Absolutely. So clear as mud. Will continue in my pursuit of hapiness reference query

[Anonymous]

Thanks for the link @Greg_Deckler. So I understand that referencing actually slows down the performance and adds load on the source. Suggests to use Dataflow. Would you know the alternative to Dataflow in Report Server as we are not using the Service.

Also, I just came across a YouTube video from Enterprise DNA(https://www.youtube.com/watch?v=ENHbpukjtlY). Wonder if it is the same as query reference? Looks like. However its stated that the performance improved 🙂

[Greg_Deckler]

@Anonymous Nothing like dataflows in PBRS that I am aware of. It *could* improve refresh performance due to caching. From the article:

Power Query caching architecture is complex, and it's not the focus of this article. Power Query can cache data retrieved from a data source. However, when it executes a query, it may retrieve the data from the data source more than once.

Soooo...clear as mud? 🙂