Transforming a from/to date structure to daily records

[system]

I would like some help with how to use Data Integrator to populate an existing data warehouse which contains a fact table and a time dimension. The time dimension granularity is one record per day.

The source data I am using to populate the warehouse with has a “from date” a “to date” and a “measured value”.

I have been tasked with taking each source data record and creating one fact record for each day between the “from date” and “to date” and divide the measured value up into daily chunks ( the last value being modified to cater for any rounding errors)

For example:

Source data (Unique ID, From Date, To Date, Measured Value)
ID1 01-JAN-10 10-JAN-10 100
ID1 11-JAN-10 20-JAN-10 150

Would result in the fact table being populated with records like this:

Fact table: (ID, Time FK, Measured Value)
ID1 20100101 10
ID1 20100102 10
…
ID1 20100110 10
ID1 20100111 15
…
ID1 20100120 15

My question is, can this be done with the standard Data Integrator transforms?

---

petejg (BOB member since 2010-06-28)

[system]

120×120 10.4 KB

Hi, I am trying convert one row in to many row below is the record in coming clientid locationid planid flightid stardate enddate 123 345 567 897 4/19/2004 4/25/2004 I need to convert this like below: clientid locationid planid flightid date 123 345 567 897 4/19/2004 0:00 123 345 567 897 4/20/2004 0:00 123 345 567 897 4/21/2004 0:00 123 345 567 897 4/22/2004 0:00 123 345 567 897 4/23/2004 0:00 123 345 567 897 4/24/2004 0:00 123 345 567 897 4/25/2004 0:00 I don’t thin…

Hope this helps

---

prabaharan (BOB member since 2007-05-16)

[system]

Thank you, that’s exactly what I need to start tackling this problem.

---

petejg (BOB member since 2010-06-28)