Power BI Datamart Components

Power BI Datamart components

What is a Power BI Datamart underneath? Can you connect to the database generated by Power BI Datamart? how the Dataset associated with the Datamart can be used? Is there a linage view? In this article and video, I’ll explain These and you will learn about the components of a Power BI Datamart. If you are new to Power BI Datamarts, this article explains what it is and its use cases and this article gets you through the Datamart editor and your first experience with it.

Video

Power BI Datamart Under the Hood

When you create a Power BI Datamart, Three objects behind the scene is created; A dataflow, an Azure SQL database, and a Dataset. Some of these components are exposed as individual components, but some are hidden.

Power BI Datamart components

Power BI Dataset

When you create the Power BI Datamart, the most obvious component of it can be seen totally separately, and it is the Power BI Dataset.

Dataset associated with the Power BI Datamart

You can also connect to the Dataset using Power BI Desktop (like a normal dataset), or using the XMLA endpoint, which can be found under the settings of the Dataset;

Power BI Dataset settings

This connection string then can be used in SSMS, Visual Studio, Tabular Editor, Power BI Helper, and many other tools to connect to the Power BI dataset.

The Dataflow and the Azure SQL Database are not that obvious to see inside the Power BI service (apart from the Datamart Editor). But there are ways to connect to them.

Connection to Azure SQL Database

To find the connection to Azure SQL Database, click on the more options on the Datamart and select Settings.

Power BI Datamart Settings

Then under Server settings, you’ll find the Connection string;

Finding the Azure SQL Database connection from Power BI Datamart

This connection can be used in other tools, such as SSMS (SQL Server Management Studio). Please note that you should be using Azure Active Directory – Universal with MFA for Authentication and use your Power BI account as the username.

Connecting to Azure SQL Database of Power BI Datamart

You can then see the database with the objects in it. The tables are hidden at the moment for extra safety measures. However, there is a view per table that you can see, plus other metadata tables such as relationships.

Power BI Datamart’s database structure

Similar to any Azure SQL Database, you can view the script of the Views, such as the “model.Customers”, which is represented below. You can see that even in the script, the Row-Level Security implementation can be visible.

Views in Azure SQL Database of Power BI Datamart

There are some limitations applied to ensure you don’t change anything in this structure that causes the Datamart to fail.

Building Reports from Datamarts

To create reports from the Datamart, there are multiple ways. You can go to the Data hub, then click on the Datamart.

Datamarts in Power BI Data hub

In the Datamart details, not only you can see the SQL connection string again, but also you can create a report from Scratch, which will be connected to this Datamart.

Create a report from Power BI Datamart

The report will automatically connect to the tables in the dataset, and if there are any measures defined;

Creating Power BI Report in the Power BI service from Power BI Datamart

The report, can be also created from the Dataset associated with the Datamart, or from the Power BI Desktop by Get Data from Power BI Dataset.

Get Data from Power BI Dataset

Lineage View

If you are on the Datamart, or on the Dataset associated with it, or any of the reports that is connected to that dataset, you will be able to see the Lineage view.

Lineage View of Power BI Datamart

In the view below, you can see that the Datamart is populating data from an OData source, and feed it into a Dataset, and there are three reports generated from that dataset. The Dataflow part of the Datamart, and the Azure SQL Database of that is hidden from this diagram (they are parts of the Datamart)

Power BI Lineage View

Also in the Data hub, when you click on the Datamart, you can see all the reports generated from it.

Datamart details in Power BI Data hub

Summary

In this article and video, you’ve learned what is under the hood of the Power BI Datamart; a dataflow, an Azure SQL Database, and a Dataset. You can connect to some of these components using other tools. In this article, you’ve learned some methods to do that. You can also build a report that connects to the dataset created by the Datamart, and also see the entire Lineage View.

In the next article, you will learn about how Datamart works with other components in the Power BI ecosystem, such as endorsements, sensitivity labels, sharing, deployment pipelines and etc.

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.

Leave a Reply