Fact Patterns
The following figure illustrates the stages of processing in a fact and the components utilized in developing a fact load process.
The data load processes comprise of a package and one or more mappings. The package uses the following mandatory variables as input:
• B1_JOB_ID: Passes the current job identifier.
• B1_DEF_MISSING_KEY: Passes the -99th key value for late arriving dimension.
• B1_DEF_NULL_KEY: Passes the 0th key value for non-existing dimension value.
The first mapping is usually a view where filters are applied based on the variables to exclude data that does not fall into the specified range. This reduces the data processed in one execution.
The data from the view is first inserted into a staging table. The staging table includes the following:
• Source natural key columns.
• Columns mapped to target or used for filters.
• Columns marked for user extension (these are UDDGEN, UDM and UDD_KEY columns).
• Columns required for looking up the foreign keys to dimensions.
• Job identifier to segregate data from multiple parallel executions of a data load process.
The UDX table (refer to the UDX Processing section for more details about UDX tables) is created only if the CM procedure has been configured for the entity. This table includes the following:
• Source natural key columns.
• Columns marked for user extension (these are UDDGEN, UDM and UDD_KEY columns)
• Columns required for looking up the foreign keys to dimensions.
• Job identifier to segregate data from multiple parallel executions of a data load process.
An additional step in the fact processing is the foreign key lookup for dimensions. There are three types of dimensions:
• Base dimensions are populated out of the box.
• User-Defined Dimensions (UDDs) are additional dimensions for which a template table is provided in the out-of-the-box product. Refer to the Using Custom User-Defined Dimensions (UDD) section for information about user-defined dimensions.
• Unknown dimensions are the objects where tables are not provided and custom dimensions have to be created. There is a built-in lookup so that custom UDD lookups do not require any code change.
The data is finally loaded into the target dimension.