Forum Discussion

jambrose's avatar
jambrose
Frequent Visitor
9 years ago

Improving Summarize Efficiency

I'm trying to pull information from a large (~20 mil records, 48 colmuns) table, and I have a working method, but it's on the verge of what my machine can handle, and so I'm worried it won't scale up...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

Generally any time you see a MINX it throws up warning flags around performance but in your case, you are only returning a 1 row table to it so it only has to iterate over 1 row. So, my guess is that it is all of the sorts you have going on. I wonder if it might be better for you to pull this in via another query and do what you are doing in DAX in M instead.

  • jambrose's avatar
    jambrose
    Frequent Visitor
    9 years ago

    I'd be open to trying it that way, but I haven't so far been able to find a way to do the same process (pull a top value based on sorting 3 other columns) on the query end of things.  Any ideas?

    • dedelman_clng's avatar
      dedelman_clng
      Community Champion
      9 years ago

      (CAVEAT: This is just an observation - I don't have much experience with SUMMARIZE and TOPN)

       

      Have you tried doing a FILTER on BigTable inside the TOPN function?  It looks like the TOPN is looking over the whole table, not just the part of the table that has the ID you are looking for.  And if it's anything like SQL, the TOPN function has to do all kinds of distincts and interations in the execution plan, which is probably what is chewing up memory.

       

      So something like this:

       

      Summary Table =
SUMMARIZE (
    Big_Table,
    Big_Table[ID],
    "First Value", MINX (
        TOPN (
            1,
            FILTER ( Big_Table, Big_Table[ID] = EARLIER ( Big_Table[ID] ) ),            [Sort1], ASC,
            [Sort2], ASC,
            [Sort3], ASC
        ),
        [Value]
    )
)

      If you get an error that there is no "EARLIER" to be referenced, try wrapping the MINX function in CALCULATE.

       

       

      Hope this helps.

      David

      • jambrose's avatar
        jambrose
        Frequent Visitor
        9 years ago

        I gave this a shot, and it didn't appear to make a significant difference in the memory load or execution time.  My understanding is that having it nested inside the SUMMARIZE implicitly filters in some similar manner anyway.  Or at least, the results I get (from either version) find the same value as desired - the top value out of only the rows with the given ID.  Thanks for taking a stab though!

    • jambrose's avatar
      jambrose
      Frequent Visitor
      9 years ago

      Well, I gave the query side a shot, and was able to accomplish the same task with the following code:

      = Table.Group(#"Big_Table", {"ID"}, 
         {{"First Value", 
            each Table.First(Table.Sort(_, 
                {{"Sort1", Order.Ascending}, {"Sort2", Order.Ascending}, {"Sort3", Order.Ascending}})
            )[Value] }})

      However, this still runs into memory load errors and fails to run on the full data set.  Not sure what else to try here.  Unless anyone else has ideas, I think I'll try moving to R for the processing steps and then import back to Power BI to generate reports.

       

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        9 years ago

        Hi jambrose,

        You use the SSMS connect your Power BI model. Then profilie Power BI Desktop Model using SQL Server Profiler using the following steps. More details, please review this article.

        Thanks,
        Angelia