Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Did you hear? There's a new SQL AI Developer certification (DP-800). Start preparing now and be one of the first to get certified. Register now

gautamgb
Microsoft Employee
‎06-23-2022 12:40 PM

Create a Power BI datamart in minutes

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_gifDatamart_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_datamartsSupported_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_creationNew_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_datamartsPower_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_credentialsData_source_connection_settings_and_credentials
Data source connection settings
  • 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-DataChoose_data_screen_for_Get-Data
    Choose data
  • On this step, define your data transformation using the Power Query transformation capabilities. Once done, click ‘Save’ to ingest data.
    Data_transformation_for_Get-DataData_transformation_for_Get-Data
    Data transformation
  • 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.
  • 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_datamartsData_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_ViewsDatamart_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_relationshipsModel_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_MeasuresModel_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_configurationDatamarts_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_LabelsDatamart_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_datasetWorkspace_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!

    Labels:
    0