I am new to both Oracle and Data Warehousing although I have been dealing with data processing on other platforms for a couple of decades.
We have a requirement to produce a cost forecast model. Events in the future timeline consume resources - such as fuel - at a known rate and a given unit cost. What I can't work out is how to allocate the resource consumption and subsequent cost across multiple time intervals in a data cube given a start date and end date for the event.
Say an event starts on June 15th and lasts 100 days, thus ending on 22nd September. The event consumes 100,000 litres of fuel, or 1000 litres per day on average. Either of the following allocation regimes would be acceptable, but the latter is preferable:
1) Allocate 25,000 litres of fuel (and the subsequent cost) each to June, July, August and September.
2) Split the allocation of fuel / cost between the months according to the number of days in each month which the event overlaps. This would be 16,000 litres for June (15th to 30th inclusive), 31,000 for both July and August and 22,000 for September.
Is there a simple way to do this kind of allocation with an OWB mapping? I'm using version 10.2. I'm very happy to be pointed in the direction of relevant web or book resources, but I have thus far been unable to find any which cover this matter.