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
tcschroeder
New Member

PowerBi embeded data sources query

I am looking for a way to query the ReportServer database to find the name of the embeded data sources in a PowerBi report.  I know how to do this for SSRS reports, but can't figure out how to get it for PowerBi reports.

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

Hi @tcschroeder ,

Power BI Desktop as a tool does not have a corresponding database (like when report server is deployed, a corresponding database is created, so we can look up its data source information via sql). We need to use Power BI Desktop to open the PBIX file and then view the data source through the following interface.

vluwangmsft_0-1650943117595.pngvluwangmsft_1-1650943179591.png

Best Regards

Lucien

View solution in original post

3 REPLIES 3
v-luwang-msft
Community Support
Community Support

Hi @tcschroeder ,

Power BI Desktop as a tool does not have a corresponding database (like when report server is deployed, a corresponding database is created, so we can look up its data source information via sql). We need to use Power BI Desktop to open the PBIX file and then view the data source through the following interface.

vluwangmsft_0-1650943117595.pngvluwangmsft_1-1650943179591.png

Best Regards

Lucien

v-luwang-msft
Community Support
Community Support

Hi @tcschroeder ,

You could obtain the data source name corresponding to the report in the specified database through the following TSQL query:

 USE [ReportServer]
 GO
    
 SELECT CATALOG.NAME
     ,CATALOG.[Path]
     ,DataSource.NAME datasource
     ,CATALOG.[Description]
     ,Created.UserName AS CreatedByUser
     ,CATALOG.[CreationDate]
     ,Modified.UserName AS ModifiedByUser
     ,CATALOG.[ModifiedDate]
 FROM [dbo].[Catalog]
 LEFT JOIN (
     SELECT [UserID]
         ,[UserName]
     FROM [dbo].[Users]
     ) AS Created ON CATALOG.CreatedByID = Created.UserID
 LEFT JOIN (
     SELECT [UserID]
         ,[UserName]
     FROM [dbo].[Users]
     ) AS Modified ON CATALOG.ModifiedByID = Modified.UserID
 JOIN DataSource ON CATALOG.ItemID = DataSource.ItemID
 JOIN CATALOG cat1 ON DataSource.Link = cat1.ItemID
 WHERE CATALOG.[Type] = 2
 ORDER BY [Path]
     ,NAME

vluwangmsft_0-1650864728053.png

 

And you could get data from sql through using Power BI.

vluwangmsft_1-1650864831302.pngvluwangmsft_2-1650864854699.png

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

Actually no.  This is showing the data sources for the SSRS reports (.rdl's).  That is what I am able to get.  What I am interested in is the data sources for PowerBI documents (.pbix).

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.