Forum Discussion
Power BI Metadata
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.
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?
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_LEVELSI 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
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.
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
