Getting Started with Power BI Datamart

Getting started with Power BI Datamarts

In the previous article, you learned what is a Datamart and the use cases of that in a Power BI implementation. In this article and video, you will have your first experience with Datamarts and learn through an example about what it is and how it works in detail. The interesting thing in all the steps below is that you just need a web browser to build the datamart.

Video

Premium Workspace

Power BI Datamart is only accessible through a Premium workspace. You either need to have a Premium capacity workspace or create a workspace using a Premium Per User (PPU) account. If you don’t have a PPU account, you can easily apply for a 60 days trial through the Power BI service.

Creating a workspace for testing Power BI datamart

A workspace with Premium settings usually have an icon representing it.

Power BI premium workspace

Creating a Datamart

Click on the New, and from the list, select Datamart.

Creating Power BI datamart

Please note that the ability to create Datamart can be enabled or disabled through the Tenant Settings of the Power BI admin portal.

Enabling Datamart in the Power BI tenant

Get Data: Power Query Online

When you create a Datamart, the first thing is to choose where to get data from. You can start by getting data from a dataflow, Excel or SQL Server, or pretty much any other data source that Power BI offers a connector to it (more than 150 sources) by selecting the Get data from another source.

Get data for building a Datamart

This will open the familiar Dataflow‘s get data window where you get to select the source.

Data source connectors in Power BI Datamart

For this example, I’ll use OData as a source and this URL: https://services.odata.org/Northwind/Northwind.svc

OData source for the Power BI Datamart

Choose the data tables you need. I am using Orders tables and some related tables to it.

In the next step, you will see the Power Query Editor online version, which will give you the ability to do all the transformations you need.

Power Query Editor online

The Data then will get loaded into the Datamart.

Loading data into Power BI Datamart

Datamart Editor

Once the data is loaded into the Datamart, you can see the first glance of Power BI Datamart editor. This editor will enhance a lot in the future I believe. However, even right now, the editor has some mind-blowing features and capabilities. I explained it in a few categories below;

Data tab

Power BI Datamart editor

The first tab is the Data View, which is similar to the Data tab in the Power BI Desktop. This is the place where you can see the data rows in each table. That is the view you see in the screenshot above.

You can also get more data into this Datamart using the Get data option, or user Transform data to get back to the Power Query editor. You can also Enter data directly as a table if you want.

Get data and transform options in Power BI Datamart

Creating New Query

An interesting ability in the Datamart is the creation of new queries. A new Query is like a new table generated through the Power Query process and steps. It might be a combined version of some table or just a transformed version of one table. You can of course do that already in Power BI Desktop using the Power Query Editor. However, the reason it is named as a New Query here is that behind the scene, this will be created as a VIEW in the Azure SQL Database.

Data transformation using Power Query Editor online to create a new query in the Power BI Datamart

This is what you see in the second tab.

Creating New Query in Power BI Datamart

Writing T-SQL Queries

Power BI Datamart, as I mentioned in this article, is creating an Azure SQL Database behind the scene. You can write queries in T-SQL statements if you want in the third tab of the Power BI Datamart;

Writing T-SQL queries on Power BI Datamarts

This is where Power BI not only enables citizen data analysts but also developers to use this component.

Model Diagram and Relationship Editor

The unified UI for the Datamart not only enables you to write T-SQL queries, see the data, and transform data. But it also enables you to define the relationship and build the diagram of the model, adding configurations for fields and tables respectively all in one web UI.

The Model tab in the Power BI Datamart Editor

The interesting fact here is that you create relationships only once, but the Datamart behind the scene will create it in both the Azure SQL Database and also in the subsequent Power BI dataset.

Creating Measures and Writing DAX Expressions

You can create measures through the Datamart editor, and the expression editor will help you with the DAX function library and the IntelliSense to write whatever you want.

Writing DAX measures in Power BI Datamart

Incremental Refresh: One Setup for all

Incremental Refresh gives you the ability to set up a delta load rather than loading the entire data. So far in a solution that you use Dataflow and Dataset separately, you need to set incremental refresh in them one by one. However, here, using the Datamart Editor, you set the Incremental Refresh once, and it sets it everywhere for you.

Incremental Refresh setup in the Power BI Datamart

Row-Level Security: In both Database and Dataset

Another Interesting feature in the Power BI Datamart is that you don’t need to set up the Row-Level Security in two different places (The database and the dataset). You just set it once here in the Datamart, and it would implement it in both places for you.

Row-level security setup in Power BI Datamart

The Roles and the assigning of the roles to users, all happen in one place. Unlike the Power BI Desktop for role creation and then Power BI service for assignment. However, the difference is that you don’t use DAX expressions for RLS. This is due to the fact that these RLS settings are originally for the Azure SQL database and then Dataset will follow that respectively.

You also have the option to test and view a specific role if you want.

Create roles and test them using the Power BI Datamart editor

You can rename the Datamart by clicking on the current title and changing it.

Renaming the Power BI Datamart

Congratulations, you’ve built your first Power BI Datamart.

Power BI Datamart in Service

Summary

In this article and video, you’ve learned how to create a Power BI Datamart, and what are the features it includes inside the Datamart editor. As you have seen through an example, there is a single unified editor experience from Getting data, and transforming it, to writing queries, creating relationships, and event managing things such as Row-Level Security.

In the next article, you will learn about what is under the hood of the Datamart and how you can accesss to different parts of it.

Here are my Power BI Datamart article series for you to learn about it;

I provide training and consulting on Power BI to help you to become an expert. RADACAD team is helping many customers worldwide with their Power BI implementations through advisory, consulting, architecture design, DAX support and help, Power BI report review and help, and training of Power BI developers. If you need any help in these areas, please reach out to me.

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.

4 thoughts on “Getting Started with Power BI Datamart

Leave a Reply