topic Re: Power BI Report Server dbo.Catalog table in Report Server

Power BI Report Server dbo.Catalog table

Anonymous — Mon, 17 Jul 2017 10:30:54 GMT

HI,

I'm trying converting content data from dbo.Catalog table of my Power Bi Report Server Database, but conversion of data about type = 13 (pbix files) return me an issue:

XML parsing line 1 character 3, illegal xml character

Could someone help me?

thanks!

Lorenzo

Re: Power BI Report Server dbo.Catalog table

mgmeyer — Mon, 17 Jul 2017 16:30:17 GMT

I'm not sure exactly what you are trying to do, are you explicitly writing queries to modify the data that is in the ReportServer Catalog table? What are you trying to achieve by this? Doing something like this is not supported and could put you data/catalog in a bad state and potential break you server installation.

Re: Power BI Report Server dbo.Catalog table

v-yulgu-msft — Tue, 18 Jul 2017 02:59:42 GMT

Hi @Anonymous,

Not sure what SQL query you were using that resulted above error. Please try below query:

WITH ItemContentBinaries AS
(
  SELECT
     ItemID,Name,[Type]
    ,CASE Type
       WHEN 2 THEN 'Report'
       WHEN 5 THEN 'Data Source'
       WHEN 7 THEN 'Report Part'
       WHEN 8 THEN 'Shared Dataset'
	   When 13 Then 'Power BI Report'
       ELSE 'Other'
     END AS TypeDescription
    ,CONVERT(varbinary(max),Content) AS Content
  FROM ReportServer.dbo.Catalog
  WHERE Type in (2,5,7,8,13)
),

--The second CTE strips off the BOM if it exists...
ItemContentNoBOM AS
(
  SELECT
     ItemID,Name,[Type],TypeDescription
    ,CASE
       WHEN LEFT(Content,3) = 0xEFBBBF
         THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
       ELSE
         Content
     END AS Content
  FROM ItemContentBinaries
)
--The outer query gets the content in its varbinary, varchar and xml representations...
SELECT
   ItemID,Name,[Type],TypeDescription
  ,Content --varbinary
  ,CONVERT(varchar(max),Content) AS ContentVarchar --varchar
  ,CONVERT(xml,Content) AS ContentXML --xml
FROM ItemContentNoBOM

Reference: Extract RDL (XML) from the ReportServer database

Best regards,
Yuliana Gu

Re: Power BI Report Server dbo.Catalog table

FinditEZ — Thu, 23 Nov 2017 16:33:55 GMT

Hi @v-yulgu-msft,

That is the precise query that generates the error @Anonymous is reporting. Note that the BOM for a Power BI document contents is different then the legacy SSRS reports, datasources and shared datasets.

Is there a different CASE WHEN clause to strip off the unique BOM characters used for Power BI contents stored in SQL Server reports catalog?

Thank you,

Ken

Re: Power BI Report Server dbo.Catalog table

FinditEZ — Thu, 23 Nov 2017 16:36:46 GMT

Hi @v-yulgu-msft,

Is there a different CASE WHEN clause to strip off the unique BOM characters used for Power BI contents stored in SQL Server reports catalog?

Thank you,

Ken

Re: Power BI Report Server dbo.Catalog table

KBO — Thu, 13 Sep 2018 08:36:12 GMT

I have some supplements:

1= Folder

3 = Templates

🙂

Best Kathrin

Re: Power BI Report Server dbo.Catalog table

Anonymous — Fri, 14 Sep 2018 10:47:19 GMT

It's worth remembering that the Binary in the Content column for a PBIX file is actually the PBIX file itself, which is a ZIP file. So I'm pretty sure you can't meaningfully parse it as XML.