I have previously written articles on the What, How, When and Why of Power BI Datamarts and Power BI Dataflows. Have a look below if you want to get a quick overview of the two features of Power BI Service.
But when should you use what?
Power BI Dataflows vs Power BI Datamarts
Let’s revisit the When of both Power BI Dataflows and Power BI Datamarts!
|Use case||Power BI Dataflow||Power BI Datamart|
Tables that are reused throughout your organization
|Dataflows are particularly great if you are dealing with tables that you know will be reused in your organization, e.g. dimension tables, master data tables or reference tables.||You can also reuse a datamart, but it is unnecessary to build a datamart to solve this use case.|
Azure Machine Learning and Azure Cognitive Services
|If you want to take advantage of Azure Machine Learning and Azure Cognitive Services in Power BI this is available to you through Power BI Dataflows. Power BI Dataflows integrates with these services and offers an easy self-service drag-and-drop solution for non-technical users. You do not need an Azure subscription to use this but it requires a Premium license. Read more about ML and Cognitive Services in Power BI Dataflows here.||When looking through Power BI Datamarts today I cannot see this functionality easily available. Dataflows was however designed to solve this use case and is, in my opinion, a good place to start.|
|Power BI Dataflows provides the possibility to incrementally refresh your data based on parameters to specify a date range. This is great if you are working with large datasets that are consuming all your memory. However, you need a premium licence to use this feature.||It is also possible to set up incremental refreshes for your separate tables in your Datamart. If you have a couple of large tables within your datamart, this could be a nice feature to take advantage of.|
Ad-hoc SQL querying and data exploration
|You can explore your data through a dataflow, but it is not possible to run SQL queries with dataflows.||Datamarts are particularly great if you want to do ad-hoc querying or data exploration of your data as sort, filter, and do simple aggregation visually or through expressions defined in SQL|
Self Service Data modelling
|Dataflows do not support setting up relationships between tables, building measures or writing DAX.||A great thing with Power BI Datamarts is that you can model your star schema right in Power BI Service. That way you do not have to wait for the data warehouse to make smaller (or larger) improvements or changes to your data model as you can do these changes yourself – but remember that permanent transformations should be moved as close to the source as possible. This also enables Mac users to do some modelling in Power BI Service.|
Need to connect to your data in Power BI Service through a SQL endpoint.
|Not possible with dataflows.||Power BI datamarts provide a SQL end-point to your data. This is great if that is a requirement from developers or data analysts. You can then use database tools as SSMS to connect to your datamart as any other DB, and run queries.|
Let me know what you think and if you have other use cases where the tools should be compared.
- Guy in a cube: https://www.youtube.com/watch?v=-ZAPAk_ANbk
- Power BI Datamarts:
- Microsoft documentation: https://learn.microsoft.com/en-us/power-bi/transform-model/datamarts/datamarts-overview
- My friend Marc has written a great article on the topic here: https://data-marc.com/2022/09/13/power-bi-datamarts-should-i-use-it-or-not/
- Or if you would like short visual introduction through Guy In A Cube: https://guyinacube.com/2022/05/25/exploring-the-preview-of-datamart-in-power-bi/
Leave a Reply