cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
wi11iamr Regular Visitor
Regular Visitor

Nested SUMX performance impact within Tabular cube

To ensure the correct calculation of Forecast Accuracy at the Sub Total and Grand Total levels, I'm using the SUMX with the VALUES funciton to iterate record individually.

 

My approach is to include a SUMX iterator for each dimension by which the values will be analysed. This results in me having 5 nested SUMX's.

When calculating percentages it is even more compounded to 10 SUMX's. 

 

My Tabular SSAS cube then has 12 different measures using the same concept, and from what I can tell these measures are killing my Analysis Services.

 

Below is one of my code snippets. Any recommendations on how to improve the performance while still maintaining the integrity of my results at a Sub Total and Grand Total level?

 

 

Forecast Accuracy :=
IF (
    [Forecast Amt] >= [Shipped Amt],
    0,
    1
        - (
            DIVIDE (
                SUMX (
                    VALUES ( 'Customers'[AccNo] ),
                    SUMX (
                        VALUES ( 'Calendar'[Year] ),
                        SUMX (
                            VALUES ( 'Calendar'[Month] ),
                            SUMX (
                                VALUES ( Items[Supplier] ),
                                SUMX ( VALUES ( Items[ItemCode] ), [Forecast Amt] )
                            )
                        )
                    )
                ),
                SUMX (
                    VALUES ( 'Customers'[AccNo] ),
                    SUMX (
                        VALUES ( 'Calendar'[Year] ),
                        SUMX (
                            VALUES ( 'Calendar'[Month] ),
                            SUMX (
                                VALUES ( Items[Supplier] ),
                                SUMX ( VALUES ( Items[ItemCode] ), [Shipped Amt] )
                            )
                        )
                    )
                ),
                0
            )
        )
)

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
marcorusso Member
Member

Re: Nested SUMX performance impact within Tabular cube

Assuming you have a business logic in your measures that require the SUMX approach (I cannot be sure of that), you could improve the performance by avoding nested loops. Replace ForecastTable and ShippingTable with the name of the tables containing data using by the corresponding measures.

Forecast Accuracy :=
IF (
    [Forecast Amt] >= [Shipped Amt],
    0,
    1
        - (
            DIVIDE (
                SUMX (
                    SUMMARIZE (
                        ForecastTable,
                        'Customers'[AccNo],
                        'Calendar'[Year],
                        'Calendar'[Month],
                        Items[Supplier],
                        Items[ItemCode]
                    ),
                    [Forecast Amt]
                ),
                SUMX (
                    SUMMARIZE (
                        ShippingTable,
                        'Customers'[AccNo],
                        'Calendar'[Year],
                        'Calendar'[Month],
                        Items[Supplier],
                        Items[ItemCode]
                    ),
                    [Shipped Amt]
                ),
                0
            )
        )
)
4 REPLIES 4
d_gosbell Established Member
Established Member

Re: Nested SUMX performance impact within Tabular cube

It's hard to say without knowing what issue you were trying to work around by putting in those sumx statements

 

What problem were you trying to solve by adding these?

 

What's the difference in output between your code and the following:

 

Forecast Accuracy :=
IF (
    [Forecast Amt] >= [Shipped Amt],
    0,
    1 - DIVIDE( [Forecast Amt], [Shipped Amt] )
)
wi11iamr Regular Visitor
Regular Visitor

Re: Nested SUMX performance impact within Tabular cube

The SUMX's are simply to ensure that my Grand Totals calculate correctly.

My understanding is that using SUMX with VALUES forces the calculation to iterate at a row level before calculating the Grand Total.

If I don't use the SUMX with VALUES, then my Grand Totals are incorrect.

 

Perhaps I'm not explaining myself very clearly, but it's a scenario/trick I learned in @marcorusso's DAX course last year.

marcorusso Member
Member

Re: Nested SUMX performance impact within Tabular cube

Assuming you have a business logic in your measures that require the SUMX approach (I cannot be sure of that), you could improve the performance by avoding nested loops. Replace ForecastTable and ShippingTable with the name of the tables containing data using by the corresponding measures.

Forecast Accuracy :=
IF (
    [Forecast Amt] >= [Shipped Amt],
    0,
    1
        - (
            DIVIDE (
                SUMX (
                    SUMMARIZE (
                        ForecastTable,
                        'Customers'[AccNo],
                        'Calendar'[Year],
                        'Calendar'[Month],
                        Items[Supplier],
                        Items[ItemCode]
                    ),
                    [Forecast Amt]
                ),
                SUMX (
                    SUMMARIZE (
                        ShippingTable,
                        'Customers'[AccNo],
                        'Calendar'[Year],
                        'Calendar'[Month],
                        Items[Supplier],
                        Items[ItemCode]
                    ),
                    [Shipped Amt]
                ),
                0
            )
        )
)
wi11iamr Regular Visitor
Regular Visitor

Re: Nested SUMX performance impact within Tabular cube

Thanks Marco, this did just the trick!!