Can you query a semantic model in the workspace using SQL?

[BenBlackswan]

Hello everyone,

I know that you can connect to the workspace using SSMS through XMLA endpoint, however you have to use DAX or MDX when query the dataset in SSMS. Is it possible to switch to SQL?

Thanks you.

[Anonymous]

Hi @BenBlackswan 

As you correctly mentioned, when connecting to a Power BI workspace through the XMLA endpoint using SSMS, the primary languages used for querying datasets are DAX (Data Analysis Expressions) and MDX (Multidimensional Expressions). These languages are specifically designed for analysis and querying in analytical databases and semantic models.

Regarding your question about using SQL to query a semantic model: SQL is fundamentally a relational database query language and is not natively supported for querying datasets in Power BI through SSMS. Power BI datasets are based on the tabular model, which is optimized for DAX and MDX queries.

However, for scenarios requiring SQL querying capabilities, Power BI offers the ability to work with Datamarts. Datamarts in Power BI allow you to use SQL for querying, providing a SQL DQL (Data Query Language) experience through development environments such as SSMS or Azure Data Studio. This feature is particularly useful for developers and analysts familiar with SQL. For more information on Datamarts and how to use them, please refer to the following documentation: Analyze with Datamarts in Power BI.

To summarize, while direct SQL querying of Power BI datasets through SSMS is not supported, leveraging Datamarts within Power BI provides a pathway to utilize SQL for data analysis and querying.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SaiTejaTalasila]

Hi @BenBlackswan ,

You can check semantic link.

https://fabric.guru/fabric-semantic-link-and-use-cases

Thanks,

Sai Teja 

[fahadqadir3]

@BenBlackswan  While you cannot directly query Power BI semantic models using SQL, understanding and utilizing DAX and MDX is essential for working within Power BI. For those who prefer SQL, leveraging DirectQuery with a robust SQL backend can offer a practical compromise, allowing you to use SQL for data manipulation and DAX for advanced analytics within Power BI.