Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Power BI Metadata

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

Power BI Metadata

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

        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