Power BI Metadata

[Anonymous]

Hi Team,

I have been working on Power BI Desktop for a while now. I had few questions on Power BI Metadata. How can we extract Metadata information such as Search engine information if any, data definitions, data lineage, versioning etc. Is there a documentation for the same? I would appreciate any help on this.

Search engine- It denotes all information on user activity or user logs, audit logs on a report in Power BI.

Thanks,

Rohit

[Anonymous]

That is not an easy question to answer.

Audit about who has viewed the report is in the ReportServer executionLog though it's a bit thin with regards to any perfromance details.

Within an actual PBIX file things get more complicated. If you have Direct Query or Live Query you can get some information about the connection by unzipping the PBIX file. This article kind of points the way

http://radacad.com/exposing-m-code-and-query-metadata-of-power-bi-pbix-file

If you open the PBIX file locally in PBI Desktop you can figure out the port number of the SSAS Tabular instance that is created to host the data and you can then query that SSAS instance to get the meta data out of the DMVs within the tabualr instance. Extracting this into a DB/Excel using some PowerShell will give you all the columns/tables/measures. I'm not entirely sure what lineage information is available as I've not looked at that aspect of it but it must be there inherently.

[Anonymous]

Thanks for the reply. That was informative. I had few more questions.

1. How can we extract versioning information from Power BI report?

2. What is the significance of local port number to connect to a Power BI Desktop model?

3. How can we extract Power BI Metadata information from an API? Is there a documentation?

[Anonymous]

the significance of the port number of the embedded SSAS instance that is sat under your PowerBI report is that you can use tools like PowerShell or SSSM to query the model.

http://biinsight.com/four-different-ways-to-find-your-power-bi-desktop-local-port-number/

Once you have the port number you can issue queries to the model for the underlying meta data. Those queries look something like this

#TMSCHEMA_MODEL
Select [ID], [Name], [Description], [Culture], [ModifiedTime], [StructureModifiedTime] from $SYSTEM.TMSCHEMA_MODEL

#TABDEF_TMSCHEMA_DATA_SOURCES
SELECT [ID], [ModelID], [Name], [Description], [Type], [ConnectionString], [ImpersonationMode], [Account], [ModifiedTime] from $SYSTEM.TMSCHEMA_DATA_SOURCES

#TMSCHEMA_TABLES
Select [ID], [ModelID], [Name], [DataCategory], [Description], [IsHidden] from $SYSTEM.TMSCHEMA_TABLES

#TABDEF_TMSCHEMA_PARTITIONS
Select [ID], [TableID], [Name], [Description], [DataSourceID], [QueryDefinition], [Type], [Mode], ModifiedTime, RefreshedTime from $SYSTEM.TMSCHEMA_PARTITIONS

#TMSCHEMA_RELATIONSHIPS
Select [ID], [ModelID], [IsActive], [Type], [CrossfilteringBehavior], [FromTableID], [FromColumnID], [FromCardinality], [ToTableID], [ToColumnID], [ToCardinality], [ModifiedTime] from $SYSTEM.TMSCHEMA_RELATIONSHIPS

#TMSCHEMA_COLUMNS
Select [ID], [TableID], [ExplicitName] , [ExplicitDataType], [DataCategory], [Description], [IsHidden], [IsUnique], [IsKey], [SummarizeBy], [ColumnStorageID], [Type], [SourceColumn], [Expression],
[FormatString], [SortByColumnID], [AttributeHierarchyID], [ModifiedTime], [StructureModifiedTime], [DisplayFolder] from $SYSTEM.TMSCHEMA_COLUMNS

#TMSCHEMA_MEASURES
Select [ID], [TableID], [Name], [Description] , [DataType], [Expression], [FormatString], [IsHidden], [ModifiedTime], [StructureModifiedTime], [KPIID], [IsSimpleMeasure], [DisplayFolder] from $SYSTEM.TMSCHEMA_MEASURES

#TMSCHEMA_COLUMN_STORAGES
SELECT [ID], [ColumnID], [Name], [OrderByColumn], Locale, [Statistics_DistinctStates], [Statistics_RowCount], [Statistics_HasNulls] from $SYSTEM.TMSCHEMA_COLUMN_STORAGES where Locale <> 0

