This content has been marked as final. Show 5 replies
Get the physical query from BI and run in db, and check the cost to see bottle neck.
Based on that you may go for aggregate table or any other approach..
It is very hard to explain performance tuning
You can create those time series measures in the DB itself
and simply pulling them in to analytics.
I am still working on performance. But I will close this question for now.
This is why I often make an alias to the fact table, and join it to the time dimension using year - 1. To me the SQL comes out legible and usually performs better than the version that comes out using AGO.
1. Create an alias fact table (Year Ago) to pull last year value.
2. Extend your fact table to store another measure (last year sales)
3. Based on volume of granular data and query pattern on year ago measures, you may create aggregate fact tables.
hope this helps.