Create a Power BI datamart in minutes

We announced the preview of datamarts at //Build on May 24^th, 2022. This is one of the biggest releases on the Power BI platform.

In this blog we will go into the details about datamarts, and how to get started.

What is datamart in Power BI?

Datamart in Power BI is a managed self-service relational analytics solution, which is backed by Azure SQL DB and enabled by end-to-end low code experiences! Datamart enables all business users to self-serve and drive faster insights into data, removing dependency on IT. Users also have the flexibility to directly access and query the Azure SQL DB (datamart backend) both from inside the datamart editor (browser-based experience) and other ‘SQL-aware’ clients.

NOTE: Datamart in Power BI is offered only for Premium Gen 2 (PPU/PPC) workspaces. Make sure your workspace is using Gen2 capacity.

Skip this if you already have access to a PPU/PPC (Gen2) workspace.

If you don’t have a Power BI account, go to app.powerbi.com and sign up for an account. If you have a Power BI account but don’t have PPU/PPC license, sign up for a 60 day trial of Premium Per User (PPU) license.

How to get started?

In this section, we’ll talk about how to quickly

1. Create datamart
2. Model datamart for downstream consumption
3. Query datamart using no code and SQL experiences
4. Set up Row Level security and Sensitivity labels

Let’s get started!

Create datamart

With your account set up, navigate to an existing (or create a new) Power BI premium Gen2 workspace. Click on ‘New’ and select datamart.

Once provisioned (provisioning is fast- in seconds!), your datamart is ready. Use the ‘no code’ Get-data experience to import data. Your datamart allows you to import data from multiple sources and set up recurring refresh schedules, along with incremental refresh.

To quickly try datamarts, you can use sample data sources listed here. For today’s demo, we will use the Northwind Traders OData feed.

Follow the steps to import the data from the Northwind data feed:

1. Click on Get Data and choose the Odata data connector.
2. Enter the Northwind URL (https://services.odata.org/Northwind/Northwind.svc) and leave connection as anonymous. Depending on your data source you will need to enter your connection credentials. Click ‘Next’.

   

3. On this next ‘Choose data’ screen go ahead and select the source tables that you want to import. Click ‘Transform data’ once done.

   

4. On this step, define your data transformation using the Power Query transformation capabilities. Once done, click ‘Save’ to ingest data.

   

5. You can see the data load progress through the loading progress indicator- at this stage feel free to wait or navigate away- come back once your data load is finished.
6. Once your datamart creation is complete, the data view will open, and you can see the data that was ingested in your datamart.

To set up your datamart refresh, please refer to this documentation.

Navigating between various datamart editor views is easy, simply use the view panel at the bottom left to navigate.

Model datamart and downstream consumption

With the data now available in datamart, you can start modeling our data to enable downstream consumption experiences. There are two major updates to cover for this section:

Create Relationships

On navigating to model view, you will notice your datamart automatically imported the relationships from source.

To create new relationships, all you need to do is to drag and drop the field from one table to the respective field on the other table. You will see a pop-up dialog for the relationship attribute definition. Configure the relationship and click ‘Confirm’ to save your relationship. You will see the new relationship in the model view canvas. To edit relationships, simply click on the relationship line connecting the tables!

Create Measures

In the model view, select the table you want to create a measure in and click on the ‘New measure’ option in the main ‘Home’ ribbon. You can breeze through the measure definition leveraging the power of DAX intellisense. Once done, click on ✔ to save your measure.

Both your measure and relationship are now available for downstream consumption.

Set up Row Level security and Sensitivity labels

Admin and governance controls that Power BI gives you for datamarts are both easy and powerful. Let’s quickly set up RLS and Sensitivity labels.

• Row Level Security (RLS) for datamart

Open your datamart and find the RLS role creation and management option in the Ribbon. Click manage role to create roles and assign them. Finally, you can use the ‘View as’ option to see the data access for the created roles. It’s that simple.

• Sensitivity Label

Go to the datamart settings (via workspace) and expand the Sensitivity label to access the controls for sensitivity labels. Choose a label you want to apply, and you can choose to enable automatic application of the sensitivity label to all datamart downstream content.

You can navigate back to your workspace to find your datamart, and the autogenerated dataset (both have the same name). Start creating new reports by using the context menu options or from within the auto generated dataset.

 

Well, that concludes this how to blog- hope you were able to follow along and create your first datamart. We are excited to see how you leverage datamart and its capabilities!

Next Steps

• Share your feedback through Ideas
• Read about our public preview announcement: Announcing public preview of datamart in Power BI | Microsoft Power BI Blog | Microsoft Power BI

Learn more about datamart here and take it for a spin!