Getting Started with SSIS Productivity Pack: Premium Slowly Changing Dimension

Getting Started with SSIS Productivity Pack: Premium Slowly Changing Dimension


Thank you for watching this video from KingswaySoft. Today I will be introducing the Premium Slowly Changing Dimension component from within the SSIS Productivity Pack. The SSIS Productivity Pack is a collection of premium SSIS components which enable greater developer productivity and increases the power of SSIS. The Premium Slowly Changing Dimension can be used to monitor changes in datawarehouse dimensions. These dimensions are relatively static and can change infrequently and unpredictably. This component can process three types of slowly changing dimensions: • Fixed Attributes, which are never supposed to change. • Changing Attributes, which can change but don’t have a history; and • Historical Attributes, which can change and a history of all previous values is kept. This component is more advanced when compared to the SSIS out-of-box Slowly Changing Dimension component. It has much greater usability. It also supports any data sources instead of just the OLEDB connections. I will quickly configure my two input sources. Let’s now drag the Premium Slowly Changing Dimension component from the SSIS Toolbox to the design surface. We will specify the first source as the primary input, aka our “Current Dimension Input” with data that should be monitored for changes. The secondary input will be our “New Source Input” with potentially changed data from an external system. Double-click it to open its Editor form. The first page involves the ‘Fields To Compare’ grid, which displays all the available fields from the Current Dimension source. Each of these fields can be paired to a field in the New Source Input. For every field that has been paired, it is possible to designate these fields as a business key. If more than one business key is selected, a compound business key is created. The compound business key is used to determine which rows to compare in the Current Dimension Input and New Source Input. The possible column types are: FixedAttribute, ChangingAttribute, Historical Attribute and Surrogate Key. The FixedAttribute option specifies attributes that are not supposed to change. If the component detects a value change in this field, it will report an error which can either fail the component or redirect to an error output depending on the error handling mechanism chosen later on. The ChangingAttribute option specifies attributes that are supposed to change. If this attribute changes, it will be directed to the change rows output. If it does not change and no other fields have been changed either, it will be directed to the unchanged rows output. The Historical Attribute option specifies attributes that are supposed to change and a history to be kept. If this attribute changes, a changed row will be generated and sent to the Changed Rows output to indicate this is an expired historical row, and a new row will be added and directed to the New Rows output to represent the new current row. The Surrogate Key option has a numeric value that is unique in the Current Dimension input. Having a surrogate key can be useful when performing inserts and updates later in the workflow. The component can automatically increment this value in new rows that it creates. We have a couple more options such as Current Record, Start Date and End Date. We also have a Refresh Component button, which will reload input columns from the current dimension input and new source input. Let’s navigate to the next page for Change Type Settings. This page only matters if there is at least a Changing Attribute and also a Historical Attribute. If you have set one of the mapped dimensions on the previous page as “Changing Attribute”, the changing attribute settings option will be enabled. When changes are detected in a changing attribute, this option dictates whether the component should change all the matching records, including historical records, or just the only record that is marked as Current in the dimension input. If this option is checked, all records from Current Dimension input with a matching business key in New Source input, including those historical ones, will be updated with the new dimension value. If this option is not checked, the component will attempt to identify the current record and only update that record. Next, under Historical Attribute Settings, we must specify the column in the Current Dimension input that contains information about which record is current. Then we can specify the values of the record that indicates whether it is current or expired. You can choose a variable to set data values, this is used to get the current date for setting start and end date values in new and updated rows. The End Data Value for Current Records option, specifies the value to use when setting the end date for current records. There are three options: Null Value, 12/31/9999 or a custom date of your choice. A calendar will appear on the right if custom date is selected. Let’s move to the Advanced Settings page. Culture Identifier is defaulted to the user’s Windows regional setting. This will be used when comparing string values. By enabling the next field, you can choose to ignore leading and trailing white space of string fields during the comparison. Similarly, you can also choose to ignore case as necessary. The next section of this page is the Surrogate Key Settings, with two additional options. You can specify the start value to use for the surrogate key in the first new row. You can also specify the incremental interval value for the surrogate key value as each row is processed. When the “ChangeType” checkbox is checked, the type of change (including Fixed, Updated, or New) will be included in an additional output column for each output. The last page is the Error Handling page, where there are 3 error-handling mechanisms to choose from. The default option is ‘fail on error’, where the entire dataflow will fail as soon as an error occurs. There is also the redirect rows to error output, where the error output will contain the failed records with extra columns, ErrorCode, ErrorColumn and ErrorMessage. There is also a third option, which is to ignore any errors that may have occurred. We can click OK to finish configuring the component. I will quickly connect this component to some Data Readers for the purpose of showing the outputs we mentioned earlier. There are three outputs to show you the results of the comparison: Changed Rows, New Rows and Unchanged Rows. There is also an Error Output if any error has occurred. We can now execute this data flow task. This concludes the demonstration of the Premium Slowly Changing Dimension component within our SSIS Productivity Pack. There are many other components in the SSIS Productivity Pack that enable developers to accomplish more in SSIS in a much more productive fashion. Please feel free to take a look at our other videos available for viewing on our website or YouTube channel. Thank you for watching this video. For any additional questions, please feel free to reach out to us.

Author:

Leave a Reply

Your email address will not be published. Required fields are marked *