Handling Early Arriving Facts

In a live working environment, it is possible that transactional data may contain values that have not yet been added to the source database in the corresponding dimension table. An example of this could be a Sales Invoice that has a Salesperson Code where the Salesperson Code does not yet exist in the Salesperson table. When the data warehouse is updated and the cubes are processed, the values for this salesperson will fall under the “Unknown” member for the Salesperson dimension. This happens because the cube does not see the Salesperson Code on the transaction as being a known value when compared to the list of salespeople in the Salesperson dimension.

In TimeXtender,it is possible to handle these “early arriving facts” in such a manner that they will show at least partial information until the data source is properly updated with all of the normal dimension information. This prevents information from being placed into the “Unknown” member when the data is consumed by end-users. Once the dimension value is properly added to the ERP system or data source by a user, all fields for the previously missing record will then be populated according to the values in the data source.

Enabling Early Arriving Facts

1. Identify the dimension table to which relevant values from the transaction table should be added, right-click the table name, and go to Advanced -> Add Related Records.
   
   The Add Related Records window opens.
   
2. In Name, type a descriptive name for the Add Related Records rule that is currently being created.
3. In the Create Records from Table list, select the transaction table that will identify the table from which to bring in potential new values. A window may appear stating that all mappings and conditions will be cleared. Click Yes.
   
4. In the Record Condition list, select the option to determine when data will be inserted into the dimension table if new values are found in the transaction table. The most common option is Not Exist, which will add in values that do not currently exist in the dimension table.
5. Select the Data Destination Table to insert the values into. The default option is the Raw table.
6. In the Field Mapping table, specify the fields to be mapped from the transaction table and inserted into the dimension table. In the example below, the DW_Account field (Company) and Salesperson Code fields will be extracted from the transaction table and inserted into the dimension table.
   
7. It is possible to add in fixed values for fields in the dimension table that the transaction may not have data for. In the example below, the fixed value “Missing Salesperson” will be added in the Name field for all Salesperson Codes added from the transaction table. This is achieved by selecting the Fixed Value option in the Mapping column for the Name field and typing the desired fixed value in the Fixed Value column.
   
8. If desired, a default value can be inserted instead of bringing in the values that exist in the transaction table. This could be used to assign fixed values to all data brought in for early arriving facts. This is achieved by clicking the checkbox in the Allow Default Value column and typing the corresponding fixed value in the Default Value column. This is not common.
9. The last step is to define the relationship between the two tables. Click the Add button in the Conditions section.
10. Select the first field to join in the dimension table (Code), and click OK.
    
11. Select the operator to be used for the join. The most common operator is Equal.
12. Select the matching field in the transaction table (Salesperson Code), and click OK.
13. Repeat steps 9 through 12 for any additional joins that need to be made (such as Company). The final result will look similar to this:
    
    Click OK when finished to save the settings, and close the Add Related Records window.
    
    A folder for Table Transformations will be added to the bottom of the dimension table. The selection criteria that were previously set can be edited by right-clicking the transformation and selecting Edit Related Record.
    
14. Deploy and execute the dimension table. Any records that exist in the transaction table, but not in the dimension table, will be added during the data cleansing process. A screen-shot of the result based on the example in this document is shown below.
    
    
    The salesperson code “BP” existed on a sales document, but no corresponding Salesperson Code existed in the Salesperson table. Once the salesperson is properly added to the ERP system and the table is refreshed, all proper information will be pulled in from the ERP system, and the name will no longer say “Missing Salesperson.”