Categories
- All Categories
- 89 Oracle Analytics News
- 7 Oracle Analytics Videos
- 14.1K Oracle Analytics Forums
- 5.3K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 50 Oracle Analytics Trainings
- 59 Oracle Analytics Data Visualizations Gallery
- 2 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
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?
Answers
-
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.
0 -
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.
0 -
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?
0 -
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?
0 -
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
0 -
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
0 -
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.
0 -
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.
0 -
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.
0 -
Yes your points helped me to analyze the correct answer. And I marked your posts as helpfull.
Thanks Again!
0