We have three multi-tenant databases in Azure SQL Server, and I would like to build a universal data model that will include data in all of these databases. My struggle is that I have found multiple ways to go about this: 1) spinning up a new database and doing a messy UNION view to combine all data sources into one place, 2) appending the tables in Power Query which gave me terrible performance, and 3) Power BI dataflow which kills our server during its nightly refresh (I'm doing import, not Direct Query). None of them are sustainable for us, as the performance is no good and it doesn't seem like a very lean approach despite removing unneeded columns and whatnot. We aren't talking huge tables here--I'd say our biggest one is around 3.5 million rows currently.
My fear is that the only way to do this is to build and maintain multiple PBIX files--one for each database. This just doesn't strike me as the best solution because each PBIX will have identical tables/joins/visuals, just pointing to a different database (and different RLS roles). Seems redundant to maintain identical PBIX files just to accommodate a different data source.
Feels like I'm missing something. Any thoughts? In a perfect world we could have a single data model that gets published to separate workspaces and the data source could be defined in the workspace. Is that possible?
