Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 213 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
ago function does not use sequence number
Answers
-
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).
0 -
Hi,
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.
Date fine.
For the others not so good if you use just the value.
0 -
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.
0 -
Hi,
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....
0 -
Robert Angel wrote:So you can have hierarchies of things that are not really hierarchical
Yes and no.
Of course you can have weeks as children of months, often it just isn't the case as the main calendar is a real one where weeks cover multiple months.
And you can have hierarchies of whatever you want but the hierarchical rule is valid and mandatory ! A child can't have 2 different values as parent or your data will be wrong. As long as you respect that (and you can fake it using composed keys in case a child has 2 different parents) everything is fine.
Here the issue was really on the sequence numbers not being used and that is a kind of blackbox: the doc says that if you provide them and they follow the rules they are supposed to be used instead of ranking functions, but the OP still got a RANK in his query and that was the issue.
0 -
Hi Gianni,
always happy to be corrected!
I always use a single table for date hierarchies, to do otherwise is to impact performance - I have seen places use views on very large tables and then wonder why their queries always run slowly.
So, given one table, and given that each row of data has to possess every key, I don't see how a child could have multiple parents?
You could get; -
Month 1 Week 5
Month 2 Week 5
But as you say behind the scenes there should be
Month 1 MKey 1 Week 5 WKey5
Month 2 Mkey2 Week 5 Wkey6
Yes?
And my part was that the hierarchical values can be widgets, doohickeys, gizmos, whatchamacallums or whatever, provided the keys are good and the granularity is less to more then you are 'okay', even when perceived hierarchical relationships (as shown above) are not consistent.
So frequently the real difficulties in rpd modelling are down to bad keys in the underlying data.
Following the example above if Wkey did not exist then I would calculate it using Month||' '||Week in the rpd if not possible in the DW, as I said the key is genuinely uniqueness.
On your last note, any theories on why the keys are being ignored, I have never experienced this?
0 -
I guess we are saying the same thing (just in different words and by quick reading while thinking something else I maybe missed few words in your previous one)
The case with a child having multiple parents I see it more when you have multiple tables in your LTS and so you time dimension can have a join which duplicate rows. It isn't correct and it's where good keys definition is the key element like it has always been (and knowing your data of course).
Here the case is a bit more subtle: it isn't about logical level keys and chronological keys not being used, but really on that new thing Oracle added in 12c, the sequence numbers in time dimensions. I'm still skeptical on the real benefit of them, because in general with time series functions the slow part isn't the RANK but more everything else. But on another hand sequence number instead of RANK could allow to cheat a bit and generate a kind of restatement of the past without really having it in the time dimension. So by having OBIEE using a sequence key instead of a RANK on chronological keys and to achieve a different behaviour because of that.
In theory as long as you provided the sequence keys OBIEE isn't going to really need the chronological keys anymore as it has the sequence keys doing the job of a RANK on the chronological keys. And in practice it's what I got in a simple test (no ranking anymore). But the OP just still keep getting a ranking, and it's where the blackbox jump in as there isn't anything allowing to easily know why OBIEE decided to do that...
0 -
Robert Angel wrote:So, given one table, and given that each row of data has to possess every key, I don't see how a child could have multiple parents?You could get; -Month 1 Week 5Month 2 Week 5But as you say behind the scenes there should be Month 1 MKey 1 Week 5 WKey5Month 2 Mkey2 Week 5 Wkey6
That whole "week" story is why weeks should not (and normally are not) actual children of months unless your org has extremely precise rules and is using an own calendar to display this. I.e. a calendar which eitehr stops at week level or where the lowest-grain level of "Day" is a direct child of the weeks meaning that days form different months get rolled up through their direct parent "Week" into their grand-parent "Month" without actually belonging to that month in any normal sense of the word. Weird, I know, but I've seen it happening
Even the rollup into Years is questionable with a "Week" concept because the first day of the first week is hardly ever 01/01/2017 nor the last one 31/12/2017.
tl;dr - weeks are a messed up concept and will only ever mean something in an extremely well defined calendar context
0
