Skip to main content

Create a Power BI datamart in minutes

Headshot of article author Gautam Bharti

We announced the preview of datamarts at //Build on May 24th, 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.

Datamart Overview gif
Datamart Overview

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

Supported workspace types for datamarts
Supported workspace types

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.

'New' Datamart creation
‘New’ 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.

Power Query connectors for datamarts
Datamart Get Data Connectors

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’.

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

    Choose data screen for Get-Data
    Choose data
  4. On this step, define your data transformation using the Power Query transformation capabilities. Once done, click ‘Save’ to ingest data.

    Data transformation for Get-Data
    Data transformation
  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.
Data view for datamarts
Datamart data view

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.

Datamart Editor Views
Datamart Editor Views

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!

Model view- Edit relationships
Relationships edit

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.

Model view- Create Measures
Create measures

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.

Datamarts Row Level Security configuration
Row Level Security
  • 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.

Datamart Settings- Sensitivity Labels
Sensitivity Labels

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.

Workspace view- of datamarts and auto-generated dataset
Workspace- Datamart and 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

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