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 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.
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;
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.
Then under Server settings, you’ll find the Connection string;
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.
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.
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.
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.
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.
The report will automatically connect to the tables in the dataset, and if there are any measures defined;
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.
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.
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)
Also in the Data hub, when you click on the Datamart, you can see all the reports generated from it.
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;
- 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 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.