"This fact table is not joined to any time dimensions" --- that's why any approach will probably be inefficient ... you should always have if atr the least a rudimentary time dimension from which to translate keys into textual items for use.
USE OF INFORMATION DRIVES DESIGN
1 person found this helpful
Thomas is correct, the design is sub-optimal, but you have a problem, let's try to solve it. There are a couple of ways to do it.
Brute Force: If you require twelve measures, "Jan Volume", "Feb Volume", etc., you can create them in your logical fact table by using a CASE statement on the last two digits of your month_wid. So for example, your measure formulas would be:
Jan Volume: CASE WHEN MONTH_WID MOD 100 = 1 THEN Volume ELSE 0 END
Feb Volume: CASE WHEN MONTH_WID MOD 100 = 2 THEN Volume ELSE 0 END
Pivot Table: You may not actually need twelve measures, but one measure "Volume" and a "Month Name" dimension. Then use a pivot table to show volume by month. Your "Month Name" dimension would also use a CASE or SWITCH statement, similar to above. CASE WHEN...1...THEN "Jan" WHEN...2...THEN "Feb"...etc.
Shipping Date Dimension: You could improve your design by creating a Shipping Date dimension which includes Shipping Year, Shipping Month, Shipping Date (assuming this is available), etc. The logical source of this dimension would be your main (fact) table + your dimension table which has Shipping Year. You can pull Year directly, and Quarter, Month, Date can all be calculated using CASE statements. The fact that the logical dimension is actually using your "fact" table as a source is of no consequence. To OBIEE it will look like a proper logical date dimension. At some point in the future you could split out an actual physical Date dimension table and then change your mappings to use that table instead - the model will behave the same either way (with possibly slight performance differences).
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.
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
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.