Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Performance : calculating Moving average, percentile

Good morning, everyone, I would like to present to you a case where performance is poor. The context is this: a date dimension table (this one includes all dates with hh:mm:ss) + a fact table ("PDJ...
Need Help
  • AkhilAshok's avatar
    AkhilAshok
    7 years ago

    Can you try changing the last 2 bits:

     

    'Fact'[TimeID] >= MovingWindowStart
    && 'Fact'[TimeID] <= MovingWindowEnd

     

    to

     

    'DimTime'[TimeKey] >= MovingWindowStart
    && 'DimTime'[TimeKey] <= MovingWindowEnd

     

    In addition, make sure TimeKey should be an integer.

AkhilAshok's avatar
AkhilAshok
Icon for Solution Sage rankSolution Sage
7 years ago

Try splitting the Date column into 2 columns: Date & Time

 

Then have 2 seperate dimension tables for Date and Time.

 

That should reduce the model size and improve performance. Since Date & Time dimension table are Contiguous, you may not need an Index column.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thank you. It's a great idea about two seperate dimension tables for Date and Time.
    I could reduce the performance.

     

    But how do you fill the lines without measures ? To calculate average moving, you have to find all the seconds into the fact table.

    And from my source, i have holes because i don't have all the seconds. i have to load the other lines with a measure to 0 with a outer join in Power Query (Merge between dimension date&time and the fact table)

     

    Without index, it means that you use time for the calculation, isn't it ?

     

    May be with this calculated measure :

    MovingAverage = CALCULATE(AVERAGE([Value]),FILTER(MovingAverage, EARLIER([Time])>=[Time]))

     

    Thank you.

    • AkhilAshok's avatar
      AkhilAshok
      Icon for Solution Sage rankSolution Sage
      7 years ago

      You should have a Date dimension (365 rows for 1 year) & a time dimension (86400 rows for one day), and link it to your fact table.

       

      Afterwards, you can derive your moving Average based on the Time dimension table which has all the time.

       

      AverageMoving2s =
VAR MovingWindowEnd =
    MAX ( 'Time'[TimeID] )
VAR MovingWindowStart = MovingWindowEnd - 2
VAR MaxTimeinFact =
    CALCULATE ( MAX ( 'Fact'[TimeID] ), ALLSELECTED ( 'Time' ) )
VAR MinTimeinFact =
    CALCULATE ( MIN ( 'Fact'[TimeID] ), ALLSELECTED ( 'Time' ) )
RETURN
    IF (
        MovingWindowEnd <= MaxTimeinFact
            && MovingWindowEnd >= MinTimeinFact,
        CALCULATE (
            AVERAGEX (
                VALUES ( 'Time'[TimeID] ),
                VAR Total = [M1 Total] RETURN IF ( ISBLANK ( Total ), 0, Total )
            ),
            ALL ( 'Time' ),
            'Time'[TimeID] >= MovingWindowStart
                && 'Time'[TimeID] <= MovingWindowEnd
        )
    )
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Thank you AkhilAshok !

         

        It looked good! But the average doesn't work.

        You can see below.

         

        AverageMoving2sTest = 
VAR MovingWindowEnd =
    MAX ( 'DimTime'[TimeKey] )
VAR MovingWindowStart = MovingWindowEnd - 2
VAR MaxTimeinFact =
    CALCULATE ( MAX ( 'Fact'[TimeID] ); ALLSELECTED ( 'DimTime' ) )
VAR MinTimeinFact =
    CALCULATE ( MIN ( 'Fact'[TimeID] ); ALLSELECTED ( 'DimTime' ) )
RETURN
    IF (
        MovingWindowEnd <= MaxTimeinFact
            && MovingWindowEnd >= MinTimeinFact;
        CALCULATE (
            AVERAGEX (
                VALUES ( 'DimTime'[TimeKey] );
                VAR Total = [Total bets/s] RETURN IF ( ISBLANK ( Total ); 0; Total )
                     );
            ALL ('DimTime');
            'Fact'[TimeID] >= MovingWindowStart
                && 'Fact'[TimeID] <= MovingWindowEnd
                  )
    )

         

        Regards,