Hello,
I am trying to fine tune a large financial application with millions of rows. Some of the metrics sum well across time, such as revenues and profits. Monthly revenues can be expressed as the sum of daily revenues.
But others, such as the portfolio total value, cannot be summed across multiple days or months. The monthly total value would be expressed as the total at the end of each period, not the sum of daily values.
I tried many different ways to express this. The best way I found was to use set analysis to force the date, such as:
SUM({<[Snapshot Date] = {"$(vG.SelectedDateMax)"}>}[Position Total Value])
where vG.SelectedDateMax is the max date. But using this technique, I cannot create a table of "Total Value per Quarter", for example. The max date is forced globally. Also, set analysis appears not to play nicely with section access, performance wise at least. Tests we did showed that we lost most of the performance gain of section access prefiltering the dataset.
I also tried to create monthly, quartely and yearly facts in the load script, but this multiplies by 5 the load time because of the aggregations, which is not really an option.
Then I tried replacing the set analysis with normal functions. I can manage to find the same answer with this function:
FirstSortedValue(aggr(sum([Position Total Value]),[Snapshot Date]), -aggr([Snapshot Date],[Snapshot Date]))
But this technique is much slower that the set analysis technique, again probably because of the aggregations.
Can someone suggest a better way to do this?
Thank you!
