How to connect to a Power BI Datamart in Power BI Desktop

By:   |   Updated: 2022-10-10   |   Comments   |   Related: > Power BI


Problem

Recently, I wrote on how to configure incremental refresh within a Datamart in Power BI service. After creating the Datamart (and configuring the incremental refresh) in the Power BI service, end users might want to connect to the model created in the Datamart in Power BI desktop to further create reports.

A few approaches are available, depending on the end-user or the mode of storage and/or connection required.

Solution

In this article, we will look at four different methods to connect to a Power BI Datamart from Power BI Desktop:

  • Connect Using SQL Server Connector
  • Connect Using Azure SQL Database Connector
  • Connect Using Live Connection via the Data Hub Connector
  • Connect Using Direct Query OR Import Connection Via the Data Hub Connector

To demonstrate the different approaches for connecting to a Power BI Datamart as a source in Power BI Desktop, I will leverage the same Datamart I created off a CSV source file in SharePoint in my previous article mentioned above. The diagram below shows the data within the Datamart.

Image showing Datamart in Power BI Service

Let's now look at the various ways to connect to this table (Datamart) in Power BI Desktop.

Connect Using SQL Server Connector

To connect via the SQL Server connector in Power BI Desktop, you need to select SQL Server, as seen in the diagram below.

Image showing how to use the SQL Server connector in Power BI Desktop

Then, as seen in the diagram below, you need to log in to the Power BI service and navigate to the Workspace you have saved the Datamart on.

Image showing Datamart within Power BI Workspace

Then, click on the ellipses () on the end of the Datamart and select "Settings", as seen in the diagram below.

Image showing how to get to Datamart Settings in Power BI Service

Next, click "Server settings" to expose the Connection string for the Datamart, as seen in the diagram below. Then click "Copy" to copy the connection string.

Image showing how to copy Datamart Connection string

After, head back to Power BI Desktop to paste the copied Connection string into the connector "Server", as shown in the diagram below. Then click "OK".

Image showing how to paste Datamart connection string in a SQL Server connector in Power BI Desktop

Next, on the "Navigator" window that opens, expand and select only the checkbox that starts with "model.DatamartName", as seen in the diagram below. Then you can either select "Transform data" or "Load". For this blog, I will only select "Load".

Image showing how to use the SQL Server connector in Power BI Desktop v2

As you can see from the diagram below, the Datamart table has been stored in "Import" mode. We could have done the same for "Direct Query" mode using this connection approach in Power BI Desktop.

Image showing Datamart table model in Import mode

Connect Using Azure SQL Database Connector

To do this, select the Azure SQL database connector within Power BI Desktop, as seen in the diagram below. I have used the search pane to narrow my preferred connector to everything Azure, as seen below. After selecting the connector, then select "Connect".

Image showing how to use the Azure SQL database connector in Power BI Desktop


The following window should look like the one for the SQL Server database connector earlier. The same approach applies here; copy the Connection string of the Datamart from the Power BI service described earlier and paste it into the server section as seen in the diagram below. Then click "OK".

We can also choose an Import or Direct Query connection mode, as was the case with the SQL Server database connector earlier. In this case, I selected "Direct Query" mode.

Image showing how to paste the Datamart connection string in the Azure SQL database connector while using Direct Query mode


Next, on the "Navigator" window, select the checkbox that begins with "model.DatamartName", as seen in the diagram below.

Then either click on "Transform Data" or "Load". For this demo, "Load" is just enough.

Image showing how to use the Azure SQL database connector in Power BI Desktop v2


Since we used the Direct Query mode for this connector, this table is now saved on a Direct Query mode, as seen in the diagram below.

Image showing Azure SQL database connection to Datamart on DQ mode


Connect Using Live Connection Via the Data Hub Connector

At the time of writing this tip, this connector is still in Preview within the Data Hub in Power BI Desktop. This is as seen in the image below.

Image showing how to connect to Datamart via the Data hub


To connect using the "Datamarts (Preview)", you can either connect via a live connection or with an option to choose between an Import or Direct Query mode. Let's look at how to connect via the live connection mode.

Firstly, you click on the "Datamarts (Preview)" as seen in the image above. Then on the "Data hub" window that opens, select the Datamart that you need to connect to, and then click on the drop-down at the bottom right and select "Connect" (note that you have two options here: Connect and Connect to SQL endpoint). See the image below on how to achieve this.

Image showing how to connect to Datamart via the Data hub via Live connection


On the next window, select the checkbox on the Datamart you are connecting to and click "Submit", as seen in the diagram below.

Image showing how to connect to Datamart via the Data hub via Live connection v2


A warning message may appear, as shown below. Click "OK". This is because you are creating a "Live" connection in a model that already has an Import and Direct Query connections.

Image showing how warning message when model contains a Live and Import or DQ mode


For this connection method to Datamart, you can see from the image below that it has been saved in a "Direct Query" connection mode even though the connection was a "Live" connection, as seen in the diagram below. But as you can see from the color at the top of the table in the model view, it is not the same color as that of a Direct Query connection, and you cannot see the "Advanced" section of the table properties. Note: If this model only connected to the "Live" connector, it would not be in a Direct Query mode but Live mode.

Image showing Azure SQL database connection to Datamart on Live mode


Connect Using Direct Query OR Import Connection Via the Data Hub Connector

As mentioned earlier, while connecting using the "Datamarts (Preview)" via the Data hub, you can either connect "Live" or have an option to choose your connection mode. To choose your connection mode via this method, you must first select the "Connect to SQL endpoint" option, as seen in the image below.

Image showing how to connect to Datamart via the Data hub via Connect to SQL endpoint


Next, you need to follow similar steps we used in the "Connect" via the Data hub above and then select the check box at the front of "model.DatamartName". After this, you either "Load" or "Transform" the data. But in this case, we want to load once more.

Choose your preferred connection mode when a new window opens, as seen in the diagram below. Then click "OK".

Image showing how to connect to Datamart via the Data hub via Connect to SQL endpoint using DQ 


In summary, I have demonstrated four different methods to connect directly to a Power BI Datamart in Power BI Desktop. Still, it should be noted that we can also connect to the auto-generated datasets originating from any Datamart via the Power BI datasets connector in Data hub, which would create a Live connection to the Datamart. In the comments section, it would be interesting to know other approaches to connecting to a Datamart in Power BI Desktop.

Next Steps





get scripts

next tip button



About the author
MSSQLTips author Kenneth A. Omorodion Kenneth A. Omorodion is a Microsoft Certified Data Analytics and BI Professional mostly in Microsoft BI stack of tools.

View all my tips


Article Last Updated: 2022-10-10

Comments For This Article

















get free sql tips
agree to terms