MONTH_WID to Month or Calculating volume on monthly basis based on month_wid

Question

I have month_wid and volume columns, i need to bring 12 columns(user request ) for adhoc reporting as Jan Volume, Feb Volume .... etc

Data in month_wid will be like 201501, 201502 ... etc

How to proceed on this ?

Note : This fact table is not joined to any time dimensions, both month_wid and volume are coming from same table. This table is connected to a dimension which has Shipping Year column.

Thomas Dodds · Answer

Doing this in the front end is not going to get you far ... you need a proper design where you have the text and you can set that logical column's sort based on the numerical representation of the same.

3139796 · Answer

After writing the case statement for month, how to achieve sort(Jan, Feb... instead of April, August...). As it is getting arranged in alphabetical order

Helan Kumar · Answer

Thanks much for your inputs, i never seen this logic till now MONTH_WID MOD 100. Very helpful !!

I do agree with the Pivot view, but user has 12 columns in the source application and she is expecting same in OBI. they have used normalizer to convert it into rows in ETL. This might have some other purpose not sure.

Apart form Shipping Year, i don't see any other time dimension attributes in the table and there are no WID's populated to join with time dimension.

Any how i'll give a try with the case statements.