Extending a Dimension Table in the Multidimensional Warehouse Data Model
You can extend the functionality of a dimension table by introducing new attributes to it. To load a new attribute into a dimension table, you must extract a new field from either a source table or a new lookup table. This new field can then go through any required transformation before loading to the dimension table.
The following steps are required to extend a dimension table:
Define the new attribute desired and determine whether there are any corresponding related or outrigger language requirements for that attribute.
Identify the source of the new content and its corresponding error table.
Assess the impact to the granularity with respect to the existing dimension table being considered for extension.
Design the table structure modifications required and apply them to the database.
Update the dimension job to include the new attribute.
Note: This step is discussed in more detail below.
Update corresponding related language or outrigger language jobs, as necessary.
Updating a Dimension Job with a New Attribute That Originates from a Source Table
Perform the following steps to update a dimension job with an attribute that originates from a source table:
In IBM WebSphere DataStage Designer, locate the appropriate dimension job and open it for editing.
Open the DRS stage and select the Output tab.
Image: DRS Stage with Columns sub-tab selected
This example illustrates the DRS Stage with Columns sub-tab selected.
In the Columns sub-tab, add a new row for the new attribute.
Input the appropriate values for the derivation, data type, data size, and other applicable properties of the new attribute.
Repeat steps two through four for the IPC stage but provide information for the Input tab as well as the Output tab.
Once the new attribute is defined in the IPC stage, it becomes available on the Transformer Stage - Input Links window.
In the Transformer Stage - Input Links window, apply any transformation logic, such as any string or number functions, as necessary.
The logic is defined in the derivations field of the output link for the target table.
Connect the output link of the transformer stage to the target dimension table.
Open the IPC stage and select the Inputs tab.
Image: IPC_SRC Stage
This example illustrates the IPC_SRC Stage.
In the Columns sub-tab ensure that the new attribute column is present and properly defined.
Open the target DRS stage and select the Input tab.
In the Columns sub-tab ensure that the new attribute column is present and properly defined.
Image: Target DRS stage with new attribute row
This example illustrates the Target DRS stage with new attribute row.
Select File, Save from the menu to save the job.
Select File, Compile from the menu to compile the job.
If your mapping is correct, the Compilation Status window displays the Job successfully compiled with no errors message. If your mapping is incorrect, the Compilation Status window displays an error message.
If your job successfully compiles, select Close.
If you job does not compile successfully, you must return to the job and troubleshoot the errors.
You should perform technical unit testing and regression testing on the server job to ensure that the new attribute is populated properly.
Updating a Dimension Job with a New Attribute That Originates from a Lookup Table
Perform the following steps to update a dimension job with an attribute that originates from a lookup table:
In IBM WebSphere DataStage Designer, locate the appropriate dimension job and open it for editing.
Create a new DRS lookup stage and open it for editing.
Image: New lookup table stages
This example illustrates the New lookup table stages.
Select the main Stage tab and input the appropriate values for database type, connection name, user ID and user password in the General sub-tab.
Image: DRS Lookup with Stage tab and General sub-tab selected
This example illustrates the DRS Lookup with Stage tab and General sub-tab selected.
Select the Output tab and then the General sub-tab.
Image: DRS Lookup with Output tab and General sub-tab selected
This example illustrates the DRS Lookup with Output tab and General sub-tab selected.
Input the appropriate values for the table names, transaction isolation, array size, and query type.
The query type can be user defined or generated by SQL.
Select the Columns sub-tab and add a new row for the new attribute.
Image: DRS Lookup with Output tab and Columns sub-tab selected
This example illustrates the DRS Lookup with Output tab and Columns sub-tab selected.
Input the appropriate values for the derivation, key, SQL type, length, scale, and other applicable properties of the new attribute.
The key fields must be marked appropriately as they are used to extract the value for the new attribute.
Select the Selection sub-tab and input any selection criteria for the attribute.
Image: DRS Lookup with Output tab and Selection sub-tab selected
This example illustrates the DRS Lookup with Output tab and Selection sub-tab selected.
Select the SQL sub-tab and input any user-defined query for the attribute in the User-Defined tab.
Image: DRS Lookup with user defined SQL
This example illustrates the DRS Lookup with user defined SQL.
Connect an output link from the new DRS lookup stage to the applicable hash file stage.
Image: Attribute to target mapping
This example illustrates the Attribute to target mapping.
Open the aforementioned hash file stage for editing and select the Inputs tab.
On the Inputs tab, input the appropriate file name and description, and select the options that are applicable to the attribute.
Select the Columns sub-tab and add a new row for the new attribute.
Input the appropriate values for the key, SQL type, length, scale, and other applicable properties of the new attribute.
Note: The column definitions must match those defined on the Output tab in the DRS lookup stage.
The hash file name must match the name specified in the Inputs and Outputs tabs. The hash file will provide erroneous values if the column definitions and hash file names are synchronized between the aforementioned tabs in the hash file stage.
Connect the output link of the hash file stage to the transformer stage (Trans_Gen_Key).
Once the link is connected to the transformer stage, the new lookup table becomes available in the inputs pane of the transformer stage.
In the inputs pane of the transformer stage, define the key expression for each key field.
The value of the key expressions is sourced from the main input link (ipc_src_in) of the transformer stage. Parameters and constant values can also be used to match with the key fields of the lookup table.
Image: IPC Stage with column definitions
This example illustrates the IPC Stage with column definitions.
Apply transformation logic, such as any string or number functions to the new attribute from the lookup table, as necessary.
The logic is defined in the derivations field of the output link for the target table.
Connect the output link of the transformer stage to the target dimension table.
Open the IPC stage and select the Inputs tab.
In the Columns sub-tab ensure that the new attribute column is present and properly defined.
Image: Target DRS stage with new attribute row
This example illustrates the Target DRS stage with new attribute row.
Open the target DRS stage and select the Input tab.
In the Columns sub-tab ensure that the new attribute column is present and properly defined.
Select File, Save from the menu to save the job.
Select File, Compile from the menu to compile the job.
If your mapping is correct, the Compilation Status window displays the Job successfully compiled with no errors message. If your mapping is incorrect, the Compilation Status window displays an error message.
If your job successfully compiles, select Close.
If you job does not compile successfully, you must return to the job and troubleshoot the errors.
You should perform technical unit testing and regression testing on the server job to ensure that the new attribute is populated properly.