Informatica SDE and SIL mapping

Source Dependent extract(SDE) and Source Independent loading mappings(SIL)? 
you 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. 

Very Basic of OBIA

ANSWERS

1) What are the Oracle Business Intelligence Applications (BI Apps)?
It is a complete, end-to-end BI environment covering the BI platform and pre-packaged analytic applications.

The platform includes a server and end user tools such as dashboards, query and analysis, enterprise reporting, disconnected access to the data -- all supported by a unified, model-centric server architecture.

On top of this platform, we have a set of analytic applications consuming the operational data sources and delivering greater insight to larger user communities across the organization via dashboards, query and analysis, and alerts.


2) What technology components do I need to buy to run the BI Apps?
- A database to host the Datawarehouse
- Oracle BI Suite EE Plus or Oracle BI Suite SE1 (OBIEE)
- ETL component options to OBIEE, either Oracle Data Integrator* or Informatica
- Application content per application

*Oracle Data Integrator (ODI) is currently only available for certain release against a specific release of a source system. Future plans are to have ODI fully available.


3) What Application content is available for the BI Apps?

ERP Analytics  - Oracle Financial Analytics - Oracle Procurement and Spend Analytics - Oracle Human Resources Analytics - Oracle Supply Chain and Order Management Analytics - Oracle Spend Classification Analytics - Oracle Project Analytics CRM Analytics - Oracle Sales Analytics - Oracle Service Analytics - Oracle Marketing Analytics - Oracle Contact Center Analytics - Oracle Loyalty Analytics - Oracle Price Analytics Industry Applications - Oracle US Federal Financial Analytics 4) Which systems can BI Apps source its data from? Oracle provides pre-built adapters for Oracle eBusiness Suite, Peoplesoft, JD Edwards, SAP and Siebel. In additional to these pre-built adapters Oracle provides the Universal adapters that enable any other source system to be a source for the Datawarehouse. 5) I have customised my source system; can I extend the BI Apps for these customisations? Oracle has designed the BI Apps to be extendable, from the source system mappings, the warehouse model, the BI Modelling layer to the Presentation objects. Oracle provides a methodology for extensions in the supplied guides. One of the most common extensions is the inclusion of descriptive flexfields for eBusiness suite sources. 6) How do I deploy the BI Apps? The BI Apps can be deployed as follows - Standalone mode - Integrated with Portal via JSR-168/WSRP - Embedded with the transaction system within a dashboard view - Embedded with the transaction system contextually within a transactional view 7) Can I have the same security as my transactional system? The BI Apps can be integrated with the source system to enable a smooth transition for users maintaining the user’s security context as they move from the transactional system to BI Apps and back into the transactional system. 8) Does a BI Apps implementation take as long as a traditional Datawarehouse implementation? A traditional Datawarehouse implementation will typically take in the region of 18 months + to deliver, because much of the time consuming effort has already been developed. A BI Apps project will usually have release cycles every 100 days or so. A minimal configuration implementation can have a development system up and running in a few weeks. 9) What is the DAC? DAC stands for Data Warehouse Application Console. It is an essential component of Oracle BI application architecture. DAC serves the following purposes: - DAC is a metadata driven administration and deployment tool for ETL and data warehouse objects - It is used by warehouse developers and the ETL Administrator - Manages Application Configuration - Manages the execution of warehouse loads - Provides a monitoring facility 10) Where can I get some more information about the content of the BI Applications? The following links on the Oracle support site are a good starting point: Content Guide: https://support.oracle.com/oip/faces/secure/km/DocumentDisplay.jspx?id=832690.1 Oracle® Business Intelligence Applications Metrics Reference Guide Release 7.9.6 https://metalink3.oracle.com/od/faces/secure/km/DocumentDisplay.jspx?id=846917.1 Oracle® Business Intelligence Applications ETL Data Lineage Guide Release 7.9.6,7.9.6.1 (Doc ID 829385.1) https://support.oracle.com/oip/faces/secure/km/DocumentDisplay.jspx?id=829385.1

11) How is BI Apps made aware of my source system configurations?
During the configuration of the BI Apps configurations are entered as parameters in the DAC and by completing CSV files that define your source system configs. These CSVs are then picked up during the ETL run and used to populate the warehouse.


12) Does my warehouse have to be an Oracle database?
No, the BI Apps are certified against many databases including DB2 and Terradata.


13) Can I view my data indifferent currencies?
The BI Apps hold both the transactional and local currency amounts. In addition there is the possibility to have up to 3 reporting currencies with basic configuration. The data model stores five currency versions in each fact as follows:

- Group Amount (x3) : Corporate Currency Amount
- Local Amount : Local Country / Region Currency Amount
- Document Amount : Actual Transaction Currency Amount

Support for different exchange rate types through ETL configuration

- Corporate
- User


14) I am a multi-country organisation and would like to have the ability to display the BI Apps in the local language, is this possible?
Yes, there is the facility to have the OBIEE components displayed in multi-language and the BI Apps can be configured to display the objects in multi-language. To display the data in multi-language is a customisation effort that will require extending the warehouse to include translation tables.