Maintaining dimension tables in a data mart is quite a chore. We have talked to customers who spend a great deal of time writing SQL scripts, stored procedures, or other code to perform this function. Often, the code is written or duplicated for each dimension table and must be modified regularly to accommodate changing business requirements. Typically, this custom code performs poorly for all but small tables. I am sure that this brings a great deal of excitement to their working day.
Well, I guess they will have to find that excitement elsewhere because with Centerprise 5, we are taking all that fun out of dimension table maintenance. Centerprise’s Slowly Changing Dimension Write Strategy automates this function and eliminates all that work. All you need to do is to set up your SCD structure using a simple UI. SCD Strategy supports Type 1 and Type 2 slowly changing dimensions and provides support for multiple row versioning strategies including effective/expiration dates, current row flag, and version number. The following screenshot shows an example of SCD definition.
As you can see, all you have to do is specify the role that each field plays in the dimension table. These roles can be surrogate key, business key, SCD1 value, SCD2 value, effective/expiration dates, active value field, etc. That’s it. Once the roles are defined, you can use Centerprise’s preview feature to see exactly how the incoming data would be processed by the SCD strategy. Here is a preview of the dimension table update in the above screenshot.
Centerprise’s SCD Strategy is powered by a high-speed, parallel engine that compares incoming data against the data in the table and, based on the differences between the values, performs an SCD1 update, an SCD2 update/insert, a simple update, or skips the row if no material changes were found. The engine is designed to efficiently process even the largest of dimension tables.
Here is a simple dataflow
I encourage you to try this firsthand using the trial version of Centerprise and you will see the ease and speed of this component. Please contact us at sales@astera.com or 1-888-77-ASTERA begin_of_the_skype_highlighting 1-888-77-ASTERA end_of_the_skype_highlighting for a trial download.