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.
Enabling Early Arriving Facts
1. Identify the dimension table to which relevant values from the transaction table should be added, right click on the table name, and go to Advanced → Add Related Records .
window will open.
2. Give a descriptive name in the Name field to the Add Related Records rule that is currently being created.
3. 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 .
4. In the
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
which will add in values that do not currently exist in the dimension
5. Select the
to insert the values into. The default option is the Raw
6. In the
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.
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
field for all Salesperson Codes
added from the transaction table. This is achieved by selecting the
option in the
column for the
field and entering the desired
fixed value in the
8. 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
column. This is not common.
9. The last step is to define the relationship between the
two tables. Click the
10. Select the first field to join in the dimension table (
) and click
11. Select the operator to be used for the join. The most
common operator is
12. Select the matching field in the transaction table (
) and click
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 the screenshot below.
when finished to save the
settings and close the
A folder for
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
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 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.”