Loading Data into Oracle

Load any data into Oracle in just a few clicks. Plus there is no need to create any scripts or learn command line tools just point and click.

Learn how you can do it now:

• How to load data from an Excel Spreadsheet into Oracle Database with Visual Importer ETL

There are several ways of loading data into Oracle database

Conventional path load Direct path load

Using Oracle ODBC Driver Using Ole DB

Conventional path load

A conventional path load executes SQL INSERT statements to populate tables in an Oracle database.

When Advanced ETL Processor or Visual Importer performs a conventional path load, it competes equally with all other processes for buffer resources, which can significantly slow the load.

Let's consider loading data into customers table using conventional path loading.

Some of the data is already inserted and there are duplicates in our source data.  

insert into customers
(customer_id,customer_name)
values
(:customer_id,:customer_name)

 What would happen if primary key is violated? 

If we run all our inserts within one transaction we won't be able to load any data

• lots of inserts
• commit

or we can insert one record at the time committing after every insert 

• insert one record
• commit
• insert one record
• commit
• ETC

This approach allows us to load as much as we can, however there is a problem with that it is slow because it involves a lot of OCI calls and consumes a lot of server resources.

We use slightly different approach at DB Software Laboratory.

We build an array of records in memory than insert entire array in one go.

• insert 100 records
• commit
• insert 100 records
• commit
• insert 100 records
• commit

if insert fails OCI lets us know that record number 52 within the array violates primary key.

• our insert is rolled back
• insert records from 1 till 51
• commit
• insert records from 53 till 100
• commit

If second insert failed we attempt to insert records one by one

• our insert is rolled back
• insert record 53
• commit
• insert record 54
• commit
• ...
• Insert record 100
• commit

Once we inserted 100 records one by one, we build array and try to insert entire array again

Direct path load

A direct path load eliminates much of the database overhead by formatting Oracle data blocks and writing the data blocks directly to the database files. It does not compete with other users for database resources, so it can usually load data at near disk speed.

There are several restrictions to using direct path load

During a direct path load

• CHECK constraints,
• Referential constraints (FOREIGN KEYS),
• Insert triggers

are automatically disabled

and

• NOT NULL
• UNIQUE
• PRIMARY KEY (unique-constraints on not-null columns)

enabled 

Note:

ODBC

(Pronounced as separate letters) Short for Open DataBase Connectivity, a standard database access method developed by the SQL Access group in 1992. The goal of ODBC is to make it possible to access any data from any application, regardless of which database management system (DBMS) is handling the data. ODBC manages this by inserting a middle layer, called a database driver , between an application and the DBMS. The purpose of this layer is to translate the application's data queries into commands that the DBMS understands. For this to work, both the application and the DBMS must be ODBC-compliant -- that is, the application must be capable of issuing ODBC commands and the DBMS must be capable of responding to them

The current Oracle ODBC driver conforms to the ODBC 3.52 specifications and certified against MDAC 2.8 (Microsoft Data Access Components) on Windows 32bit and 64bit platforms. It supports all core APIs and a subset of Level 1 and Level 2 functions. Microsoft supplies the Driver Manager components for the Windows Platform. The Oracle ODBC Driver is also available as part of the Oracle Instant Client installation and can be found at
http://www.oracle.com/technology/tech/oci/instantclient.

All our ETL Tools provide full support ODBC including ODBC connection strings.
In case of loading data into Oracle it works slower than Conventional path load and Direct path load.

OLE DB or ADO

OLE DB (or Object Linking and Embedding, Database) is an API designed by Microsoft for accessing data from a variety of sources in a uniform manner. It is a set of interfaces implemented using the Component Object Model (COM); it is otherwise unrelated to OLE. It was designed as a higher-level replacement for, and successor to, ODBC, extending its feature set to support a wider variety of non-relational databases, such as object databases and spreadsheets that do not necessarily implement SQL.

OLE DB separates the data store from the application that needs access to it through a set of abstractions that include the datasource, session, command and rowsets. This was done because different applications need access to different types and sources of data and do not necessarily want to know how to access functionality with technology-specific methods. OLE DB is conceptually divided into consumers and providers. The consumers are the applications that need access to the data, and the provider is the software component that implements the interface and therefore provides the data to the consumer. OLE DB is part of the Microsoft Data Access Components (MDAC) stack. MDAC is a group of Microsoft technologies that interact together as a framework that allows programmers a uniform and comprehensive way of developing applications for accessing almost any data store. OLE DB providers can be created to access such simple data stores as a text file and spreadsheet, through to such complex databases as Oracle, MS SQL Server and Sybase ASE. It can also provide access to hierarchical datastores such as email systems.

However, because different data store technologies can have different capabilities, OLE DB providers may not implement every possible interface available to OLE DB. The capabilities that are available are implemented through the use of COM objects - an OLE DB provider will map the data store technologies functionality to a particular COM interface. Microsoft describes the availability of an interface as "provider-specific," as it may not be applicable depending on the database technology involved. Note also that providers may augment the capabilities of a data store - these capabilities are known as services in Microsoft parlance.

