Using Power BI is simple, however, using it properly, needs a good architecture. In a multi-developer tenant, the architecture of a Power BI implementation should allow multiple developers to work on the same solution at the same time. On the other hand, the Power BI architecture should be designed in a way that different layers of that can be decoupled from each other for better integration. In this article, I’m explaining how Dataflows and Shared Datasets can be helpful in building such architecture in Power BI. To learn more about Power BI, read Power BI book from Rookie to Rock Star.
Challenges of a Single PBIX file for Everything
Before I start explaining the architecture, it is important to understand what is the challenge and then think about how to solve it. The default usage of Power BI is involving getting data imported into the Power BI data model and then visualizing it. Although there are other modes and other connection types, however, the import data is the most popular option. There are, however, some challenges in a model and a PBIX file that has everything in one file. here are some;
- Multiple developers cannot work on one PBIX file at the same time. Multi-Developer issue.
- It would be very hard to integrate the single PBIX file with another application or dataset. High Maintenance issue.
- All data transformations are happening inside the model, and the refresh time would be slower.
- The only way to expand visualization would be adding pages to the model, and you will end up with hundreds of pages after some time.
- Every single change, even small change in the visualization, means deploying the entire model.
- Creating a separate Power BI file with some parts of it referencing from this model would not be possible, as a result, you would need to do a lot of duplicates, and high maintenance issue again.
- and many other issues.
If you are the only Power BI developer in your organization, and you are working on a small model, which won’t grow into multiple Power BI files in the future, then you can keep going with having everything in one file. However, I do not recommend everything in one file if the scenario is different.
Dataflow to Decouple the Data Preparation Layer
Power Query is the tool for data preparation in the world of Power BI. Based on my experience and also what I have seen from other implementations, for a proper data model, you will need to spend 70% of your time on data preparation. That means if you are doing a Power BI implementation for 10 months, then seven months out of that was spent on data preparation with Power Query! You don’t want that time to be wasted and duplicate for another work. You want to maintain the efforts you have done for your data preparation.
Instead of doing your data preparation as part of a Power BI file, which then will link that work only to that file, you can do it through Power BI dataflow. Power BI Dataflow is running Power Query independently from Power BI files. The independence of dataflow from Power BI file means that you can do data preparation for one entity only once, and use it multiple times! In other words, you can use dataflow to decouple the data preparation (or ETL) layer of the Power BI system, from the Power BI file. after doing the data preparation using dataflow, then Power BI files can get data from it using Get Data from Dataflow.
Here is the diagram of dataflows and Power BI files:
I have written many articles about Dataflows, which I strongly recommend you to read;
- What is Dataflow and Use Case Scenarios of that in Power BI?
- Creating your first Dataflow
- What is the Common Data Model and Why Should You Care?
- What is Computed Entity and Linked Entity?
- Workaround for Computed Entity and Linked Entity in Power BI Pro
- How to Use Dataflow to Make the Refresh of Power BI Solution Faster
- Move your Shared Tables to Dataflow; Build a Consistent Table in Power BI
Shared Dataset for the Data Modeling Layer
Although dataflow creates a shared data source for Power BI models. Still, if you want to create multiple Power BI models, you then need to set field level formatting inside the Power BI file. You do need to create DAX calculations inside each file, and you need to create hierarchies and any other modeling requirements inside the Power BI model. Because the storage of dataflow is a file-based structure, which is totally different from the Power BI in-memory storage engine.
Instead of doing all the modelings, and calculations in each Power BI file separately, you can leverage the Shared dataset concept. Shared dataset concept means that you create a Power BI file with a model only, and no visualization pages.
Get Data from Power BI dataset for the Data Visualization Layer
After publishing that dataset, then you can use Power BI Desktop to get data from that dataset using the Get Data from Power BI dataset. This would be a live connection to the Power BI dataset hosted in the service.
Here is the diagram of the Power BI shared model;
As a result, here is the proposed architecture diagram
In this architecture, we are using layers as below;
- Dataflow for the ETL or data preparation layer
- Shared dataset for the data modeling layer
- Get data from Power BI for the visualization layer
Benefits of this Architecture
Every architecture plan has pros and cons, let’s discuss some of the benefits of this architecture
Decoupling data preparation
The development time you put using Power Query will be preserved and can be used in other Power BI models. As the dataflow stores the data in Azure Data Lake. Decoupling the data preparation from the modeling means that you can apply changes in either side with the minimum effect on the other side. Moving shared data tables to Power Query dataflow is a recommended approach that I wrote about it in another article.
Faster Refresh Time for Power BI model
You move the heavy lifting work of Power Query to the dataflow and as a result, the part of Power BI dataset refresh would be a simple load only. I have written about it previously and compared through a scenario that how it can make the refresh time of the Power BI model faster.
Dataflow is not a core developer tool
You can use SSIS or Azure Data Factory or other ETL tools to take care of the data preparation. However, the power of Power BI resides in the simplicity of it. Everyone with fair knowledge of Power Query can use dataflow too. You don’t need to be a C# or SQL Server developer to work with that tool.
The proposed architecture supports multiple developers at the same time on one Power BI solution. You can have multiple ETL developers (or data engineers) working on dataflows, data modeler working on the shared dataset, and multiple report designer (or data visualizers) building reports. They don’t need to wait for each other to finish their work and then continue. They can work independently.
Re-use of Calculations and Modeling
Having a centralized model will reduce the need for writing DAX calculations again and again. You will do the modeling once, and re-use it in multiple reports. On the other hand, you are sure that all reports are getting their data from a model which is reconciled and fully tested, or in other words, it is a gold data model.
Minimum redundancy, Maximum Consistency, Low Maintenance
A solution implementation that reduces the need for duplicating the code (using dataflows and shared dataset), and re-use the existing code (using get data from dataflow and datasets), will be highly consistent. Maintenance of such implementation would be possible with minimal effort.
What about Enterprise Architecture?
You might already use Power BI in an enterprise solution using Azure Data Factory or SSIS as the ETL tool, and SSAS as the modeling tool and Power BI live connection as the visualization tool. Such architecture is similar to what I mentioned here already. If you already have that architecture in place, I recommend you to proceed with that.
Implementing a Power BI solution is simple. However, maintaining it is not! If you want to have a low maintenance, highly scalable, consistent and robust Power BI implementation, then you need to have a proper architecture in place. In this article, I explained the benefits of a proposed architecture that leverages dataflow for data preparation layer and shared dataset for data modeling layer. The proposed architecture will have the minimum maintenance efforts, it is highly scalable. And I strongly recommend you to consider using it in your implementations. Do you have any thoughts about this architecture or any questions? please let me know in the comments below.