Forum Discussion
Switch from [Live Connection] to [Direct Query Composite] to have local Changelog. Bad idea?
I have a ton of reports that connect to a central 'golden' dataset via Live Connection.
I'd like to start having a changelog in these reporte.
The easiest way to me, is to enter that changelog directly into a table in each report PBIX file. That way the changelog can be displayed in the report, and the source data is in the same file.
This however, changes things from a [Live Connection] to a [Direct Query Composite Model]. I've usually just vaguely avoided Direct Query unless necessary.
Is this a bad idea?
Hi mmace1
Or maybe you can use data flow to create a central changelog that can be accessed by all reports without switching to Direct Query.
Create your first Microsoft Fabric dataflow - Microsoft Fabric | Microsoft Learn
Creating a dataflow - Power BI | Microsoft Learn
The alternative workaround are to add the change log to the central dataset or store the change log externally as mentioned by AmiraBedh.
Whether it's a bad idea to use the Direct Query composite model for your change log depends on your specific scenario, including the size of your reports, how often they're updated, and your organization's data governance practices. If the drawbacks listed above aren't a major problem for your situation, then it might be a perfectly acceptable solution.Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
IMHO, switching from a Live Connection to a Direct Query Composite Model just to include a changelog is a bit of a mixed bag. While I see the convenience of having the changelog directly in the PBIX file, there are a few things I’d consider before making this change.
Pros :
Having the changelog stored directly in each report means that can manage and display it right there without relying on external sources. It feels like a simple solution
The Composite Model allows me to combine the Live Connection with Direct Query, which gives me more flexibility to add something like a changelog without affecting the central data source.
Autonomy: With a local changelog, changes to the central dataset won't affect my ability to track updates at the report level.
Cons:
I’m concerned about performance. Direct Query, when mixed with Live Connection, could create delays, especially if the central dataset is large or complex. Adding another layer with a local table might slow things down further.
Having a local changelog in each PBIX file could make it harder to maintain consistency. If something changes in one report’s changelog, you'll need to update all the others, which feels like a hassle.
If I were you I'll tend to stick with Live Connection because it keeps everything centralized as a single source of truth. Moving to a Composite Model feels like I’d be stepping away from that principle, which could lead to discrepancies across reports.
Also if you publish these reports to the PBI service, you will d need to be on Premium, which is another hurdle to consider.
You could add the changelog to the central dataset so that all reports pull from the same source, keeping it simple and consistent or store it externally (maybe in SharePoint, Excel, or a database), then link it dynamically in the reports without changing the connection model.
You can also consider using parameters or bookmarks to display the changelog dynamically without adding a whole table.
Thanks!
"Also if you publish these reports to the PBI service, you will d need to be on Premium, which is another hurdle to consider."
Why would it need to be on premium? We have Pro licenses now, and composite models are shareable.
The downside of sharepoint, is now everything lives in 2 seperate places (vs 1 pbix file), and the data is also not displayable directly in a report. I think this is inconvenient enough, it'll go the way of eventually not being updated nor referenced.
The downside of keeping it in the main dataset, is now we have 40 changelogs in that dataset (gross), rights to edit the main dataset are needed to update the changelog, and the main dataset takes a bit to download / upload.For just using composite models, Pro should be sufficient uless you are dealing with large datasets or certain features like Paginated Reports, or large-scale sharing and collaboration.
As for your points it is true that SharePoint does indeed introduce a level of separation that might cause long-term maintenance issues.
I find your topic open to be discussed let's see what other people think 😄
