Re: SQL*Loader question.

"Peter Shvets" <pshvets_at_powermarket.com> wrote

> I am trying to load some data using SQL*Loader.
> Here is what I am doing.

<snipped>

You can not code SELECT statements like that in a Control file in order to generate the correct column values. What are you selecting? The data for that row has not even be loaded yet. How can you then select a column value from that line of text not yet in the database?

Also consider the reason for having SQL*Loader.. to load text-based data as fast as possible. Adding SELECT statements into the Control file to perform data scrubbing, kind of defeats the whole purpose of using SQL*Loader in the first place IMO.

In most data warehouses, you will find that the raw data is first loaded into a staging/work area. There the data is cleaned, scrubbed, fixed, checked, whatever.. the dimensions updated.. and only then is the data loaded into the fact table.

As for joining with the date dimension (or time dimension), to obtain the dimension key to use.. I never do that. Instead I make the dimension key meaningful with in context of the data loaded. For example, let's say your date dimension covers the last 5 years. This means a table containing 365x5 rows (plus leap days). Each row containing columns such as a public holiday flag, a display date, the week day's name, etc. etc.

Instead of making the key for that dimension table a sequence, I use the format YYYYDDD as the numeric key for the date dimension. This means that in order to obtain the applicable dimension key for the date, you no longer need to join the raw fact data with the date dimension when updating the fact table. Instead, you simply DECODE the date in the fact row to a numeric YYYYDD value. Saves you from doing another join when populating the fact table. And this still stays within the design principles of a star schema.

--
Billy