Post reply

Product Hierarchy in a Matrix with 8+ measures on Values-- Perfomance

  • November 9, 2023 at 7:10 pm

    #4324635

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • November 9, 2023 at 7:20 pm

    #4324638

    I've not set something like that up before, but I'd try taking different things out of the equation. Can you query it without using powerBI? or is it slow if you pull the data into PowerBI BUT don't actually display anything?

    What I am thinking is my first step with performance issues when jumping through multiple applications is to try to narrow down the performance bottleneck. Is the bottleneck on the data pull, the data parsing, or the data presentation. Once I know that, I can start tuning the "slow" part of the system.

    My GUESS is that pulling in 9 million rows worth of data is what is causing the performance bottleneck, but it could be the calculations on those 9 million rows, or it could be the presentation layer. I would try watching bandwidth during the report execution as well as it could be that you are maxing out the bandwidth to your report and you need to reduce the data set.

    Just my 2 cents mind you... All of my SQL stuff is on premise, so debugging is a lot easier, but I know that bandwidth has been a big bottleneck with reports once people started working from home. Report in PowerBI Desktop or Excel on site executes in a few seconds, but doing it from home can take minutes due to the amount of data that gets downloaded. Unfortunately, the only fix to that is to reduce the data set which most end users don't like, so they just need to be patient...

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • November 10, 2023 at 7:27 pm

    #4325350

    Okay, silly me... I completely missed Reza Rad's video on it, "Aggregation to speed up the performance of a Power BI report even if all IMPORTED"

    You basically create an aggregated table using GROUPBY in PowerQuery to create the aggregate table that's at a higher level of granularity than the fact table. And then you modify the measures to use

    /* No DAX? */

    Sales = IF(
                ISCROSSFILTERED(DimPromotion[PromotionKey]) ||
                ISCROSSFILTERED(DimProduct[ProductKey]),
                    SUM(FactInternetSales[SalesAmount]), // Main table
                    SUM(SalesAggregationTable[SalesAmount])
               )

    (This post really needs the snippet from the Simpson's where Marge snaps at Lisa after she explains some conclusion saying "Oh Lisa, don't you think we've already figured that out???!")

Viewing 3 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply