#1. Sum(LineTotal * UI_CurrencyRate)
#2. Sum(LineTotal) * only(UI_CurrencyRate)
#3. Sum(LineTotal * $(=only(UI_CurrencyRate)))
I would expect an aggregation of a product to be cheaper than a product of two aggregations. That fits with the second expression being slower than the others.
Doing the second aggregation outside the chart context performs pretty good too:
#4. Sum(LineTotal) * $(=only(UI_CurrencyRate))
When Product is used as dimension then both ProductCategoryName and LineTotal are 1 hop away and on the same side of the fact table. In all other cases where two dimensions involve a hop over the fact table the performance takes a hit. The combinations Product - Channel, Channel - Year-Quarter and Product - Year-Quarter exhibit the same behaviour.
Very interesting. I hope somebody can explain it in more technical detail.
1. "Product" as 2nd dim is faster than "Year-Qtr" and "Channel" as 2nd dim
This does not surprise me. "Product" and "Category" are the same dimension, basically. Together there are only 295 combinations and these can be generated without running through the fact table. Whereas the "Category"-"Channel" combinations can only be found by running through the (large) fact table and creating the necessary lookup-tables.
2. Expression #3 is fast
This does not surprise me either. The dollar expansion is made before the other steps of the chart evaluation, resulting in one single value that is used all over the cube: The evaluation of the Only() function is really not part of the building of the cube.
3. Why is #2 slower than #1?
I haven't got a clue... I need to ask Håkan.