Source Independent Load(SIL) in ETL

The Source Independent Load Mapplet is the key component of the Source Independent layer of the Oracle BI Applications. It represents fundamental business concepts such as sales orders, purchase orders, products, customers, suppliers etc. from various operational systems like Oracle EBS, PeopleSoft, Oracle’s Siebel CRM , and SAP R/3, which often vary widely in the ways each represent or define these concepts - creating challenges for an analytic solution that must integrate data from many operational sources. The Source Independent Mapplet provides a core consistency through a consolidation layer that “normalizes” these differences into a single common definition of each fundamental business concept across the entire value chain. The transformations packaged inside the Source Independent Mapplet consolidate the data from multiple source systems by enforcing commonalities and by preparing the data for source independent loading into the enterprise data warehouse.

The transformations performed by the SIL (Source Independent Load) Mapplet include:

• Lookup descriptions for data elements which are normally present as codes in the operational sources. The SIL Mapplet provides the flexibility to bypass the lookup for descriptions when they are provided in the input. To truly integrate data from multiple sources, the domain values for data elements should be standardized, and source supplied values need to be transformed to the corresponding values from the standardized set. The SIL Mapplet supports this functionality.

• The Business Analytics Data Warehouse maintains relationships between fact tables and dimension tables using surrogate keys for better query performance. The SIL Mapplet generates the surrogate keys used for maintaining dimension tables.

• The SIL Mapplet supports Type I and Type II slowly changing dimensions. The SIL Mapplet of a dimension provides a Type II flag, which can be configured to indicate a INTERNAL USE ONLY Oracle Confidential 36 Type II change depending on the data  elements for which history tracking is required. The SIL Mapplet handles the creation of a new  record in the dimension table for each Type II change and all the records are effectively dated to facilitate dimension key lookups.

• The SIL Mapplet has a dimension key lookup transformation for resolving the relationships between fact tables and dimension tables. Since the Business Analytics Data Warehouse has Type II support for slowly changing dimensions, the SIL Mapplet performs the lookup using the transaction date to preserve history.

• Currency conversions are performed by exchange rate lookups depending on the transaction date. SIL Mapplets provide complete flexibility and bypass the currency conversion logic when the amounts in all three global currency denominations are provided as input.

• The SIL Mapplet has transformations to figure out the update strategy for an input record, whether it needs to be inserted in the data warehouse, or whether it needs to be updated or handled as a Type II slowly changing dimension. For the latter case it is inserted and a corresponding record in the data warehouse is updated.

The SIL Mappings provide several key benefits:

• Data integration from every operational source uses the SIL Mapplet to load data into the Business Analytics Data Warehouse, enforcing consistency.

• Most of the transformations required to load the Business Analytics Data Warehouse are encapsulated in the SIL Mapplet, which is reused for building data integration components from every source. This results in significant savings of time and effort required to build the data integration components, and reduces overall risk.

• The SIL Mapplet provides a lot of flexibility in the transformations that it encapsulates. The behavior can be controlled by configuring the inputs according to need. The SIL’s ability to resolve integration issues and move data across different platforms makes it an invaluable resource for any data warehouse administrator.