Time series functions

marcobalduini

Hi all,

I've a curious behaviour on time series functions.

I'm working on 12c (12.2.1.2.0) version.

My fact table is at day grain,

my time hierarchy is correctly set up (chron K are present).

Trying to build a simple report

MONTH | REVENUE | AGO (REVENUE,MONTH,1)

For some months the ago function is not working properly.

Going deep in analysis, looking at physical SQL i found how OBIEE calculate AGO periods

and I found the issue on SQL side, but I think that this function is used all around the world so I think (hope) that the error could be on my setup.

Here's what I've found:

OBIEE build two logical table in the SQL,

- table 1

nr. months | nr. weeks | nr. day in week

- table 2

nr. months+1 | nr. weeks | nr. day in week

then OBIEE joins the two tables with all columns to create correspondance with current month and previous month.

In my case I've selected dates between 01 Jan 2017 and 28 Feb 2017.

If I look at the output of intermediate SQL,

table 1 (referred to Feb) has

2 months | 5 weeks | 7 day in week

table 2 (referred to Jan) has

2 months | 6 weeks | 7 day in week

So for week 6 of Jan the join is not resolved - Feb has only 5 weeks - and I loose those days in the sum of my measure.

Seeing that , it looks to me that AGO function could work only at month or year level, when my fact table has month grain...could not find anything on Oracle doc about that.

Am I doing something wrong?

Kind regards and sorry for very long post..

Marco

Gianni Ceresa

Hi,

I will say it depends on what you expect to get ...

When you look for data of the 28 Feb, what values do you expect in the AGO column? 28 Jan or 28 Jan + 29 Jan + 30 Jan + 31 Jan ?

I guess the thing is more in the meaning of "AGO", the fact that part of January is missing is technically not wrong if you ask the system to give you the value of the same day a month before.

A bit like with 30 March : what value of February do you expect? NULL or 28 Feb ?

As you see it's all about the "meaning" of it, but technically I don't see it as an issue, more as a "not clearly defined from a functional point of view" thing ...

Robert Angel

Hi,

no, nothing wrong, that is how it works.

If your time hierarchy goes down to days then you could use days ago with a standardised number of days for a pseudo month or based on the number of days in the current month.

Then I guess you might have a different issue, how many working / selling / etc days depending on what sector you are in.

Then you might also need to account for statutory holidays.

In short it is not scientifically accurate, but can be sufficient for some purposes.

Robert Angel

My turn to echo your sentiment...

marcobalduini

thank you guys for your clarifications!

I've understood your point and how Oracle intended those functions.

Kind regards,

Marco