topic Re: DMV request to Power BI Server Analysis Services for hidden tables in PowerQuery in Report Server

DMV request to Power BI Server Analysis Services for hidden tables in PowerQuery

olegkazanskyi — Fri, 10 Dec 2021 18:37:40 GMT

I try to get the list of tables with M codes from PowerBI online Analysis Service.

I managed to get the required data with the request:

SELECT *
FROM $SYSTEM.TMSCHEMA_PARTITIONS

But it doesn't show any tables that were selected not to "Load" in PowerQuery.

The purpose of this task is to get all data connections from a dataset and hidden tables are essential.

Does anyone know how to get the hidden tables' codes?

Re: DMV request to Power BI Server Analysis Services for hidden tables in PowerQuery

lbendlin — Mon, 13 Dec 2021 02:17:02 GMT

Interesting observation. You can see the "hidden" queries with

select * from $SYSTEM.TMSCHEMA_PARTITION_STORAGES

but you can't get to the query meta data.

Re: DMV request to Power BI Server Analysis Services for hidden tables in PowerQuery

d_gosbell — Mon, 13 Dec 2021 02:54:59 GMT

Partitions are the objects that hold the data that gets loaded into the model, so it is not expected that the partition information would show you any queries that are marked as "not loaded". So this has nothing to do with the partitions being hidden.

The location of the query metadata has changed over the years, but I believe you will find these queries in the $SYSTEM.TMSCHEMA_EXPRESSIONS DMV.

Re: DMV request to Power BI Server Analysis Services for hidden tables in PowerQuery

lbendlin — Mon, 13 Dec 2021 03:11:35 GMT

d_gosbell that one also only shows the queries that have Load enabled.

Re: DMV request to Power BI Server Analysis Services for hidden tables in PowerQuery

d_gosbell — Mon, 13 Dec 2021 03:36:52 GMT

lbendlin wrote:

d_gosbell that one also only shows the queries that have Load enabled.

Not according to my tests.

I just added 2 queries to DimProductCategory both set to disable the load, one was referenced as a merged query for DimProductSubcategory the other was completely unreferenced and both show up in the TMSCHEMA_EXPRESSIONS and neither show up in TMSCHEMA_PARTITIONS (although I can see a reference to the first one in the merge step in the DimProductSubcategory M code )

Re: DMV request to Power BI Server Analysis Services for hidden tables in PowerQuery

lbendlin — Mon, 13 Dec 2021 14:16:47 GMT

d_gosbell Interesting. I get rather inconsistent results. need to test more variations. What would be the reason for Query3 to not appear in the attached sample? Where can I see the lineage details?

Re: DMV request to Power BI Server Analysis Services for hidden tables in PowerQuery

d_gosbell — Mon, 13 Dec 2021 23:52:16 GMT

lbendlin wrote:

Interesting. I get rather inconsistent results. need to test more variations. What would be the reason for Query3 to not appear in the attached sample?

So the documentation for the DMVs is pretty poor, but it seems to me that TMSCHEMA_PARTITIONS contains the text for any queries that are loaded and TMSCHEMA_EXPRESSIONS contains the text for any queries that are not loaded.

So when I query your file I see the following

TMSCHEMA_PARTITIONS - shows "Query 3"

TMSCHEMA_EXPRESSIONS - shows "Query 1" and "Query 2"

lbendlin wrote:

Where can I see the lineage details?

If I query the DISCOVER_CALC_DEPENDENCY DMV I can see that "Query 3" has a dependency on "Query 1"

Re: DMV request to Power BI Server Analysis Services for hidden tables in PowerQuery

lbendlin — Tue, 14 Dec 2021 00:49:11 GMT

Brilliant. olegkazanskyi this means you need to run both DMVs to get your desired result.

Re: DMV request to Power BI Server Analysis Services for hidden tables in PowerQuery

olegkazanskyi — Tue, 14 Dec 2021 17:50:33 GMT

This is a wonderful insight into the DMV queries results.

Thank you!