relate data between Oracle tables (foreign key).

[frisha]

Hi All,

As we know, the Oracle stage (ORA 9i) can have a single input link and a single reject link, or a single output link or output reference link.
[b]The Oracle stage can have only one input link writing to one table.[/b]

My question is how we would relate data between Oracle tables, otherwords create foreign keys if we are talking about different jobs to create for "n" number of tables.

Thanks.

[roy]

Hi,
IMHO DWHs are not something you have FK constraints on (period) and expect loading performance.
your ETL should have integrity checks for that and the DWH should be free of such things (correct me if I'm wrong people).

now if you insist on having FK constraints then do it the slow sequential way you enforce by having them.

IHTH

[chulett]

As we know, the Oracle stage (ORA 9i) can have a single input link and a single reject link, or a single output link or output reference link. The Oracle stage can have only one input link writing to one table.

No, not exactly true. The OCI stage can support many input/output/reference links at a time. They do all have to connect to the same schema, but you can have multiple links and even write to the same table at the same time over multiple links. Just be aware of any deadlocks or commit issues you may create in the process.

[frisha]

I am still confused.
I have one out file (CFF stage) wich link to the transformer. From the transformer two references links to Oracle stages( For ex: one to Client table other two client_addr). In my situation after job finished, event detail specifies

ClientMastJob..HoldCltMast: DSD.StageRun Active stage finishing.
20 rows read from FromCltMast
3 rows read from FromPRSLookup
20 rows written to ToOrclClt
0 rows written to ToOrclCltAddr

My first table updates while second (0)nothing. ETL allowed me to created multiple inputs to ORA.
Would I able to have multiple input to the same schema but target different tables? Does this tool support it? If yes, where could I made a mistake?

[ray.wurlod]

Hi,
IMHO DWHs are not something you have FK constraints on (period) and expect loading performance.
your ETL should have integrity checks for that and the DWH should be free of such things (correct me if I'm wrong people).

now if you insist on having FK constraints then do it the slow sequential way you enforce by having them.

IHTH

This is not necessarily true. Red Brick Warehouse (designed by Ralph Kimball and others) requires FK constraints on which a multi-table index (called a STAR index) can be constructed.

And, I would argue, referential integrity is absolutely vital in a star schema.

[roy]

Hi,
the summery you see seems to state no rows went that path.
so if you use CFF, 1. no rows matched that path or 2. the selection criteria is probably not correct.

did you double check that?

p.s.
yes DS alows you to to do that (in reply to your question)

IHTH

[roy]

Agreed Ray, but usually you populate the staging area first and only then you load to production, so you don't have to have FK constrait while loading to staging (should have stated that as well ), or am I wrong

[frisha]

Roy,
The data that suppose to go to different tables is on the same row, but some info should go to Client table while another to Addr table and I do not see that. Also, I will check constraint on second table. Let see.

As I understood, I would be able in the same job write to multiple tables for same schema and create FK? Is it correct?

Thanks again.

[ray.wurlod]

As I understood, I would be able in the same job write to multiple tables for same schema and create FK? Is it correct?

Basically this is correct, you can.
But best practice is to load the operational master (dimension table) first, then to load the operational detail (fact table). In this way you should be able easily to ensure that referential integrity is preserved, and it's much easier to implement type 2 slowly changing dimensions (for example) if you do these things in separate DataStage jobs.

[frisha]

Ray,

So, if I do this task in separate jobs how would I assign the ID from master table to foreign key of Detail table if I can't see the ID from Master table in the second job?
I can not load a detail table without information for foreign key (derivation).
Also, can you explain changing demantions term please.

Thank you.

[cecilia]

Usually, in the second job, the idea is that you refer to the dimension (loaded in a hash table) by code in order to get the ID and so on for every dimension.
I suggest you to read The DataWarehouse Life Cycle by Kimball in order to get a reference for the used terminology and best practices.
Regards

[ray.wurlod]

Ray,

So, if I do this task in separate jobs how would I assign the ID from master table to foreign key of Detail table if I can't see the ID from Master table in the second job?
I can not load a detail table without information for foreign key (derivation).
Also, can you explain changing demantions term please.

Thank you.

In, or prior to, the second job, you pre-load a hashed file with the mapping of the natural key to the current value of the surrogate key).
As the detail data are being loaded, you perform a reference lookup against this to obtain the value for the FK column in the detail table.

[frisha]

THANK YOU ALL.