Issue with Month Year sorting by Date in 12c

Question

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

Gianni Ceresa · Answer

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.

Sam H · Answer

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

[Deleted User] · Answer

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