Oracle Communities

Oracle Analytics and AI

Child Item

ago function does not use sequence number

I provided both absolute and relative sequence number in each level of time hierarchy. However when I run the report, the OBIEE still used rand function to generate the sequence instead of using the sequence that I provided. Did I miss any step? The obiee tutorial did not tell anything about the sequence number.

Find more posts tagged with

Accepted answers

All comments

Thomas Dodds

to use timeseries (ago, todate, etc) ... hierarchy must be marked as a TIME type ...

User729543-Oracle

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.

Martin van Donselaar

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

User729543-Oracle

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.

Gianni Ceresa

Hi,

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?

User729543-Oracle

That is correct. OBIEE keep ignoring the sequence columns that I use in the hierarchy level. Not sure what is wrong.

Gianni Ceresa

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?)

User729543-Oracle

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).

Gianni Ceresa

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 ...

User729543-Oracle

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.