Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
jhalverson
Helper I
Helper I

How to access datamart query from Power BI desktop

Hi all,

 

I have created a query in a datamart on Power BI Service, but am not sure how to access it on Power BI Desktop. The first image is the list of tables and the sql query I wrote that combines info from said tables. The second image is what pulls up when I connect to the datamart (in the form of a sql server database) on power bi desktop. The list of available tables does not contain my query "ConnectViolationsReport". 

jhalverson_0-1680287698468.pngjhalverson_1-1680287778873.png

 

Is it possible to access my query from Power BI Desktop? Or is it possible to write the same SQL query somewhere in Power BI desktop? I know when you select SQL Server as a datasource it gives you an option to write the query there, but I am not sure what the name of the database would be as this is a datamart.

 

Any help is greatly appreciated, thanks!

 

 

1 ACCEPTED SOLUTION
otravers
Community Champion
Community Champion

You can't see SQL queries from an external SQL server in Power BI Desktop, you can only see their result if the query results in a view/table. (You currently can't define SQL views in Datamarts.)

 

You can add a SQL statement to your Source step in Power BI Desktop provided you connect via the Sql.Database connector and not the PowerBI.Datamarts connector. Connect to your SQL endpoint via SSMS to find your database name as it's needed for the SQL connector to work.

 

otravers_0-1680448222514.png

 

See this entry for further details:

https://www.oliviertravers.com/power-bi-datamarts-sql-server-desktop-tools-connection/

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

View solution in original post

1 REPLY 1
otravers
Community Champion
Community Champion

You can't see SQL queries from an external SQL server in Power BI Desktop, you can only see their result if the query results in a view/table. (You currently can't define SQL views in Datamarts.)

 

You can add a SQL statement to your Source step in Power BI Desktop provided you connect via the Sql.Database connector and not the PowerBI.Datamarts connector. Connect to your SQL endpoint via SSMS to find your database name as it's needed for the SQL connector to work.

 

otravers_0-1680448222514.png

 

See this entry for further details:

https://www.oliviertravers.com/power-bi-datamarts-sql-server-desktop-tools-connection/

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors