Forum Discussion

kosmiz's avatar
kosmiz
Frequent Visitor
4 years ago

Split Power by performance query by steps with power query

Hello everybody,

 

I have a question about a model that I am trying to build for documentation purposes.

 

I manage to run all the DMVs queries to get all relavant information from a SSAS model about tables, columns relationships etc. and I plugged them into a combined model in which I download all json performance file of my PBIX and analyse them with information from DMVs.

 

At the moment, taking all the fields used in the query automatically generetaed for each visual, I am able to say what are the fields used in each visual and where are coming from, the relationship related to this fields etc. Now, maybe I'm going too far, but I was wondering if, instead of extracting all the fields, I could split the query by DAX table before, so that I will be able to know if a field is a filter, a Dimension of the visual, if there is a subtotal or grandtotal or what operation is used for. So for example if I take this query

 

DEFINE
VAR __DS0FilterTable =
TREATAS({"2102"}, 'SETT_CANVAS (2)'[COD_CANVAS])

VAR __DS0FilterTable2 =
TREATAS({"blabla"}, 'CLM'[DESC_L4])

ROLLUPADDISSUBTOTAL(
ROLLUPGROUP(
'SETT_CANVAS (2)'[bla bla bla],
'SETT_CANVAS (2)'[COD_FAKE],
'CLM'[DESC_L4],
'ANAG_FAKE'[SOC]
), "IsGrandTotalRowTotal"
) ecc.

 

is there a way to split them by Name of the table generated in each step (e.g. VAR __DS0FilterTable etc. would be colsidered as a filter and all fields in it will be categorized as filters, the one called VAR __DS0PrimaryWindowed will be the one for the visual rendering of the first 500 rows and all fields in it will be dimensions or measures of the visual etc.). I tried but could not manage to work that out so far.

 

Thanks a lot for any suggestion

Kos

Need Help

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    I'm not sure I understand what your ultimate goal is here. In the majority of cases, evaluating __DS0Core is going to be the query component that takes most of the processing time. What are you trying to learn from splitting up the query?

  • kosmiz's avatar
    kosmiz
    Frequent Visitor
    4 years ago

    Hello Alexis,

     

    Thanks for your reply. I am trying to find a way to differentiate the role of each of the fields in the query. At the moment I can tell if they are column or measure but I don't say anything about the role in the vsiual. I would like to categorize them knowing that some of them will be filters, some others will be columns and rows (or axis) of the visual, some will be measures used as filters and some will be proper measures of the table and so on. Starting from the naming of the different tables was my only idea but any other is more than welcome.

     

    Thanks again 

    Andrea