Oracle Communities

Oracle Analytics and AI

Child Item

Issue with Ago function in OBIEE

Hi,

Below is the scenario of my issue.

When I try to add the “# of Orders Previous Year” ago function formula to the criteria both the “# of orders” and “# of Orders Previous Year” become 0. The expected results should be as above.

“# of Orders Previous Year” formula is Ago (# of orders, Person Visit Year, 1)

Can you please help me with your inputs on the issue?

Find more posts tagged with

Accepted answers

All comments

[Deleted User]

9 times out if 10 this means that your RPD config is wrong and most likely your time hierarchy is at fault due to incorrect chronological keys.

2845107

Thanks for the reply.

If I remove "Person Visit Date" from above case statement mentioned. I get the correct results for “# of orders” and “# of Orders Previous Year”.

Currently chronological key is set at detail level. So I have modified the hierarchy by putting chronological key at each level and tried, but there is no luck. No.of elements at check level are also correctly set at each level.

[Deleted User]

2845107 wrote:Currently chronological key is set at detail level.

Not sure I follow what you state there. Every level must have a chronological key in a time hierarchy, not just one.

Can you post screenshots of your time hierarchy configuration?

[Deleted User]

2845107 wrote:Currently chronological key is set at detail level.

Not sure I follow the logic there. Every level must have a chronological key in a time hierarchy, not just one.

Can you post screenshots of your time hierarchy configuration?

Robert Angel

For guidance on chronological key set up see -> https://gerardnico.com/dat/obiee/obis/time_dimension (dated but still relevant)

And for guidance on ago -> https://gerardnico.com/dat/obiee/obis/logical_sql/ago

2845107

I have reviewed my time dimension hierarchy and in that at the month level logical key which is defined(Cal_Month) has the value like 12,11,10 etc. Since we need to have a combination of year and month in the logical level key . I have used the column(Per_Name_Month) which has the value like 2018/12,2018/11,2018,10 etc. So after using this column as the logical level key. I am getting the values populated for previous year.

And also I need to have Person Visit Date column in criteria and exclude in the results because the metric values are at the Person Visit Date level.

With these two changes I am getting the values for both both the “# of orders” and “# of Orders Previous Year”.

In my time dimension hierarchy chronological key is set at detail day level which is on the column Row_Wid.

As I am having at least one chronological key in my hierarchy which needs to be at detail which I have. So this should be sufficient enough?

Please let me know your thoughts.

Thanks for the help

Robert Angel

Hi,

sorry to interject Christian, but this is basics...2845107 you are hurting me....

Time Dimension

year: key 2018

quarter: key 201803

month: key 201807

datekey: 24072018 (or wid is good!!)

Also set number of elements to give OBIEE a hand along the lines of 365 / 12 / 4 / 1

Set this up in your time dimension, using each as chronological keys (display keys may differ)

Then you can ago (etc) on year, quarter, month, day levels.

And OBIEE can optimise your sql around time dimensions which will appear all the time.

Then your users get a useful built in time drill path.

Don't think of one analysis.

Think of a BI Architecture which serves many purposes, developed in a consistent, coherent and performant way.

[Deleted User]

Thanks for confirming my suspicions. Chronological keys must exist on each level of hierarchy and be unique.

So as in your statement above

- 2018

-- 01

--- 20180101

as keys won't work since it would aggregate across all 01 months - so all Januaries.

Apart from that you also found the way to force a given granularity of Person Visit Date which is fine as well.

For this use case / example I'd say you got it all.

Robert Angel

Did we solve your issue?

If so can you kindly close the question (assumed answered or if you want to point the answer out for the benefit of others 'Mark Correct'.)

thanks,

Robert.

2845107

Yes your points helped me to analyze the correct answer. And I marked your posts as helpfull.

Thanks Again!