The AGO function resulting in a redundant join with the date dimension and causing performance issue

Question

Hi all,

I have the following performance issue when I use the AGO function in OBIEE 11g:

In the physical layer, my date dimension is joined to the fact table with a condition on date intervals. let's call this join Join1

In the logical level, I created different logical table sources (of the fact) and mapped them respectively to each date level because I wanted to add a WHERE clause that changes dynamically whenever the user selects a different date level (day/week/month, etc.)

Because I needed to use a date column in the WHERE clause, I had to map every LTS to the date dimension.

Now, whenever I use the AGO function, the JOIN1 conditions are actually applied twice. one time because of the AGO calculation logic and another time for the WHERE clause added in the LTS. This results in a huge performance issue and the query runs forever.

why is OBIEE behaving like this? and is there some workaround for this?

Please let me know if you have some ideas or questions to avoid this type of behavior.

Thanks

Rida · Answer

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:

Current behavior:

SELECT x,y,zFROM dimdate,facttable,ago_derived_table1 tab1,ago_derived_table2 tab2WHERE (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,zFROM facttable,ago_derived_table1 tab1,ago_derived_table2 tab2WHERE (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..

Thomas Dodds · Answer

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).

[Deleted User] · Answer

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.