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