locked
Calculating facts with slowly changing dimensions using DAX - Performance Question RRS feed

  • Question

  • My question primarily relates to performance.

    Let’s say I have a fact table, a SCD, and a dimension table.

    PaymentFacts

    -PaymentId

    -UserId (FK)

    -PaymentAmount

    -PaymentDate (FK)

    UserStatus (SCD)

    -UserId

    -ChangeDate

    -Status (FK)

    -SeqNum

    Statuses

    -Status

    Dates

    -Date

    My goal is to report the sum of PaymentAmount for each Status. I want to use the most recent status for each user.

    Here is my measure:

    Total:=CALCULATE( SUM(PaymentFacts[PaymentAmount]),

    DATESBETWEEN(Dates[Date],BLANK(),LASTDATE(Dates[Date])),

    FILTER(UserStatus,  UserStatus[SeqNum] = CALCULATE(MAX(UserStatus[SeqNum]),FILTER(ALL(UserStatus), UserStatus [UserId] = EARLIER(UserStatus [UserId]) && UserStatus[ChangeDate] < LASTDATE(Dates[Date]))))

    )

    Everything works great, but it doesn’t scale well with large datasets. Any ideas on improving it?

    Thanks,

    Matt

    Monday, March 3, 2014 10:20 PM

Answers

  • HI mhaberman,

    You may have already considered this but in case you haven't...You can try looking for parts of the formula which are a fixed property of a row i.e. the value depends solely on a row's data and can't be affected by the user's selection when interacting with the model via Pivot Tables, Charts, etc. This type of logic can be off-loaded from the measure and instead persisted in a calculated column in the relevant table. You would then use the calculated column as one of your filter parameters in the CALCULATE function(s).

    Javier Guillen discusses this topic in a blog post here: http://javierguillen.wordpress.com/2013/08/30/split-dax-calculation-steps-to-enhance-memory-utilization/


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog



    • Edited by Michael Amadi Friday, March 7, 2014 7:44 AM
    • Proposed as answer by Elvis Long Wednesday, March 19, 2014 3:03 AM
    • Marked as answer by Elvis Long Thursday, March 27, 2014 1:48 AM
    Friday, March 7, 2014 7:42 AM

All replies

  • Get 64bit hardware and software.

    Unrecognized Excel MVP (UEM)

    Tuesday, March 4, 2014 9:21 PM
  • HI mhaberman,

    You may have already considered this but in case you haven't...You can try looking for parts of the formula which are a fixed property of a row i.e. the value depends solely on a row's data and can't be affected by the user's selection when interacting with the model via Pivot Tables, Charts, etc. This type of logic can be off-loaded from the measure and instead persisted in a calculated column in the relevant table. You would then use the calculated column as one of your filter parameters in the CALCULATE function(s).

    Javier Guillen discusses this topic in a blog post here: http://javierguillen.wordpress.com/2013/08/30/split-dax-calculation-steps-to-enhance-memory-utilization/


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog



    • Edited by Michael Amadi Friday, March 7, 2014 7:44 AM
    • Proposed as answer by Elvis Long Wednesday, March 19, 2014 3:03 AM
    • Marked as answer by Elvis Long Thursday, March 27, 2014 1:48 AM
    Friday, March 7, 2014 7:42 AM