By: Kenneth A. Omorodion | Updated: 2022-10-10 | Comments (1) | 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.
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.
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.
Then, click on the ellipses (…) on the end of the Datamart and select "Settings", as seen in the diagram below.
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.
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".
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".
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.
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".
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.
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.
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.
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.
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.
On the next window, select the checkbox on the Datamart you are connecting to and click "Submit", as seen in the diagram below.
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.
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.
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.
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".
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
- Read this Microsoft documentation on creating reports using Datamart.
- Read my tip on configuring incremental refresh within a Datamart in Power BI service
- Learn more about Power BI Datamart: Get Started with Datamarts.
- You might also want to read this blog by Olivier Travers: Connecting to Power BI Datamart and PowerApps Dataverse's SQL Server from Desktop Tools and External Services.
- Try this tip out in your data as business requires.
Learn more about Power BI in this 3 hour training course.
About the author
Kenneth A. Omorodion is a Business Intelligence Developer with over eight years of experience. He holds both a bachelor’s and master’s degree (Middlesex University in London). Kenneth has the MCSA, Microsoft Data Analyst - Power BI and Azure Fundamentals certifications. Kenneth is a Microsoft Certified Trainer and has delivered corporate training on Power BI, SQL Server, Excel and SSRS.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2022-10-10
