Property | Value |
---|---|
Target Table | Name of the table in the target schema (data warehouse) into which data is loaded |
Table Type | Fact or Dimension |
SCD Type | Type 1 - Existing records are updated directly. The nature of the dimension depends on how it handles changes made in the source system. Type 2 - When changes occur, a new effective row is inserted. The existing records are deactivated by entering the current date as the effective end date and new records are inserted with an effective start date as today’s date. The new record will have the end date as a high date. |
Fact Type | Whether this is a snapshot or accumulation fact table Snapshot - Captures a snapshot view of the data as available in the source system during that period (monthly or weekly). Each snapshot's data is stored independently. Accumulation - Data from the source system is accumulated periodically. Changes from source system will be merged with the existing data. Multiple copies of the same data will not be maintained. |
Source System Driver Table | Name of the table in source database on which the data is primarily based |
Source System Download Table | Name of the download table populated by the source batch program. Note that this property is applicable only for the snapshot facts for which special batch programs have been designed in the source system for data extraction. |
Stage Table | Name of the table in the staging schema (data warehouse) that can be used to query the data records generated by the ETL logic |
Oracle Data Integrator Package | Name of the ODI package that needs to be executed to transform data from the replication layer and populate a specific target table in the data warehouse |
ETL View | Resides in the replication layer and fetches the base values from the replication tables as a starting point for further transformations to be done by the Oracle Data Integrator interfaces |
Materialized View | Names of the materialized views delivered with the product for a specific fact table These materialized views are designed to support all of the OAS answers delivered with the product. Refreshing these materialized views will be taken care by separate ETL jobs. |
Source Batch Program | The batch program that needs to be executed in the MDM system to extract the data into download tables. This is available only for the MDM snapshot facts. |
Property | Value |
---|---|
Target Field | Name of the column in the fact or dimension table present in the data warehouse. The extracted/transformed data is loaded here. |
Description | Description of the target field |
Source Field | Name of the field from the source application used to load the target field either directly or after transformation |
Transformation Logic | Specifies the details of how the data from the source field is transformed before being populated into the target field. |
Category | Details |
---|---|
Surrogate Key | The primary key on the fact/dimension table maintained within the data warehouse. |
Natural Keys | The set of columns from the source system define the granularity of the fact or dimension table. The natural key columns from the source along with certain other data load attributes can be used to uniquely identify a record in the table. The additional columns needed for the identification depend on the type of the table as listed below: • SCD Type 1 Dimension - Data Source Indicator • SCD Type 2 Dimension - Effective Start Date, Effective End Date, Data Source Indicator • Accumulation Fact - Data Source Indicator • Snapshot Fact - Snapshot Date, Data Source Indicator |
Attributes | Hold descriptive data from the source system and are typically available in dimension tables. |
Measures | Hold numerical value based data calculated from the source system data and are typically available on fact tables. |
Degenerate Dimensions | Hold descriptive data from the source system and are typically available in fact tables. These cannot be grouped into any of the available dimensions. |
Foreign Keys | Foreign key references on the fact tables to the dimension tables. |
User Defined Attributes | Additional fields available to customers for extending the star schemas. These fields will not be populated out of the box. Customers need to customize the ETL and populate these fields. |
Data Load Attributes | Hold the date/time information, job number details, etc, related to the ETL processes, that are needed for audit purposes. |