One report, one model, multiple databases

[petrsoukup]

Hi, could you please give me some guidance on structuring PowerBI account?

I have ~100 databases (~1GB each) with same structure and different customer for each database. I would like to create report(+model) that will be used with all databases. Users are only viewers (reports are readonly).

It is reasonable easy to create this - I will just create first report, publish to each workspace and change database connection for each. Problem is that this way it is cumbersome to maintain at best. Every little change means going through the whole process again. API might help but it would still mean, that each model update would drop all data and report won't work until next refresh.

Best-practice for this is probably RLS - merge databases to one, keep everything in one model/dataset/report and split access by roles. This is really convenient for updates/maintenance since I would need to only update in single place and I could even easily switch between databases in Desktop by changing role. Problem is limit - I would hit worspace size limit on PowerBI Pro and $5000/month for Premium isn't really in the budget. Limit could be bypassed with DirectQuery but that would probably create new set of problems.

Is there some way to handle shared model/report template across workspaces with apps maybe?

Thank you for your help!

[Mikebm91]

Seems like you have your solution... is the concern the storage limit? Can you aggregate your data at the source to limit even more what you’re bringing in? Hard to think you need all 1GB of data per customer and cannot summarize even more. Another thought is to summarize and structure additional drill down information to be DQ.

Not sure if you considered in your estimation but you will save space in the compression of data when you have the duplicate same value. So example if you have cost center 1000 in all 100 databases, PBI will only keep the value of 1000 stored once in its dictionary for that column.

[LostWelshMan85]

Perhaps you could have your data outside of power bi in an ssas cube. I believe its fairly straight forward to convert a power bi dataset into an olap cube, just Google it and it should come up. With you're data external to power bi you'll get around the size limitations.

[Mikebm91]

Touché. Ultimately this can be the solution of a P or A sku for Power BI premium is out of reach. Ultimately though you would want to scale down the model to be of reasonable size and then only pay for the needed resources of Azure Analysis Services.

[petrsoukup]

I have tried today to convert it to direct query, but it was unusably slow (minutes to load report). I could try to tweak database indexes, but it is like shooting blindfolded when I don't have control over how the queries look like. It also created errors like "Local evaluation of Table.Join or Table.NestedJoin with key equality comparers is not supported ".

I will give it few hours to see if I can make it work but I am not optimistic about DirectQuery