ETL – BI / DW Insider

Rather than just being a platform or development environment, Oracle Business Intelligence (BI) Analytical Applications are fully inclusive business intelligence solutions that incorporate all of the key metrics, workflows, and business processes for a particular business function. Bundled within theses solutions are numerous pre-built components including:: • Dashboards; • Metrics; • Reports; • Drill-down paths; • Dimensional models; • Naming standards; • Database objects; • ETL routines; • Metadata; • Security

In addition, Oracle BI Analytical Applications contain universal adapters that allow for rapid integration and direct connections with leading commercial-off-the shelf (COTS) packages including SAP, Oracle E-Business Suite, PeopleSoft, and Siebel applications systems.

Oracle BI Analytical Applications come bundled with best practices and industry standards built-in. Additionally, they include all of the functionality required to conduct business intelligence for many common business functions including financials, human resources, sales, service, contact centers, marketing, supply chains, order management and fulfillment business areas.

Fundamentally, Oracle BI Analytical Applications are built upon the Oracle BI Platform and provide complete end-to-end, prebuilt business intelligence solutions that deliver intuitive, role-based intelligence to all members of an organization including senior executives, mid-level managers, and front-line employees. So rather than developing custom business intelligence solutions for each business area and function, the use of Oracle BI Analytical Applications allows an organization the ability to rapidly configure a ready-built solution utilizing the complete Oracle BI Platform.

Oracle BI Analytical Applications come bundled with two main additional pre-built back-end repositories:: • Business Analytics Warehouse; • ETL (Extract-Transform-Load) Repository

The Business Analytics Warehouse (BAW) is a completely pre-built data warehouse that physically contains all of necessary dimension and fact table needed for the business intelligence applications. The BAW is fully-compliant with the dimensional modeling methodology developed by Ralph Kimball and supports many advanced techniques including slowly changing dimensions, conformed dimensions, aggregate tables, hierarchy tables, and surrogate keys.

The ETL repository includes all of the routines for extracting of data to a staging area, transforming the data into a common format, the loading of date into data warehouse tables, changed data capture, and seeding data for common dimensions. In addition, the powerful ETL repository consist of two main components, Informatica which is the ETL engine that contains the data integration routines, and the DAC (Data Warehouse Application Console) which is the “ETL orchestration tool” that controls application configuration, execution & recovery, and monitoring.

Steps within ETL Processing
The term ETL which stands for extraction, transformation, & loading is a batch or scheduled data integration processes that includes extracting data from their operational or external data sources, transforming the data into an appropriate format, and loading the data into a data warehouse repository. ETL enables physical movement of data from source to target data repository. The first step, extraction, is to collect or grab data from from its source(s). The second step, transformation, is to convert, reformat, cleanse data into format that can be used be the target database. Finally the last step, loading, is import the transformed data into a target database, data warehouse, or a data mart.

Step 1 – Extraction
The extraction step of an ETL process involves connecting to the source systems, and both selecting and collecting the necessary data needed for analytical processing within the data warehouse or data mart. Usually data is consolidated from numerous, disparate source systems that may store the date in a different format. Thus the extraction process must convert the data into a format suitable for transformation processing. The complexity of the extraction process may vary and it depends on the type and amount of source data.

Step 2 – Transformation
The transformation step of an ETL process involves execution of a series of rules or functions to the extracted data to convert it to standard format. It includes validation of records and their rejection if they are not acceptable. The amount of manipulation needed for transformation process depends on the data. Good data sources will require little transformation, whereas others may require one or more transformation techniques to to meet the business and technical requirements of the target database or the data warehouse. The most common processes used for transformation are conversion, clearing the duplicates, standardizing, filtering, sorting, translating and looking up or verifying if the data sources are inconsistent.

Step 3 – Loading
The load is the last step of ETL process involves importing extracted and transformed data into a target database or data warehouse. Some load processes physically insert each record as a new row into the table of the target warehouse utilizing a SQL insert statement. Whereas other load processes include a massive bulk insert of data utilizing a bulk load routine. The SQL insert is a slower routine for imports of data, but does allow for integrity checking with every record. The bulk load routine may be faster for loads of large amounts of data, but does not allow for integrity check upon load of each individual record.

Posts

Oracle Business Intelligence (BI) Analytical Applications

Three Steps in ETL Processing