All our ETL Tools fully support OLE DB. However it is one of the slowest ways of accessing the data and when working with large datasets it tends to use a lot of memory.

More Information:

•  
  • How to load data from an Excel Spreadsheet into Oracle Database with Visual Importer ETL
  • Advanced ETL Processor
  • Visual Importer

Loading data into the Datawarehouse

This article describes the various stages of loading data into the data warehouse

There are several stages in the process:

Preparation for Extraction

Data can come in various formats. It can be extracted from the source database directly or it may be loaded from the files. When we extract data directly all we need to is to check if connection is working.
This usually done automatically by ETL automation tool. In case of files, we need to obtain the files first. They might be stored on the remote ftp server or somewhere in the web. Those files have to be copied to the location where the can be accessed by the ETL tool.

Some ETL Tools offer complete automation of business processes including full support for file operations

Extraction.

SQL can be used to extract the data from the database or text parser to extract the data from fixed width or delimited files.
Extracting data often involves the transfer of large amounts of data from source operational systems. Such operations can impose significant processing loads on the databases involved and should be performed during a period of relatively low system load or overnight.

Cleansing and Transforming data.

Data cleansing is a process of checking data against predefined set of rules

For example:

• Checking date formats
• Checking field length
• Pattern validation
• Data type checks

A lot of data transformations can be performed during the process of extracting data from the source systems. However, there are often some additional tasks to execute before loading the data into the data warehouse. For example, reconciling inconsistent data from heterogeneous data sources after extraction and completing other formatting and cleansing tasks and generating surrogate keys.

Some typical data transformations include:

• Joining multiple fields into one field (Address 1 + Address 2 + Address 3).
• Generating calendar tables.
• Mapping data from one representation to another, such as Female to 1 and Male to 0
• Transforming data from multiple representations to a single representation, such as a common format for telephone numbers
• Creating surrogate keys

Loading the Data

Once all the data has been cleansed and transformed into a structure consistent with the data warehouse requirements, data is ready for loading into the data warehouse.

The initial load of the data warehouse consists of populating the tables in the data warehouse schema and then checking that the data is ready for use.

After the data has been loaded into the data warehouse database, verify the referential integrity between dimension and fact tables to ensure that all records relate to appropriate records in other tables. DBA should verify that every record in a fact table relates to a record in each dimension table that will be used with that fact table.

Data integrity in the reverse order is not necessary, however in some cases it might be necessary to remove unrelated data. For example when a dimension table has several times more records than the fact table

Dealing with null and empty strings.

Most queries that retrieve data from the data warehouse use inner joins between the fact and dimension tables. If you use a dimension table containing data that does not apply to all facts, you must include a record in the dimension table that can be used to relate to the remaining fact table values. For example, for null value 0 can be used as surrogate key of dimension table and for empty string 1.

To verify referential integrity in a star schema simple SQL query can be used to count the rows returned when all appropriate dimension tables are joined to the fact table using inner joins. The number of rows returned by this query should match the number of rows in the fact table. With large data warehouses it might have some performance implications and should be executed outside of normal working hours

More Information:

• Advanced ETL Processor
• Visual Importer

Star Schema Optimisation

This article describes the ways to optimize star schemas and making your data warehouse run faster

This article is not database specific

Basic Star Schema of a retail shop

In the previous article we have described the ways of calculating SQL query execution time.

Let's think about what can be done about Query performance.

We have only two options

• Improve hardware
• Make the database server do less work

Improving Datawarehouse hardware

There are a lot of ways of improving hardware. You can add memory processors increase your hardrive size etc. But quite often it does not help

We have seen some configurations with 64 gigs of memory and 32 processors

With all 32 processors doing nothing but the database is running very slow.

All this because the DBA decided to use raid 5 for better protection.
The performance of you disks this the most important part of the data warehouse.

It defines it. It tells how long do you have before the system becomes unusable due to pour performance

HDtach is very useful utility for checking disk performance, you would be very surprised when compare the laptop with the servers. Modern laptops quite often much faster.

The bottom line is:

• HDD performance is the key 
• Do not use RAID 5 use RAID 10

Making the database server do less.

Let's go back to the Star Schema Diagram

STATE CODE field is 5 bytes long
Everybody knows that the US state abbreviation is always two characters long

if we change our definition from nvarchar(2) to nchar(2) field size changes from 5 bytes to 4

Next well know thing, that there are no international characters in US state abbreviation. There is no need to use Unicode and we can change field type from nchar(2) to char(2)

The field length is 2 bytes now.

Everybody knows that there are only 52 states in US

What we can do next, is to move STATE CODE out of the fact table in to the dimension and use STATE_ID BYTE in fact table as state identifier.

By using common sense we were able to reduce the field size five times. The same technic can be applied to the rest of fields.

Conclusion

Make sure your disk fast and fact table record size as small as possible