to use timeseries (ago, todate, etc) ... hierarchy must be marked as a TIME type ...
Thanks for the reply. I already used TIME type. The ago function works, but it still uses rank function to generate sequence in the sql. In OBIEE12c, they added sequence number tab on the hierarchy level, so you can specify sequence instead of using rank function. My issue is not ago not working. My issue is the ago still use rank instead of using the sequence number that I provided.
What column are you using as a sequence and what does this column contain?
Are you sure it is really sequential? That is, you can get the leading value simply by adding 1 to that value.
11 + 1 =12
2016 + 1 = 2017
But: 201612 + 1 <> 201701
For year, I use year number. For month, I use 1 to 12 for relative year and 1 to 3 for relative quarter. For week, I use 1 to 52 for relative year, 1 to 13 for relative quarter, and 1 to 4/5 for relative month. I do the similar thing for day. The only thing that is not sequential is the absolute sequence number for the level other than year, but I don't think OBIEE use it. In ago function, I use week in the query level and use year in the shift (ago) level.
So adding the info you posted in the comments on https://gianniceresa.com/2017/01/sequence-numbers-time-dimensions-new-obiee-12c/ I have this view on your need: you have an AGO at the year level for measures at the week level (so something like sales of this week compared to sales of the same week last year).
You set relative sequences for almost all your levels based on your post above but OBIEE keep ignoring the sequence numbers.
In theory based on a quick test I did when writing the blog AGO is supposed to use the absolute sequence for the year and the relative sequences for levels underneath it, and apparently these are the sequences you defined.
Am I correct or missed something somewhere?
That is correct. OBIEE keep ignoring the sequence columns that I use in the hierarchy level. Not sure what is wrong.
What you can do is a really simple test: you have an absolute sequence at the year level, so try an analysis with just the year, a measure and the same measure with AGO year-1.
So a super simple one which is supposed to use your absolute column instead of ranking.
How does the query looks like? (simpler than that it can't be ...)
Also what is the physical database? (oracle? version?)
The year level works, but the next level (using relative sequence) did not work.
Also have you worked on a scenario which a dynamic calendar is needed. For example, 2012week 5 belongs to month 2 for current year (report month sales in year 2012), but the same 2012week 5 will belong to month 1 in the last year metric (report month sales in year 2013). This is because 2012 has 53 weeks and it has to be restated in the last year metric. So a calendar year with 53 weeks has different week/month relationship when it is used in a regular metric and when it is used in a last year metric (ago function).
I didn't, and when you explained your time dimensions you actually did what I generally avoid: a week can't fit in a month, so for most of the project the time dimension split in 2 alternate hierarchies: year > quarter > month > day and year > week > day .
The "week" between month and day often give issues because it just keep moving from one month to the other depending on many things.
My scenario was just a really basic test with year > quarter > month > day and tested the AGO function on months using "1 year ago" for the same month. In my test I also set absolute sequences for all the levels, even if not used maybe the lack of them explain why your real use case doesn't use sequences?
Just making a guess as, as far as I know, there isn't a lot written on that topic ...
We use retail 454 calendar, so a full week always belongs to a month. The challenging thing is that a week could belong to different months in a regular metric or in a last year metric (ago).
I am still stuck on sequence number issue. Not sure what setting caused the feature not working. Hope the OBIEE support team can jump in.
If you wait a formal / official answer from the "Oracle" side you will need a SR or at least to ask them to move the post in the MOSC space (but even there it isn't as official as a SR).
in my experience you need wholly UNIQUE keys at every level.
In your scenario week 26 for (say) 2015 is not differentiated from week 26 for any other year.
What I am suggesting is generating a pseudo key for your quarters, months, weeks etc that will give uniqueness and sequentiality, you can make the display key the 1-52 for week, but behind the scenes use a pseudo key such that yields a wholly unique key, and this should be used for your chrono key.
So year Fine.
For the others not so good if you use just the value.
Also set up your dimension hierarchy to reflect this, total, year down to your lowest level of granularity with the chrono keys as the key for each level and all other members at the same level of granularity populated on the same tier as its key, the ago function will then use these keys in its logic.
I don't think week is an issue, I am sure I have had this in hierarchies previously and as long as you have more weeks than months (and it would be a really strange year otherwise!) then it is fine, the granularity is the important factor, less to more is the rule.
So you can have hierarchies of things that are not really hierarchical; -
Building; Floor of Building; Room of Floor of Building.
This is not a real parent child hierarchy....