Power BI has a feature that allows you to add source tables to a report that is referencing a published semantic model. This is a really big deal in data modeling land, because previously if you wanted to add a minor table for some additional use case that you only needed for one report, you had to either add it to the source model or create an entirely new semantic model. It’s particularly important where you’re using “golden model” architecture, a.k.a. there’s a small number of core models that are re-used for reporting; it helps keeps the resulting reporting numbers consistent across an org.
How it works
You’ll usually be starting out with a report that’s already connected to a Power BI semantic model (or dataset, as its still called in some menus), but you can go the other direction too and add specific tables or a whole model to an existing report that has other sources already (?! this is mind-blowing for old-schoolers).
Now, when you go to add a source, it asks you to “add a local model” and says “a DirectQuery connection is required”. You just say yes and select the parts of the semantic model you want to be included. Usually this is all of them, if your report is built on the model: the step where you select a source to add is actually the next step.
Now, a few important points about how this works:
1. The sources you add don’t need to support Direct Query
The language they use in the popup is a bit misleading – you can add import mode sources like SharePoint lists or Excel to a report that references a semantic model. When it says “a DirectQuery connection is required”, it’s actually talking about direct querying the semantic model, not whatever source you’re adding. This isn’t Direct Query in the way you normally encounter it – it’s a “live” connection to the semantic model, which is very fast.
2. Your report, when published, will have its own semantic model artifact in the workspace
This is important to notice, because if you’re used to the standard way of building reports off of existing semantic models, the traditional usage it wouldn’t create its own semantic model – it would use the source semantic model. In this case, because you’re adding queries, there’s a semantic model created. It still references the original semantic model, but presumably it needed a place to hold the added bits. The new semantic model will have the same name as the report by default when published.
You will need to schedule refresh on this model if you want your added bits to refresh if they were sources that weren’t using Direct Query (e.g. your SharePoint or most dataflow sources).
3. The source model needs to be in a shared workspace, not a personal workspace
The option to add a source will simply be grayed out if you’re connecting to a semantic model in a personal workspace. It took me a while to figure this out when I was trying to play with it, because I’ll typically publish trash I’m fiddling with to my personal workspace to avoid cluttering up real workspaces… turns out it needs to be in a real shared workspace. 🙃
Summary
The ability to add sources to a report that is already referencing a semantic model is awesome. It’s one of the more underutilized Power BI features right now, but it’s really the way to go if you want to be standardizing reporting at scale! The tips above were not referenced in any documentation I could find, and tripped me up during setup, so thought I’d share!