The Power BI Flex Edition #3 - The 3 D's: Dataflows, Datasets, and Datamarts

Hi everyone and welcome back to the Power BI Flex! For Edition #3, let’s discuss the three D’s: Dataflows, Datasets, and Datamarts.? When deploying Power BI to your enterprise, it’s best to plan out your data strategy.? This means considering your strategy outside of just Power BI, but also within your Power BI environment.? For this blog, let’s consider why and how’d you want to use these different types of artifacts.

?

Dataflows

Dataflows are a repeatable ETL (extract, transform, load) process that you can use to gather your data.? When you are in Power BI Desktop, and you can go to “Transform data”, it will open a new window called Power Query.? Power Query pre-dates Power BI.? It also exists in other tools as an add-in, like in Excel.? The idea behind Power Query is to give the user an opportunity to connect to an outside source, do your transformations and clean up of the data, and then load it to your model.? When we do this in Power BI Desktop, this strategy works fantastic.? But why limit ourselves to just the 1 model?

A common issue that arises in this industry is having multiple sources of the truth.? With dataflows, Microsoft has solved that for us!? Dataflows exist in Power BI Service online, but functionally, they work the same way as Power Query in Power BI Desktop. ?You can connect to an outside source, perform your transformations, and load your data.? The big difference is this is a single table or component.? You can now use “Dataflows” as your source in Power BI Desktop and load your data from the dataflow into your local model.? This gives you the ability to have a single source of truth, and not have to perform repeat calculations across different datasets! This is EXTREMELY powerful!

In addition, dataflows are accessible via other tools like excel as well.? So if your analysts would like the ability to access the data that exist in those dataflows, that is an option for them.

?

Datasets

A dataset is a collection of tables that have been loaded, along with the modeled relationships, DAX measures/columns, etc.? Datasets can be used over and over in many different workspaces, depending on the type of report and visualization you want to use.? Depending on the size of your enterprise, it may make sense to have 1 master dataset that your reports use, or to have different datasets that different types of reports can use.

Datasets can import directly from outside sources, but by using dataflows as the source, you will have a single source of truth, which is very important for reporting.

Datamarts

A datamart is a smaller section of data from your data warehouse – literally a mart of data.? That could be HR data, sales data, logistics data, etc.? On the backend, its a managed database that will allow you to bring your data into your model in Power BI Service and store it in an Azure SQL DB.? This will improve performance and also give you a SQL endpoint to do your own analysis.

Datamarts are geared toward storing your data in an accessible way for self-service scenarios, such as business analysts trying to query your data.? By adding the additional layer of a datamart, you protect your data warehouse and allow your analysts to query via the SQL endpoint.

I hope everyone enjoyed this edition of the Power BI Flex! Stay tuned for next time where we will review the coolest Power BI visual element: Field Parameters!

?