Power BI Datamarts

As mentioned in my previous blog about the Microsoft Build event announcements, the biggest news was Power BI Datamarts. This is a new self-service capability included with Power BI Premium (premium capacity or per user) that enables users to uncover actionable insights through their own data without any help from IT teams. You can quickly and easily build a relational database for analytics using no-code experiences for workloads up to 100GB. Think of a datamart as a mini data warehouse for a department.

It is a combined data prep and model authoring experience built-in to the Power BI Service. It does this by bringing together dataflows and data modeling, with an automatically provisioned Azure SQL Database behind the scenes storing the relational database and powering everything. So you ingest data from different various sources and extract, transform, and load (ETL) the data using Power Query via a special dataflow into an Azure SQL database that’s fully managed and requires no tuning or optimization. This differs from a regular dataflow which just creates a data model that is stored with the report.

Once data is loaded into a datamart, you can then create relationships/measures/calculated tables using the new model authoring experience. It will also automatically generate a Power BI DirectQuery dataset pointing to the datamart which can be used to create Power BI reports and dashboards (even though it does not support an Import dataset, it is still very fast). What’s more is that you’ll get a T-SQL endpoint so you can query the underlying data inside Power BI or outside Power BI (i.e. SSMS, Azure Data Studio). The dataflow you setup to populate the datamart can be refreshed on a schedule (and incremental refresh is supported). All the above is editable via a single web UI and treated as one package. This makes a very compelling argument to keep the simpler workloads within Power BI – reducing complexity and lowering the barrier to entry for many users.

Some important benefits of a datamart:

A datamart can be built from source systems, or it can be built from an enterprise data warehouse created by IT for the case when you want a subset of the data warehouse where you can run your own queries on a much smaller data model
It cuts through several barriers that users face when they want to set up a reporting solution. Instead of having to get access to an Azure subscription to set up an Azure Data Factory resource and a Azure SQL Database resource, you can now just do it with the click of a button
It can be a better option than storing data in a data lake (which dataflow does now by storing data in CDM format in a data lake), as loading a dataflow into an Azure SQL Database allows you to assign roles and row level security, as well as allows for simple connections from a variety of tools, and has faster performance
Because it has a brand new web experience for data modelling and measures authoring, no Power BI desktop is required (this means Mac users don’t have to run Power BI Desktop in a Windows emulator anymore)

To create a datamart: Log onto app.powerbi.com and go into a premium workspace (premium capacity or per user). On the home page of a workspace, click New -> Datamart. Then choose “Get Data” and connect to a data source. Then choose the data you want to import into the datamart, and you will be taken to Power Query. Then a datamart and a DirectQuery dataset will be created (both with the same name). You will then be taken to the datamart workspace (visual designer) where there are four tabs:

Data – See the data in the tables, and where you can create a new measure (via DAX) or setup incremental refresh, and do filtering and sorting
Design – Create a query to view the data via a Power Query diagram View. This is for users who don’t know SQL – they can write custom queries using this visual query editor
SQL – A visual editor that allows you to create T-SQL queries to view the data. In the future you will be able to save queries
Model – Model the data, import additional data sources, manage roles (row-level security), and create new measures

If you go to the dataset that was created (which pulls data from the datamart), from there you can create a report. Or you can go to Power BI desktop and use the “Azure SQL Database” connection and create a report that way (via the connection string mentioned below). You can also use “Power BI datasets” to connect to the datamart. See Create reports using datamarts.

To use a tool like SSMS to view the datamart: Go to Setting for the datamart, go to “Server settings” and copy the connection string. In SSMS, use that string for the server name, use “Azure Active Directory – Universal with MFA” for the authentication, and after it connects you can go to the Views section in SSMS and choose a view to query the tables in the datamart. See Analyze outside the editor and Connecting to Power BI Datamart’s SQL Server from Desktop Tools and External Services.

Some of the main reasons to use datamarts:

When you are not able to wait for IT to make changes to the enterprise data warehouse (i.e. you want to quickly add budgets and forecasts to the data)
Sort, filter, do simple aggregation visually or through expressions defined in SQL
For outputs that are results, sets, tables, and filtered tables of data
Provide accessible data through a SQL endpoint (so you can use SSMS, Excel, etc)
Enable users who don’t have access to Power BI Desktop

Datamarts do net yet support data manipulation language (DML) to update data in the datamart, or data definition language (DML) to update the database schema’s in the datamart, but will in the future.

I still see use cases where you would use a regular dataflow instead of a datamart: for tables that are often reused within an organization such as a calendar table, a dimension table, or a lookup table.

The bottom line is departments now have a full blown analytics product that allows them to connect to data sources, transfer and clean the data, and create a data mart with a dataset on top. All without writing any code!

More info:

Introduction to datamarts (official docs)

Power BI Datamart – What is it and Why You Should Use it?

Datamarts and exploratory analysis using Power BI

Microsoft Power BI Gets Low-Code Datamart Feature

CREATE END-TO-END SELF-SERVICE ANALYTICS WITH POWER BI DATAMARTS

Power BI Data: Dataset vs. Dataflow vs. Datamart vs. Dataverse vs. SQL Server vs. Synapse – Are You Confused Just Yet?

Video Add Data at Scale | Datamart in Power BI

Dataflows with benefits

Video Exploring the preview of datamart in Power BI! and Power BI dataflows vs datamarts: What’s the difference??? and Answering your datamart questions

Video What is Power BI Datamarts?

First Look at Datamart