Handle Early Arriving Facts
Overview
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 the Jet Data Manager 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 (in this example Salesperson 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.
Enable Early Arriving Facts
-
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 will open.
Give a descriptive name in the Name field to the Add Related Records rule that is currently being created.
-
Select the transaction table in the Create Records from Table dropdown that will identify the table from which to bring in potential new values that don’t currently exist in the dimension table. A window may appear stating that all mappings and conditions will be cleared. Click Yes.
In the Record Condition dropdown, 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.
Select the Data Destination Table to insert the values into. The default option is the Raw table.
-
In the Field Mapping section, select 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.
-
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 entering the desired fixed value in the Fixed Value column.
-
If desired, a default value can be inserted instead mapped fields 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.
The last step is to define the relationship between the two tables. C lick the Add button in the Conditions section.
-
Select the first field to join in the dimension table ( Code ) and click OK.
Select the operator to be used for the join. The most common operator is Equal.
-
Select the matching field in the transaction table ( Salesperson Code ) and click OK.
-
Repeat steps 9 through 12 for any additional joins that need to be made (such as Company).
The final result will look similar to the screenshot below. 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.
-
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 screenshot 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, then all proper information will be pulled in from the ERP system and the name will no longer say “Missing Salesperson.”