#TMSCHEMA_HIERARCHIES
Select [ID], [TableID], [Name], [Description], [IsHidden], [HierarchyStorageID], [ModifiedTime], [StructureModifiedTime], [DisplayFolder] from $SYSTEM.TMSCHEMA_HIERARCHIES

#TMSCHEMA_KPIS
Select [ID], [MeasureID], [Description], [TargetDescription],[TargetExpression], [TargetFormatString], [StatusGraphic], [StatusDescription], [StatusExpression], [TrendGraphic], [TrendDescription],
[TrendExpression], [ModifiedTime] from $SYSTEM.TMSCHEMA_KPIS

#TABDEF_TMSCHEMA_LEVELS
Select [ID], [HierarchyID], [Ordinal], [Name], [Description], [ColumnID], [ModifiedTime] from $SYSTEM.TMSCHEMA_LEVELS

I haven't investigated if you can issue these queries via the REST API. I tend to use PowerShell for this sort of thing but I can't see an obvious way to do this currently in the REST API documentation https://app.swaggerhub.com/apis/microsoft-rs/PBIRS/2.0 though you can easily query properties of the report if you just want to know who created itm, when it was created etc. without delving into the tables, columns and measures that are available inside it

[Anonymous]

Thanks for the information. I had few more questions.

1. I tried finding information about User logs like who created the report, when was it created, who had viewed/edited the report but didn't find any information on the same.

If they are seen in Report server execution log, where exactly can it be seen?

Also, if we can query properties of the report to find this information which queries should be used?

2. Where do we get versioning information from Power BI reports?

3. I understand you haven't looked at Data Lineage aspect of things but do you see any future work or release for Power BI which may help in getting this information from Power BI reports.

[Anonymous]

You can get versioning and information about who created a report and when from the ReportServer DB with something like this

SELECT c.ItemID
,c.[Path]
,c.[Name] as ReportName
,ISNULL(cp.[Name], 'Root') as ParentItem
,CASE c.[Type]
WHEN 1 THEN 'Folder'
WHEN 2 THEN 'Report'
WHEN 3 THEN 'Resources'
WHEN 4 THEN 'Linked Report'
WHEN 5 THEN 'Data Source'
WHEN 6 THEN 'Report Model'
WHEN 7 THEN 'Report Part'
WHEN 8 THEN 'Shared dataset'
WHEN 11 THEN 'KPI Card'
WHEN 13 THEN 'PowerBI'
ELSE CAST(c.[Type] AS VARCHAR(10))
END AS ItemType
,c.Property
,c.[Description]
,c.[Hidden]
,cu.UserName as CreatedBy
,c.CreationDate
,mu.UserName as ModifiedBy
,c.ModifiedDate

,ISNULL(CAST(c.ContentSize AS MONEY) / CAST((1024*1024) AS MONEY),0) as ContentSizeMb
FROM dbo.[Catalog] c
LEFT OUTER JOIN dbo.[catalog] cp
on c.ParentID = cp.ItemID
LEFT OUTER JOIN dbo.Users cu
ON c.CreatedByID = cu.UserID
LEFT OUTER JOIN dbo.Users mu
ON c.ModifiedByID = mu.UserID
WHERE LEFT(c.[Path],14) <> '/Users Folders' -- ignore user folders

I have provided some queries for figuring out when data refresh has taken place (OR NOT) for PBIX reports on other forum posts. This is the other big thing we tend to want to have some visibility of.

Information about report executions is in the ExecutionLog3 view though the shortcomings of that are also detailed in other posts here.

I don't work for MS and have no insight into what they are planning to do.

You can get the most of what you need for lineage via the meta data queries I have mentioned previously and the blog I linked to which explains how to get the M queries out of the PBIX file. Hopefully at some point they will make this easier for on premise datasets, managing the free for all that is self service is no fun at all without good meta data and lineage tools.

Report server doesn't maintin versions of PBIX files. You have to do that manually. We run a powershell script that takes a copy of every new PBIX file every half hour. That way if someone wants to backtrack we can recover something fairly recent. Its rubbish but its better than what you get out of the box, which is nothing.

[pratheeban]

Hi,

Did you find any information about how to get the metadata of the Power BI Objects using REST API because I also need it..

Thanks