Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 17 Oracle Analytics Lounge
- 218 Oracle Analytics News
- 44 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 81 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
MONTH_WID to Month or Calculating volume on monthly basis based on month_wid
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.
Answers
-
"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
0 -
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
...etc...
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).
Good luck!
0 -
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.
0 -
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
0 -
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.
0