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

[olegkazanskyi]

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?

[d_gosbell · Accepted solution]

---

@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 )

[d_gosbell · Accepted solution]

---

@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"

[d_gosbell]

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.

[lbendlin]

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

[d_gosbell · Accepted solution]

---

@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 )

[lbendlin]

@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?

[d_gosbell · Accepted solution]

---

@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"

[lbendlin · Accepted solution]

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

[olegkazanskyi]

This is a wonderful insight into the DMV queries results.

Thank you!

[lbendlin]

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.