Getting Started with Power BI Datamart

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.

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

Creating a Datamart

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

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

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.

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

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

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.

The Data then will get loaded into the 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

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.

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.

This is what you see in the second tab.

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;

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

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.

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.

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.

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

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

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;

• Power BI Datamart – What is it and Why You Should Use it?
• Getting Started with Power BI Datamart
• Power BI Datamart Components
• Power BI Datamart Integration in the Power BI Ecosystem

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

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.