Why is it behaving like this? Because that's how all time series functions work: They retrieve several instances of your data corresponding to the time horizons and function you chose.
Workaround would be to use LTS modelling to do that AGO.
The builtin AGO function presupposes the logic to retrieve the data ... the trade is performance for flexibility. In your case the flexible function is a detriment to performance - so to get your performance you will have to model logically or physically (removing some flexibility) to get same results (with performance).
Thank you for the replies! and sorry for the late answer, I had to give it a lot of thought.
I still think that this behavior is not optimal at the least, and here is why:
When the join between the date dimension and the fact is already applied once in the AGO calculation, OBIEE could easily just add the WHERE clause -defined in the LTS- to the filters applied in the SQL query. Instead, OBIEE joins to the date dimension a second time to apply this clause. I will try to show it by example in the following:
SELECT x,y,z FROM dimdate, facttable, ago_derived_table1 tab1, ago_derived_table2 tab2 WHERE (tab1 joined with fact) -- This join is equal to the date dimension join with the fact table (tab1 joined with tab2) (WHERE CLAUSE of the LTS) (dimdate joined with fact)
What is expected and more optimal in my opinion:
SELECT x,y,z FROM facttable, ago_derived_table1 tab1, ago_derived_table2 tab2 WHERE (tab1 joined with fact) -- This join is equal to the date dimension join with the fact table + the WHERE clause (tab1 joined with tab2)
I hope it is clearer now..
Actually the only workaround I see now is to remove the WHERE clause logic from the LTS to the physical layer, which is stupid and brings lot of model duplications..