How to get the sql database table name in the dataset

Hi Boji ,

Yes, kind of, but it's not as nicely prepared as the connetion details and it requires Premium workspaces.

Basically the dataset structure is:

• Each dataset table has one or more partitions. If created in Power BI desktop, tables with incremental refresh have multiple partitions, all other tables have one partition.
• Each partition has one query to load the data into the partition. This is the Power Query of the table.
• Each query can use one or more data sources. This depends very much on how complex your Power Queries are built. Ideally, you simply load tables or views from a SQL Server and all ETL transformations are done upstream in the database.

If you have some kind of Premium workspaces then you can get the Power Queries of each table from the XMLA API and parse the SQL tables out of the Power Query code. The simpler your Power Queries are, the simpler you can keep this implementation. For an unrestricted solution there is a Power Query parser written in typescript available on github that could help you, and a list of supported data sources that could occur in the query code from the Power Query documentation. But probably, and with SQL Server queries only, you can keep it much simpler - example see below.

Implementation

• You can use PowerShell to get the partitions and their PowerQueries for each dataset in XML format using the XMLA Discover command. This code shows the fundamental approach. You need to loop over all workspaces and datasets. You can get them from the Power BI REST API, you probably already have that part.

Install-Module -Name SqlServer -Scope CurrentUser

$cred = Get-Credential

$XmlaCommand = @"
    <Discover xmlns='urn:schemas-microsoft-com:xml-analysis'>
        <RequestType>TMSCHEMA_PARTITIONS</RequestType>
        <Restrictions>
            <RestrictionList>
                <DatabaseName>MyDatasetName</DatabaseName>
            </RestrictionList>
        </Restrictions>
        <Properties></Properties>
    </Discover>
"@

Invoke-ASCmd -Credential $cred -Server "powerbi://api.powerbi.com/v1.0/myorg/MyWorkspaceName" -Database "MyDatasetName" -Query $XmlaCommand

get-tabular-model-schema-partitions.ps1 Be aware that you need to URL-encode the workspace name.

• This will return XML code containing the Power Query code per partion in XML tags like:

...
<QueryDefinition>let
    Source = Sql.Database("mysqlserver.database.windows.net", "AdventureWorksDW2019"),
    dbo_FactResellerSales = Source{[Schema="dbo",Item="FactResellerSales"]}[Data]
in
    dbo_FactResellerSales</QueryDefinition>
...

 XML<QueryDefinition> in TMSCHEMA_PARTITIONS response.
In this example, the connection is server: mysqlserver / database: AdventureWorksDW2019 and the table is FactResellerSales. All columns are selected. If you want to get all the column names for your lineage you need to query the SQL Server if they are not listed in the Power Query code.

• Now you can parse the <QueryDefinition> tags out of the XML code.
• Next you can parse the tables out of the Power Query code. Be aware that there are also options to spread server and database selection across multiple lines of code in Power Query using Sql.Databases or using SQL code to query a table instead of using the Item filter to select the table. Your script needs to deal with at least the coding styles that are used in your organization.

Thoughts

• If your dataset table names are the same as your SQL tables names and you usually just load SQL tables or views into dataset tables as they are on the SQL Server, and one dataset is usually just fed from one database, then it could be easier to just retrieve the list of dataset tables from the TMSCHEMA_TABLES request instead of getting the Power Queries from the TMSCHEMA_PARTITIONS request. The result would be the same and you just get a ready to use list of tables without Power Query code parsing.
• If you don't want to parse the Power Query code and you have the datasets under centralized control, then you could maintain Annotation attributes in the tabular model that store the information you need in an easily machine-readable format in a custom attribute. You can query annotations using the TMSCHEMA_ANNOTATIONS request. Partitions are object type 6. You can use Tabular Editor to add annotations to the dataset.
• Tabular Editor scripting or DMV queries are alternative solutions to get the partitions metadata, but you would still need to parse the Power Query code, except when using annotations.
• My preferred approach would be to use a data warehouse builder tool that builds and deploys your SQL data warehouse and your Power BI datasets from one tool and this tool can also provide the requested lineage information, instead of retrieving the lineage information from the Power BI service later. This approach fits best in more centralized data warehouse/BI teams or in any other setup that allows you to introduce such a tool for everyone involved. One of my favorites is Analytics Creator.

BR

Martin