Forum Discussion
Helper IIDMV request to Power BI Server Analysis Services for hidden tables in PowerQuery
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?
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 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"
Brilliant. olegkazanskyi this means you need to run both DMVs to get your desired result.
8 Replies
- lbendlin
Super User
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.
- 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.
- d_gosbell
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 )