Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 17 Oracle Analytics Lounge
- 218 Oracle Analytics News
- 44 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 81 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Time series functions
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
Answers
-
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 ...
0 -
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.
0 -
My turn to echo your sentiment...
0 -
thank you guys for your clarifications!
I've understood your point and how Oracle intended those functions.
Kind regards,
Marco
0
