Do you guys know about Source Dependent extract and Source Independent loading mappings?
I know they are informatica mappings used for specific requirements.
SDE loads data to stage area, whereas bulk load is used,
SIL loads data from srage area to target area, whereas normal load is used.
Have you guys ever worked with these types of mapping? What are the key transformations used in these mappings?
SDE_* - Workflow loads data into the staging table.Staging table will
not have any index defined on them and always the load into the staging
table will be a truncate and load.Since there are no index defined "Bulk"
load mode is being used.
SIL_* - You can find two SIL_* workflows in the Workflow Manager.During
a full load the workflow (SIL_*_Full) will be invoked from DAC and the
target table is truncate ,indexes are dropped ,data is loaded in 'Bulk' mode
and then the indexes are rebuild.Durring incremental load , the workflow
(SIL_*) will be invoked which would just insert/update the incremental
data.Since the amount of data during incremental load is minimal compared to
full load this would run in 'Normal' mode.If indexes are present on a table
"BULK" mode does not work.
SDE
During ETL, the preconfigured Informatica workflows/mappings, identified as the SDE (Source Dependent Extract) mappings, extract data from source OLTP database tables by querying views created by DAC with names starting with V_* and load the data into staging tables on the SRMW database. The view definition will change depending on whether the ETL run is a full load or incremental load run. In case of a full load, it would be a “SELECT * FROM <base table>â€. While in the case of an incremental load, the view definition joins the base table with the Image tables. This is done to minimize the impact and duration of the ETL process on the OLTP database.
DAC drops and creates these views during each run (unless specified explicitly in one of the System properties called ‘Drop Create Views Always’). These views can be dropped any time and DAC will create them when necessary.
SIL
Query data from the staging tables, perform final transformations, and load the dimension and fact tables. Because these processesare independent of the original OLTP database, they are used for data loading from any sources when the staging tables have been populated.
"Are both types of mapping predefined? Or we have to create mappings in informatica, if so, what are the key transformations?"
All of these mappings are pre-built as part of the Oracle BI Apps. The BI Apps provides a complete data warehouse data model and the ETL routines to load this data model plus the logical content in OBI EE to query and present this data to users. The only reason you would create your own mappings is to map to sources that are not already supported by the BI Apps to load into the existing data warehouse tables or to load into brand new data warehouse tables that you may build to meet gaps in the BI Apps.
I know they are informatica mappings used for specific requirements.
SDE loads data to stage area, whereas bulk load is used,
SIL loads data from srage area to target area, whereas normal load is used.
Have you guys ever worked with these types of mapping? What are the key transformations used in these mappings?
SDE_* - Workflow loads data into the staging table.Staging table will
not have any index defined on them and always the load into the staging
table will be a truncate and load.Since there are no index defined "Bulk"
load mode is being used.
SIL_* - You can find two SIL_* workflows in the Workflow Manager.During
a full load the workflow (SIL_*_Full) will be invoked from DAC and the
target table is truncate ,indexes are dropped ,data is loaded in 'Bulk' mode
and then the indexes are rebuild.Durring incremental load , the workflow
(SIL_*) will be invoked which would just insert/update the incremental
data.Since the amount of data during incremental load is minimal compared to
full load this would run in 'Normal' mode.If indexes are present on a table
"BULK" mode does not work.
SDE
During ETL, the preconfigured Informatica workflows/mappings, identified as the SDE (Source Dependent Extract) mappings, extract data from source OLTP database tables by querying views created by DAC with names starting with V_* and load the data into staging tables on the SRMW database. The view definition will change depending on whether the ETL run is a full load or incremental load run. In case of a full load, it would be a “SELECT * FROM <base table>â€. While in the case of an incremental load, the view definition joins the base table with the Image tables. This is done to minimize the impact and duration of the ETL process on the OLTP database.
DAC drops and creates these views during each run (unless specified explicitly in one of the System properties called ‘Drop Create Views Always’). These views can be dropped any time and DAC will create them when necessary.
SIL
Query data from the staging tables, perform final transformations, and load the dimension and fact tables. Because these processesare independent of the original OLTP database, they are used for data loading from any sources when the staging tables have been populated.
"Are both types of mapping predefined? Or we have to create mappings in informatica, if so, what are the key transformations?"
All of these mappings are pre-built as part of the Oracle BI Apps. The BI Apps provides a complete data warehouse data model and the ETL routines to load this data model plus the logical content in OBI EE to query and present this data to users. The only reason you would create your own mappings is to map to sources that are not already supported by the BI Apps to load into the existing data warehouse tables or to load into brand new data warehouse tables that you may build to meet gaps in the BI Apps.
As I am new to OBIA this information helped me. thanks for the post
ReplyDelete