Subscribe to our ezine for more Oracle tips and tricks - just go to www.asktheoracle.net/oracle-tips-signup.html
This article discusses Oracle 9i Business Intelligence enhancements - what's been added to or changed in Oracle 9i to make running business intelligence/decision support applications (i.e. using Oracle as a data warehouse) even easier.
Oracle 9i improves on the features embedded within previous versions of the database to provide a scalable, high-performance platform with embedded support for data warehousing and business intelligence requirements included new extraction, transformation and loading (ETL) features, extended support for on-line analytical processing (OLAP) and improvements to data mining features.
For more information about the new features see the resources section which has book reviews, suggestions for further reading and links to information on OTN and other places.
Smartsoft also offer high quality, instructor-led Oracle 9i training courses and SQL courses for developers and dbas. Furthermore all our courses can be tailored to your exact needs to provide an even bigger return on your investment. See our Oracle courses section for more details.
New subscribers can access the previous Oracle 9i training classes here:
http://www.smart-soft.co.uk/Oracle/oracle9i-new-features-part1.htm
http://www.smart-soft.co.uk/Oracle/oracle9i-new-features-part2.htm
http://www.smart-soft.co.uk/Oracle/oracle-9i-backup-and-recovery.htm
http://www.smart-soft.co.uk/Oracle/oracle-9i-security-enhancements.htm
http://www.smart-soft.co.uk/Oracle/oracle-9i-manageability-enhancements.htm
http://www.smart-soft.co.uk/Oracle/oracle-9i-scalability-enhancements.htm
For information on other releases and other aspects of Oracle databases,
just go here:
http://www.smart-soft.co.uk/Oracle/oracle-tips-and-tricks.htm
This issue is only going to provide training on the Oracle 9i ETL enhancements. Notes on the OLAP and data mining enhancements will be added later.
Oracle 9i has been designed to be a complete solution for data warehouses and thus for business intelligence and decision support systems. The main ETL enhancements include:
Let's look at these in a little more detail. Refer to the Oracle 9i Data Warehousing Guide for a full description of these new features.
Before Oracle 9i, the tools available for loading data in to the database consisted of SQL*Loader, whatever custom tools you designed and built, and possibly SQL*Plus if the data was coming from another Oracle database. The new feature of external tables provides an alternative to the use of SQL*Loader, although this feature could be regarded as just an extension of SQL*Loader. Let me explain.
SQL*Loader provides a mechanism for defining the structure of flat files (whether the fields are character or numeric, how long they are, etc.) and loading them into the specified table(s). To load the data, the database obviously has to read the external file and chop up each record as specified by the control file.
External tables build on this by enabling flat files to be treated as if they were read-only Oracle tables. This enables them to be queried using SQL, PL/SQL or any other supported language. As the tables are stored outside the database, dml operations (insert, update and delete) are not supported, nor are indexes or constraints, but joins to other tables (internal or external) are supported including joins to IOTs or hash tables.
They are defined to the database by the use of the create table command with the organization external clause. The first part of the create table command is as normal but after that the structure and location of the flat file is defined in much the same way as it would be for SQL*Loader.
Let's look at a simple example, assume we have employee data consisting of employee number, name, address, and start date, with the fields being separate by a comma. The create table statement would be as follows:
CREATE TABLE extern_emp
(id NUMBER,
emp_name VARCHAR2(60),
emp_addr VARCHAR2(80),
start_date DATE )
ORGANIZATION EXTERNAL
(
TYPE oracle_loader
DEFAULT DIRECTORY load_data
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
(id ,
emp_name ,
emp_addr ,
start_date CHAR date_format date_mask "dd-mon-yyyy")
)
LOCATION('emp.dat'))
Table functions provide an alternative to intermediate tables during the transformation of source data prior to loading into the data warehouse. They take a set of data either in the form of a ref cursor or a collection (nested table or varray) and transform the data as required and returning a collection. Table functions are invoked from within the from clause. They can be pipelined - to return each row of data as it is processed rather than wait until all input has been processed - and paralleled. Implementation can be in PL/SQL, Java or C.
With the introduction of multi-table insert statements, Oracle 9i avoids the need for multiple insert...select statements. Each insert...select statement can now specify multiple destination tables by having multiple into clauses, thereby avoiding the need for the source table to be read more than once. Inserts can also be made conditional by the of the when clause.
Let's look at a few examples, just to give you an idea of how you can use them.
INSERT ALL
INTO sales_history VALUES (trans_date, prod_id, inv_amt)
INTO prod_history VALUES (trans_date, id, inv_amt)
SELECT sale_date,product_code,sale_value
FROM sales;
INSERT ALL
WHEN prod_id > 20
INTO sales_history
VALUES (trans_date, prod_id, inv_amt)
WHEN id IN (SELECT id FROM prod WHERE prod_desc LIKE '%GOLD%')
INTO
prod_history VALUES (trans_date, id, inv_amt)
SELECT sale_date,product_code,sale_value
FROM sales;
INSERT FIRST
WHEN prod_id > 20
INTO prod_30_sales_hist
VALUES (trans_date, prod_id, inv_amt)
WHEN prod_id > 30
INTO prod_40_sales_hist
VALUES (trans_date, prod_id, inv_amt)
WHEN prod_id >
40
INTO prod_50_sales_hist
VALUES (trans_date, prod_id, inv_amt)
ELSE
INTO prod_history
VALUES (trans_date, id, inv_amt)
SELECT sale_date,product_code,sale_value FROM sales;
The new Oracle 9i merge statement enables you to conditionally insert a new row or update an existing row in the destination table without having to use PL/SQL. All the hard work is done by database itself, you just have to specify the source and destination tables.
Example:
MERGE INTO customers old
USING cust_changes new
ON (new.cust_id=old.cust_id)
WHEN MATCHED THEN
UPDATE SET old.cust_name=new.cust_name, old.cust_addr=new.cust_addr;
WHEN NOT MATCHED THEN
INSERT (cust_id,cust_name,cust_addr)
VALUES (new.cust_id,new.cust_name,new.cust_addr);
The change-data capture feature in Oracle 9i is an extension of log miner and is used to track DML changes, storing the changes in a change table. These changes can also be propagated thorough a publish and subscribe mechanism. Refer to the Oracle 9i Data Warehousing Guide for further details.
Oracle have made many changes with regards to data warehousing and business intelligence for Oracle 9i. In this article we have only looked superficially at the enhancements to data extraction, transformation and loading to make these operations easier and more efficient.
Details about the changes made to OLAP and data mining will be published in future articles, but if you can't wait, why not attend one of our Oracle 9i training courses?
Contact us directly with your questions/feedback or to enquire about training and consultancy options and check out the resources page for links to other useful resources.
---------------------------------------
Looking for more Oracle tips and tricks? If you're looking for no frills, no fluff, just solid, reliable technical information, take a short cut now and subscribe to our ezine. Published monthly, it's jam-packed full of tips and tricks to help you make more of your Oracle systems and save you hours of blood, sweat and tears searching for information. Subscribe today and your first issue will soon be winging its way to your mailbox.
Send us your questions and we'll answer them in the next newsletter.
Smartsoft Computing Ltd
Bristol, England
Tel: 0845 0031320
Contact Us
Click here to view our privacy policy .
This site uses hitslink.com to gather statistical information about our visitors. View hitslink privacy policy .
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. UNIX is a registered trademark of The Open Group in the United States and other countries.
© Copyright Smartsoft Computing Ltd 2001-2008. All rights reserved.