The What, How, When and Why on Power BI Datamarts!
- What are Power BI Datamarts
- How can you set up Power BI Datamarts?
- When should you use Power BI Datamarts?
- Why should you use Power BI Datamarts?
What are Power BI Datamarts
Power BI Datamarts are a self-service analytics solution that provide a fully managed database that enables you to store and explore your data in a relational and fully managed Azure SQL DB.
That means that you can connect your sources, transform these, set up relationships between the tables and build measures – resulting in a data model in Azure SQL database that you can connect to as any other database.
Datamarts are not a new thing though. A datamart in the world of a data warehouse is the access layer containing a focused version of the data warehouse for a specific department that enables analytics and insights to the business. A datamart could be a star schema designed to provide specific KPIs.
Hence, in Power BI Datamarts we can now build this access layer for specific business domains in Power BI Service as a star schema with relationships and measures.
If you want to learn more on Power BI Datamarts, you can read the documentation from Microsoft here.
How can you set up Power BI Datamarts?
You set up a Power BI Datamarts in Power BI Service. This is done through the workspace you want to hold the datamarts and by clicking “New”.
You then do the following:
- Choose source type and connect to your source
- Load the data source and transform this (if you want to) in Power Query
- Then the data source is loaded into a datamart. You can now do the following based on what you want and need to do to your data:
- Set up relationships
- Build measures
- Run queries with SQL
- Run queries using low-code functionality
You need premium capacity or premium per user to get access to Datamarts.
When should you use Power BI Datamarts?
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
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 datamodel as you can do these changes yourself. If these changes should be permanent or a in between solution while one wait for the datawarehouse depends on the governance that is set up.
In addition, 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.
Why should you use Power BI Datamarts?
Power BI Datamartscan help us solve a range of challenges with self-service BI. Many of these are similar gains as one could get from Power BI Dataflows.
- Improved access control as you can provide data analysts with access to the datamart instead of direct access to the data source
- One source of truth for business logic and definitions
- Provides a tool for standardization on the ETL process
- Enables self-service BI for non-technical users
- Enables reusability
Specific worth from datamarts (compared to Power BI dataflows) are:
- Self-service solution for quering and explore data for data analysts, as well as for non-technical users as you can query the datamart using low-code functionality
- Reduce time to production if the alternative is to wait for the needed changes or development to be delivered through the data warehouse. Also, Datamarts developers do not need code experience, and can ingest, transform and prepare the models using existing knowledge from Power Query and Power BI Desktop.
- Power BI Datamarts support row-level-security (where Power BI Dataflows do not)
Useful links:
- Microsoft documentation: https://learn.microsoft.com/en-us/power-bi/transform-model/datamarts/datamarts-overview
- My friend March 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/
What are Hill Sprints?
I am having a series called hill sprints (since we are climbing mountains – hehe) that will provide a to the point introduction on a topic covering the What, How, When and Why.
Why hill sprints?
Hill sprints are essentially a form of interval training – probably one of the more intense (but engaging) options. They are quick, brutal and to the point. Let me know if you have another fun analogy towards climbing mountains that would make sense for a series name! (Having way to much fun with this)
First Hill Sprint Series will be on Power BI Service. In this series we will go through some of the main components in Power BI Service, explaining what is it, how can you set it up, when should you use it, and why should you use it.
Hopefully, this can provide some quick insights and knowledge on the components and help decide if this is the tool for you with your current setup or challenge.
Data Ascend 
Leave a Reply