Create SQL Server Linked Server for PowerBI-Dataset? Fire DAX Query from SQL Server to PowerBI.com?

[Anonymous]

Anyone managed to create a linked server to that firing DAX Queries via OPENQUERY is possible via TSQL Code?

I tried every combination with bellow TSQL Code already without success. Creating a linked server for an onPrem SSAS Tabular instance worked but not for the PowerBI.com datasets. Obviously the MSOLAP.7 provider has to be installed first in SQL Server? Thanks for any hints..

Backgound:
I need to consume data from a published PBI-dataset in R-Script. In R-Script there seems to be no possiblility to directly connect to DAX/PowerBI sources.

EXEC master.dbo.sp_dropserver @server=N'PBI', @droplogins='droplogins'

EXEC master.dbo.sp_addlinkedserver

@server = N'PBI', -- name of linked server

@srvproduct=N'MSOLAP',

@provider=N'MSOLAP.7', -- see list of providers available on SQL Server under Linked Server node in SSMS Object Browser

@datasrc=N'https://analysis.windows.net/powerbi/api;;Initial Catalog=ec789b72-07ad-4f48-a9ee-c3d62aba05f1', -- machine or instance name that host Analysis Services

@catalog=N'model' -- Analysis Services database (cube)

EXEC master.dbo.sp_addlinkedsrvlogin

@rmtsrvname=N'PBI',

@useself=N'False',

@locallogin=NULL,

@rmtuser=N'user@domain.com',

@rmtpassword='***'

Select * From OpenQuery(PBI, 'EVALUATE TESTTABLE')

[Eric_Zhang · Accepted solution]

@Anonymous

I don't think you can create any so-called linked server to PowerBI.com, as there's no such "provider" for PowerBI.com. However, Power BI actually provides REST APIs to create dataset, push data, embed reports etc. The REST API also has limitation, there's no such a API to get data from a published dataset at this moment. You can check and vote this idea Be able to get a dataset's data via REST API up.

Backgound:
I need to consume data from a published PBI-dataset in R-Script. In R-Script there seems to be no possiblility to directly connect to DAX/PowerBI sources.

[Eric_Zhang · Accepted solution]

@Anonymous

I don't think you can create any so-called linked server to PowerBI.com, as there's no such "provider" for PowerBI.com. However, Power BI actually provides REST APIs to create dataset, push data, embed reports etc. The REST API also has limitation, there's no such a API to get data from a published dataset at this moment. You can check and vote this idea Be able to get a dataset's data via REST API up.

Backgound:
I need to consume data from a published PBI-dataset in R-Script. In R-Script there seems to be no possiblility to directly connect to DAX/PowerBI sources.

[Anonymous · Accepted solution]

thanks @Eric_Zhang, yes i think you are right, currently there is no possiblity as the needed MSOLAP.7 provider is not registered in sql2016. Also there seems to be a issue regarding SSO authentication for the provider:

https://docs.microsoft.com/en-us/azure/analysis-services/analysis-services-connect?hubRefsrc=email&u...

I will now connect via excel und integrate the excel as a linked server to be able to join onPrem-SQL with PowerBI data.

Ugly but working workaround... 😉