With the emergence of various tools within the Power BI platform, we’re increasingly being asked what the difference is between all these tools and what the best architecture is for different clients. Of course, there’s no one-size-fits-all answer to that, but I can explain what each tool does and how to best use them.
Different phases
The image below clearly shows the different phases involved before you can create a Power BI report or dashboard. Not every tool is suitable for all of these phases. For each tool, I’ll briefly explain what it is and isn’t suitable for.
- Data Access
- Data Transformation
- Data Modeling
Power BI Dataflow
With a Power BI Dataflow, data from various sources can be accessed and loaded into an Azure Data Lake. You can read more about this in my other blog post. Power BI Dataflow. The image above illustrates this clearly once again. Data can be retrieved from various sources, and transformations can be applied to the data as needed. Phases 1 and 2 can therefore be carried out using this tool.
Power BI Datasets
A Power BI dataset has actually been available since the very beginning of the Power BI era. Data from various sources can be loaded into a dataset. Data can be loaded directly from the source, or it can be loaded from a data flow or from SSAS. Transformations can then be applied to this data. The model can then be created within the dataset. Modeling is essentially the phase in which the various exposed tables are linked together. With a Power BI dataset, you can therefore go through all the different phases.
SQL Server Analysis Services
SQL Server Analysis Services is specifically designed for modeling. This means it can be used to go through all the different phases. SSAS can be run on-premises or subscribed to as a service in Azure.
What's the Difference Between a Dataflow and a Dataset?
You cannot model data using a Dataflow. That always requires a Dataset. Both a Dataflow and a Dataset can be used to make data accessible, so why would you choose a Dataflow over a Dataset? Some advantages of using a Dataflow are:
- Reuse of entities across different datasets. Tables can be reused. This means transformations only need to be performed once, and the data will then be the same across all datasets.
- Incremental data loading. Large tables can be loaded incrementally.
- Performance. By shifting the logic to a data flow, it can be processed online. This means that not all data needs to be loaded into Power BI anymore. This can sometimes be problematic with large datasets.
Why SSAS
There are also a number of reasons why SSAS will simply be chosen as the modeling tool.
| Data Flow/Dataset | SSAS | Comments | |
| Self-Service | ++ | – | Data modeling is more accessible within the dataset because this interface is the same as Power BI |
| Row-level security | – | ++ | This can be configured centrally within SSAS; otherwise, it must be configured on a per-dataset basis. |
| Version Management | – | ++ | SSAS / Visual Studio is designed for this purpose. As of now, this is not yet possible within Power BI. |
| Debug/Performance | – | ++ | SSAS offers many options and features for optimizing and analyzing performance. In Dataflows, these capabilities are still very limited and not transparent. |
| Partitioning | — | ++ | Aside from whether or not to load a data flow incrementally (based on a date field), there are no options for, say, partitioning the data |
| Perspectives | + | ++ | The ability to use perspectives is currently still limited within datasets |
| Browser-based | ++ | — | No additional tooling is required to develop Dataflows/Datasets |
| Scalability | + | + | Depending on the chosen SSAS architecture, scaling up is easy in both cases |
Conclusion
Of course, every organization must make a well-considered choice regarding the most appropriate BI architecture. At present, SSAS still offers many features and capabilities that are either missing or insufficient in dataflows and datasets. Nevertheless, depending on the organization, the use of data flows and datasets can constitute a sound BI architecture.
