AGO function issue in OBIEE 12c

Uttam.Singh

Need some suggestion on this issue we started facing after upgrading to 12c (12.2.1.2.0) where we are using AGO function in the RPD to get the Year Ago sales. It used to work fine in 11g but after upgrading to 12c we are seeing this issue where the sales for the previous year is showing wrong values. Not sure if this is a bug or not.

Please provide your inputs which all places I should be checking to correct this.

1. We did use the W_DAY_D as the Time Dimension with all the right Chronological key set in the RPD correctly

2. The Physical Query generated is kind of a stitch join

3. I tried creating a measure at the webcat level using the similar formula after pulling in the Time hierarchy - AGO("Measures"."Amount ($USD)" , "Time"."Date Posted"."Year", 1) but it still shows the wrong value against the Year Ago measure of the current month.

4. The only difference I noted is that the Key used Year (PER_NAME_YEAR), Quarter (PER_NAME_QTR) are nullable at the DB level and does contain a row with null value. There is a row in W_DAY_D where ROW_WID = 0 which has all the above fields as null. I don't believe this would cause an issue but need your suggestion if I should replace the null with 0 and test it out (I will do this and check too).

Results: In the example below, the Year Ago Amount for 2017 / 01 should be from 2016 / 01 (3,850,804) but its showing 119,374,929.

Joel

I think you should start with tidying up your time dimension. That should be the 1st step to rule out the data in there that needs to be excluded.

Uttam.Singh

Hi Joel - Can you please help to elaborate on this?

Joel

I think you mentioned that there were some invalid data entries in your time dimension. I was just suggesting that you clean these up and then retest in order to determine if this is the culprit of your issue.

[Deleted User]

You claim "We did use the W_DAY_D as the Time Dimension with all the right Chronological key set in the RPD correctly" but seeing what you're facing I'd say after making sure you have a properly populated physical time dimension you should make sure your logical time dimension is actually correct...

Uttam.Singh

Yeah - I did update that Null row with some values but it still didnt help.

Gianni Ceresa

Your analysis generate a physical query, take that query, read it and understand what kind of logic it's applying. You will probably find the error in the model or where it is ...

Uttam.Singh

Thanks Gianni - Its the same formula which was working fine in 11g. There are no other changes to the data model or RPD but still we are facing the same issue.

Uttam.Singh

Thanks Christian - Yes, the logical dimension is set properly as well.

Gianni Ceresa

You are missing the point: do not expect OBIEE 12c to generate queries in the same way as 11g does. The product will always try to get smarter and smarter and generate the best query. So if your model isn't perfect the query can change over the versions.

That's why I said to take the physical query, read it, understand it, and find out what it does (these numbers you see on your screen probably aren't random, so find the logic the query used). Once you get the logic used by the query you can go back to your model to find out what and why made OBIEE think in that way.

Uttam.Singh

well said - I totally agree with you.. Frankly speaking the query generated by OBIEE is not easy enough to understand and hence I was looking for some suggestions from the SMEs to pin point the issue.