Home » Developer & Programmer » Data Integration » Building a fact table in Oracle 10G
Building a fact table in Oracle 10G [message #155192] Thu, 12 January 2006 15:23 Go to next message
selectsplat
Messages: 2
Registered: January 2006
Junior Member
Greetings.

I'm a long time Data Warehouse developer, however, I've very new to Oracle.The process of building a fact table for a star schema in SQL Server or Sybase goes something like this...

- Update your dimension tables.
- Select all of the values from the various tables in the staging areas you want to go into your fact table.
- Cycle through each column in the fact table, join to the cooresponding dimension table, and replace the 'value' with they 'key' from the dimension, so that you end up with a fact table of nothing buy key values.

During the process of this last step, I normally create a series of #temp tables, one for each step of the way. However, I am told that in Oracle, you don't create temp table, but just join all tables together at once.

My question is, do I still use this same process, except instead of replacing one column at a time, just do them all at once? And, should I keep this as two steps, first gatehr the column I want from the stage, then replace all the values from the dimensions, or should I do all of this in one step?

Thnaks in advance.
Re: Building a fact table in Oracle 10G [message #159128 is a reply to message #155192] Thu, 16 February 2006 05:39 Go to previous messageGo to next message
jayadesh
Messages: 10
Registered: February 2006
Location: INDIA
Junior Member
HELLO GUYS,
THE METHOD U TOLD IS PERFECTLY CORRECT.
ONCE DIMENSION TABLES IS UPDATED.
YOU HAVE TO GO FOR STAGING TABLES.
THEN U CAN REPLACE UR KEY VALUES WITH ID'S.
THANKS
Re: Building a fact table in Oracle 10G [message #159143 is a reply to message #155192] Thu, 16 February 2006 06:34 Go to previous message
selectsplat
Messages: 2
Registered: January 2006
Junior Member
Thanks for the response, but it doens't really answer my question.

In orderable, am I supposed to replace the value for keys for all dimansions at once, even if I have 30 some dimensions?

IN SQL Server, I'd do one at a time, in a series of temp tables. In oracle, I'm told not to use temp tables, but a 30 table join does sounds like the most efficient way to do things.
Previous Topic: Work flow of warehouse buider
Next Topic: Import PACKAGE OWB
Goto Forum:
  


Current Time: Thu Mar 28 15:29:23 CDT 2024