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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors