'How to connect to Power BI database outside of Power BI?
I'd like to connect to a power BI database and query it using SQL. My first instinct was to use Python's pyodbc
package, like so:
conn = pyodbc.connect( 'DRIVER={SQL Server};SERVER=powerbi://api.powerbi.com/v1.0/url;DATABASE=DB Name;Trusted_Connection=yes;')
However this raises the following:
---------------------------------------------------------------------------
OperationalError Traceback (most recent call last)
<ipython-input-6-669e861464b3> in <module>
----> 1 conn = pyodbc.connect( 'DRIVER={SQL Server};SERVER=powerbi://api.powerbi.com/v1.0/myorg/Datasets;DATABASE=Mutual Servicing;Trusted_Connection=yes;')
OperationalError: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (17) (SQLDriverConnect); [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). (123)')
I thought this might be possible based off of the information that appears when I hover over a table in my model tab on Power BI, which looks something like this:
Name: SomeTableView
Data source type: SQL Server Analysis Services database
Server: powerbi://api.powerbi.com/v1.0/myorg/stuff
Database: DB Name Here
Am I approaching this the right way? Or are databases hosted on a power BI server not meant to be accessed from outside power BI?
Solution 1:[1]
Hi your approach is correct but back end of the PBI is SSAS not a SQL instance. Instead of. pyodbc try pyadomd which is the SSAS library for python below i have included a sample code
for the dll install this
from sys import path
path.append('/root/dll/Microsoft.AnalysisServices.AdomdClient.dll')
from pyadomd import Pyadomd
conn='Provider=MSOLAP;Data Source=powerbi://api.powerbi.com/v1.0/myorg/;Initial Catalog=testcube;User Id=testuser;Password=xxxxx ;'
query = "SELECT DIMENSION_CAPTION AS [DIMENSION] FROM $system.MDSchema_Dimensions"
with Pyadomd(conn) as conn:
with conn.cursor().execute(query) as cur:
print(cur.fetchall())
From the documentation of pyadomd you can find some samples too cheers!!!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | AmilaMGunawardana |