Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Issue with Ago function in OBIEE

Received Response
215
Views
10
Comments
2845107
2845107 Rank 4 - Community Specialist

Hi,

Below is the scenario of my issue.

pastedImage_0.png

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

  • [Deleted User]
    [Deleted User] Rank 10 - Analytics Guru

    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
    2845107 Rank 4 - Community Specialist

    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]
    [Deleted User] Rank 10 - Analytics Guru
    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]
    [Deleted User] Rank 10 - Analytics Guru
    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
    Robert Angel Rank 8 - Analytics Strategist

    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
    2845107 Rank 4 - Community Specialist

    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
    Robert Angel Rank 8 - Analytics Strategist

    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]
    [Deleted User] Rank 10 - Analytics Guru

    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
    Robert Angel Rank 8 - Analytics Strategist

    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
    2845107 Rank 4 - Community Specialist

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

    Thanks Again!