Populating Fact Table

[Guest_imported]

Is there a way to populate a fact table with PL/SQL? I wrote queries and have been trying to use them to insert data into the fact table but am not having much luck.

I can export the data then generate insert statements using other applications, but this seems a bit round about.

Thanks,
DinaZ

(I'm new to Oracle8i)

 

[carp]

I have a routine that populates my fact table by
(a) populating a cursor that does the aggregation/computation of facts and groups the detail records by the columns I'm using for dimensions.
(b) for each record in the cursor, I get the dimension keys from the dimension tables. If a key cannot be found, then the current dimension value is inserted into the dimension table and the key is then returned.
(c) Once all dimension keys are known, the record is inserted into the fact table.

There are probably better ways to do it, but this works for us, and can reduce a million row detail table to 16 rows of loaded facts (only nine dimensions) in about 18 seconds on a Solaris box w/ two CPUs.

 

[Guest_imported]

carp,

I haven't worked with cursors yet, but I will give it a try.

Thanks,
DinaZ

 

[carp]

Rather than clutter up the forum with the source code, if you send me your email address I can send you the source code for my procedure (well, early source code, when there were fewer dimensions - but the idea's the same!).

 

[Guest_imported]

carp,

Yes, that would be great. I couldn't find your e-mail address in the forum. My address is: dinaz1@zdnetonebox.com

Thank you,
DinaZ
:

 

[Guest_imported]

Carp
Can I ask you to send me the source for populating the fact table?
I am at chidiokeh@yahoo.com
thank you!
c.

 

[carp]

Consider it done!