If you fell like setting up a collaborative Power BI environment, then this post can give you some ideas.
Working in a large enterprise scale Power BI usually requires some setup like this, but usually you will have a data warehouse to back you up, and if you do, make use of it. However, if you don’t have a proper data warehouse or data lake, or anything like that, in your organization, but you still have a Power BI Premium capacity and several Power BI devs working together, this suggestion might work for you.
Premises:
- You already have your data sources working, with data being properly loaded for your Power BI devs to connect to data and work with it.
Implementing a shared Power BI data model
The goal here is to create an architecture that allows a multi-dev work, where models, measures and data transformation can be re-used, by that creating a centralized single-source of truth, reducing or eliminating duplicative copies of datasets.
Here is the suggested solution architecture:
Let me explain this…
Use Dataflows
By loading your data using dataflow you can kind of simulate an ETL layer on your end. That means that all data transformations can be done using dataflows (power query online), layer by layer, until you have all your data put together for consumption.
Key point here is, you cannot create DAX code or construct any kind of modeling (relationships), but you can merge, append, union data, and all that good stuff, like cleansing your data.
The result of dataflows is a bunch of tables that can later be used in your datamart.
Use Datamarts
If you don’t have dataflows already setup, you can skip dataflows and go with Datamarts, since datamarts can do power query online just like dataflow. Problem is, if you need multi-layer dataflows, meaning, you need to use multiple dataflow to join different data sources, with different logins and etc., you might want stick to dataflows.
Anyway, your data is ready, is clean, is well structured, normalized, or de-normalized, to your needs. Now you can apply Datamart on top of it.
Datamart allows you to create DAX measures and relationships between your tables, and all datamarts automatically generates a dataset in the end (with all your stuff inside, of course).
As a result you have a datamart that you can connect to, from a PBID.
Connecting to a datamart from PBI Desktop (PBID) is like connecting to a dataset in your workspace, you will see your tables, you relationship diagram, but you wont see underlying data, nor have power query available to assess data, and you also cannot pick and choose which tables to load.
Each datamart generates a single dataset, therefore make sure that your dataset is complex and large enough so people can explore and find insights from it. However, if it start to become too large, maybe its a better idea to split it into smaller datamarts/datsets.
Where are we at?
We are here:
We just finished build dataflows and datamarts, so we have proper tables setup with proper relationships, and with proper DAX measures.
Power BI Desktop as Data Viz Layer
You data is curated and your measures are ready to use, all you have to do is, open your PBID, and connect to your datamart and create your visuals.
You can even create your own measures if you want. You cannot create columns, if you need new columns, you will have to ask for your data modelers to create if at dataflow level.
Once your report is create you can publish to any workspace you have access to.
And yes, you need to have at least build permission to access your datamarts’ datasets and create your reports, but your users don’t, just give them viewer access and distribute reports and dashboards through apps or in workspaces.
FAQ
Why don’t you connect Power BI Desktop to Dataflows directly?
Well, you can do that if you want, but that will create a scenario like this:
Notice here that the management of your DAX code and relationships will be scattered across different PBID files, which can make it harder to govern.
Ok, you didn’t convinced me, I still want to connect directly to the Dataflow using PBID and publish different datasets in different workspaces.
You can do that too, but that might put yourself in a very difficult position, because that will make harder to govern all your datasets. Also, how do you manage all different DAX measures? By doing that it makes it easier for someone to say: “my total amount is different from workspace A to workspace B, why is that?”.
To prevent this kind of problem is why we have centralized, governed, datasets, so you can make sure that all important measures will always match, whenever they are.
References
Power BI Whitepapers https://learn.microsoft.com/en-us/power-bi/guidance/whitepapers
Lots of RADACAP blogs and videos https://radacad.com/getting-started-with-power-bi-datamart
Pedro Carvalho 