Hello,
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.
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.
