How to get the sql database table name in the dataset

[Boji]

I'd like to scan all workspaces and find their datasourceInstances details. Currently, when I run Admin - WorkspaceInfo GetScanResult call, i can get the connection details as: 

'connectionDetails': {'server': 'corp\\ser1', 'database': 'database1'},

But I'd like to further know the table or view names in the database that the dataset connect to. Is there any way I can get those info? 

Thanks, 

[Martin_D]

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

[Boji]

Martin,

Your answer is amazingly helpful. There are a lot of new things that I will need time to digest and put into practice. Is it possible to suggest the most feasible way for the scenario below?

Our organization is moving the database from an existing on-premises server to Snowflake cloud. We will need to update the dataset in Power BI to redirect to the new location. We will have a one-to-one match list of the existing table/view name, database, and server name to future Snowflake table/view name. Since there are hundreds of Power BI reports that would be impacted, I am working to write a script either in Python or PowerShell to automate the transformation.

A little more detail: our company is on the premium capacity, and I am on the fabric administration role. The Power BI spaces that I would need to work with to redirect the dataset resource location include both premium workspaces and pro workspaces. The power query is typically simple without incremental change (but some of the tables are refreshed on schedule). That is to say, most of the datasets only have one query (one query could load multiple SQL table/view to do ETL in power query). I will need to write a script to find all SQL tables/views in Power Query to redirect them to the future Snowflake storage location. Do you have any suggestions for the best route? I don't mind to work on the power query parsing as long as I can get the power query script and update it with new table name in the Snowflake. 

Thanks for your kind help in advance, 

Boji

[Martin_D]

Hi @Boji ,

Since the PowerQuery command to load from SQL Server is SQL.Database or SQL.Databases and for Snowfalke it's Snowflake.Databases you need to change the PowerQueries anyway. You cannot easily use steps ike "change datasource", which is more useful like for switching from dev to prod database withing the same technology.

If you are lucky, then you just need to replace the first lines of a PowerQuery that gets it data from SQL Server into lines that get the same table from Snowflake. Create one PowerQuery from Snowflake manually and compare the code pattern for SQL Server and Snowflake sources. Then you can write code to extract the database and table/view names, generate the Snowflake PowerQuery code and replace the lines in the PowerQuery code.

Now, the tricky part might be how to access, read, and change the PowerQuery code from a script. Basically you have two options:

1. XMLA API, see above. But I'm not sure whether Power BI allows to change the PowerQuery code through XMLA. You would have to try. You can do an intial try easily using Tabular Editor and Power BI Premium dataset. This approach has two pitfalls: a) Will definitely not work with Pro workspace. b) You cannot download the pbix file anymore after modifying the dataset metadata from the XMLA API.
2. Use pbi-tools Welcome to pbi-tools | pbi-tools You can write script to automatically download the pbix files, disassemble them into text files using pbi-tools (which is scriptable), modify the PowerQueries, reassemble the pbix file using pbi-tools, publish pbix file back to the workspace. Once set up, as long as you have pretty standard PowerQueries to load from SQL Server, this way you can fully automate the process.

Kind regards,

Martin