Hi Guys,
We have a reporting tool which extracts reports from tables having data ~30 millions records.
Users have created many analysis which uses a field (field name - "trailing twelve months") formulated at logical layer , this field returns sum of a field values for last 12 months.
Many filters are added further in analysis based on user's requirement.
Currently this field is built at logical layer using Ago function, it works fine but performance is too bad! Users have almost stopped using it.
After analyzing we found that because in "trailing twelve months" calculations Ago function has been used 4 times for last 4 quarters, a very costly query is run at physical layer 4 times, that too without any where clause. It seems to be the main issue of bad performance.
I tried below options:
1. Using Log function instead of Ago at logical layer - failed! because Lag function can be used to formulate series of values
2. Created DB function to calculate SUM of required field for last 12 months and mapped to logical layer - failed! because user wants to add many other filters dynamically, so returning aggregated values from DB doesn't work.
Please help on this.
Thanks!
