Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

ETL and type 2 slowly changing dimension

ETL and type 2 slowly changing dimension

ETL and type 2 slowly changing dimension


I am in the process of setting up a datawarehouse for a distributed measurement system.

The measurement probes periodically report measurement data (facts) which is automatically ETL'ed into the datawarehouse.

One of the dimension tables is a type 2 SCD with the probe configuration. The probe configuration itself happens via a user interface and is stored in a dedicated database (a system independant from the datawarehouse).

I am unsure on the best approach to follow to keep the type 2 SCD configuration table up to date and I am looking for advice on this topic.

The options I am considering are:

1) Changes in the dedicated configuration system trigger a mechanism to create additional entries in the dim table of the datawarehouse. The keys in the config system and in the dim table of the datawarehouse are aligned.

2) Changes in the configuration system are stored in the probes and reported as part of the measurement data. The ETL does a lookup for the particular configuration in the dim table, and adds a new entry "on-the-fly" whenever a match cannot be found.
Keys for the configurations in the dim table are not aligned with the config system, and potentially even depend on the order in which measurement data from the different probes was reported or ETL'ed.

3) The measurement data ETL process queries the config system and updates (appends records to) the config scd dim table prior to loading the measurement data. Keys in the config system and dim table of the datawarehouse can be aligned.

Any comment, advice or suggestion on this topic will be welcome.

Thank you.

RE: ETL and type 2 slowly changing dimension

Wow.  Good question.  Regarding option 1, this will insure that you capture all configurations, whether they have facts or not.  The other options do not have this capability. Is this capability important? Could it become important in the future? If not, then this option should be discarded as it collects data which is not needed which will waste space and processing time.  

Regarding the other options, option 2 seems more "Transaction-Like" and could perhaps significantly delay the load window.  Load processes usually like "Bulk" operations, and option 3 seems more "Bulk-Like" than option 2.  

The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close