Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 16 Oracle Analytics Lounge
- 216 Oracle Analytics News
- 43 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 79 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Issue with Month Year sorting by Date in 12c
Hello all
We have recently upgraded to 12c and are finding issues in sorting dates.
In the rpd we have the following structure for all date dimensions:
Our hierarchy looks as follows:
In 11g we were able to sort the Calendar Month Year column to by the date (see below).
If we did not sort, the data would appear as:
December 2014
December 2015
December 2016
…
…
February 2014
February 2015
February 2016
Instead of :
February 2014
…
…
December 2014
…
…
February 2015
…
…
December 2015
…
…
February 2016
…
…
December 2016
etc
When we upgraded to 12c, we are unable to select the Date to sort on. It only lets us choose columns that are in the same hierarchy level as the column we are sorting.
I have researched this problem and one solution seems to be to add the sort column to the hierarchy level required. Does this mean we need to create duplicates of the Date column to add it to the 2 levels (Month and Day)?
Has anyone else encountered this?
thank you
Sam
Answers
-
Could you check if this behavior still observed if Hierarchy for Logical Dimension Table doesn't exits, I mean to say trying to set "Sort Order Column" before Dimension Hierarchy creation , just wanted to check if hierarchy isn't there then still the restriction of column for sort order column selection exists.
Hope I din't confuse you but to avoid confusion could you test below steps:
a) Take a backup of your current RPD in case below steps doesn't work.
b) Delete your Game Play Date Hierarchy.
c) Edit column Calender Month Year for additing Sorder Order Column, check if you are able to add the same now.
d) Recreate the Hierarchy.
I have not tested the issue its just a thought to get you moving , if above works then actual issue should be bug in OBIEE 12c
0 -
Hi Sam,
This limitation is new in 12c but it makes sense from a logical point of view.
When you mix columns of different levels, using a column of the "day" level to sort one of the "month" for example you can have an issue from a logical (and practical) point of view because your month will have multiple days associated, so the sorting isn't really stable and always the same.
With a column at the same logical level the system avoid that and doesn't need any extra check because for every given month your other column (the sorting one) isn't supposed to change value (or you have issues in the levels definition).
So to sort month columns you generally have a numeric representation of the months, like 201707 or 2017.07 or any other form similar to this with the year first and then the month on double digit.
It's new in 12c, one of the many new rules/restricting to make sure the RPD is "better" (from a logical point of view).
0 -
+1 to Gianni. This was actually one of the "unclean" config options that earlier versions permitted which are not actually valid in the strictest sense of dimensional modeling.
0 -
Thank you Gianni for explaining this. It does make sense when put that way. We will go down the path of creating additional columns to sort the month (as per your suggestion).
Really appreciate everyone's time.
Regards
Sam
0 -
You can also consider to create them in a way matching the requirements of sequence numbers for time dimensions: https://gianniceresa.com/2017/01/sequence-numbers-time-dimensions-new-obiee-12c/
In case you plan to maybe implement this at a later stage, the same columns can definitely be used to sort the months as well.
0
