topic Re: TSQL power bi API in SSMS in Developer

TSQL power bi API in SSMS

djwbaker — Wed, 06 Oct 2021 07:52:02 GMT

Hi Has anyone been able to query Power bi datasets using TSQL in SSMS?
I can use MDX, DAx And XMLA. But i cant seem to activate the TSQL option, any ideas?

cheers Dan

Re: TSQL power bi API in SSMS

dm-p — Wed, 06 Oct 2021 20:20:37 GMT

Hi @djwbaker,

Power BI data models are not conventional SQL database tables, and rather Analysis Services (Tabular) models. As such, T-SQL is not a valid way to query them, whereas MDX/DAX/XMLA are all appropriate languages suitable for doing this. As such SSMS will not enable T-SQL here, but enable the others, as you're experiencing.

Regards,

Daniel

Re: TSQL power bi API in SSMS

Anonymous — Mon, 11 Oct 2021 03:03:12 GMT

Hi @djwbaker,

AFAIK, power bi rest API does not include t-SQL operate features.
Perhaps you can try to use SQL connector with an advanced t-sql statement and parametrize with query parameter then you can use rest api to modify them on the power bi service side.

Power Query SQL Server connector | Microsoft Docs

Using the Power BI Service Parameters to change connection strings (To possibly change between Dev

Datasets - Update Parameters - REST API (Power BI Power BI REST APIs) | Microsoft Docs

Regards,
Xiaoxin Sheng