Hi Luigi,

I don't think you are understanding me.

For functions like AGO to work you have to have a well formed time hierarchical dimension with appropriate chronological keys at each level.

Appropriate = uniquely reflecting the level of granularity of the level in question.

You need to check the key that is defined as the unique identifier at week level in your time dimension hierarchy that must have been created to enable AGO to work for month to work.

Your key should be along the lines of 201749 for week 49 of the year 2017, 49 by itself is not valid. (Or a surrogate key equivalent that changes at the same rate as the underlying year / week does.)

What you are showing me is the dimension, you need to show me the (probably level based) hierarchy.

More importantly you need to check that the week key is fit for purpose, and not a weak key....

Hi Luigi,

thanks for the screenshot, what I am asking you to do is to click on the WEEK key in your business model hierarchy as per your screenshot above, query the PHYSICAL column. Then when you are 'on' the physical model column use the tool to query the underlying database and ascertain if your key; -

a. truly represents the granularity of your physical weeks, i.e. if you have 5 years data and 52 weeks in each year (to keep my maths simple) do you have 5 x 52 week distinct keys?

b. Is the key sufficient to represent the uniqueness at that level, i.e. NOT 48,49,50, but 201748, 201749, 201750 or surrogate key equivalent.

Hope that clarifies?

My point is a crucial one.

Keys are of paramount importance in OBIEE.

In the AL_PERIOD_SCH_SHIP_DATE I would expect (in one row for todays date as an illustrative example); -

YEAR_ID 2017

QUARTER_ID 201704

MONTH_ID 201711

WEEK_ID (I see this is not present in your table, sound of alarm bells here!!! - your week probably looks like 47 not 201747 - i.e. it is not a key which would let me find ONLY the week in question, BECAUSE IT LACKS SUFFICIENT DETAIL TO DO SO - excuse the capitals!)

So, I would suggest in your business model logical table you need to create a concatenation of Year_ID and Week to make a Week_Id which can then validly be used as the key in the time dimension and which will work in the AGO function.

Note - as a function this will not be the most performant way to do it, the best way is to go back to your Data Warehouse developers who populate the table and ask them to create a unique week id in the physical table, and then model it through to use it in the appropriate place.

For further explanation on the concepts see this => https://gerardnico.com/wiki/dat/obiee/obis/time_dimension

Sure, hope you get there.

Kindly also mark whatever helped or turned out to be correct for the benefit of those who follow.

Robert.