Changing Default Sorting of a Column in OBI EE

In our Time dim, we have a column, which displays the month as

FY2009 Nov

FY2009 Dec

…

When the data is sorted, BI treats the data as character data, i.e. FY2009 Dec will come first and FY2009 Nov will follow that.

The physical query fired by BI is

select distinct T5686.FISCAL_MTH_NAME as c1

from

D_PERIOD_MST T5686

order by c1

Our requirement was to sort the data as per the month nos while displaying the month names so that FY2009 Nov will come first then FY2009 Dec.

To do this, in the RPD, open the properties of the column whose auto-sort needs to be modified, click on ‘Set…” button. A window will open with the available columns in the dimension. Select the column which needs to be used for sorting and click on ok. Click on OK again to accept the changes.



Save the RPD and run query again. Now the month names will be dispalyed in the sorted order of the month no.

Here is the physical query fired by BI.

select distinct T5686.FISCAL_MTH_NAME as c1,

T5686.FISCAL_MTH as c2

from

D_PERIOD_MST T5686

order by c2

For the same query fired above, BI added the column which was set as the "Sort Order Column" as part the physical query

In this way, we can tell OBI EE to use different column for sorting the data of a column.