ETL in Datawarehousing

ETL phase of datawarehousing deals with extracting data from source and loading it to datawarehouse, after transforming it to a standard required format.

ETL process can be carried out by many means. Simplest of which is PL/SQL procedures. Simplest and the earliest of all are ETL process is PL/SQL procedures. But with time new advanced tools for ETL process have been developed. Few tools for ETL are:

Oracle Warehouse Builder (OWB) Oracle
Data Services SAP Business Objects
IBM Information Server (Datastage) IBM
PowerCenter Informatica Informatica
DataFlow Manager Pitney Bowes Business Insight
Transformation Manager ETL Solutions Ltd.
Pentaho Data Integration Pentaho

Out of these Informatica is a powerful ETL tool, recently very popular because of its features.
To know more about Informatica tool visit the blog again.

Indexes in data warehouse:

A telephone directory without an index. Can you imagine, what will happen in such a situation. It will take hours to search a
name in the directory. This is a very simple example of importance and use of index. Similarly an index on a table with millions of data
is helpful in reducing the search time.

An index is a database object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and
provides direct, fast access to rows.

A normal index in Oracle database is created using B- Tree(balanced tree) structure. While there can be different types of indexes that
can be created in Oracle, the one's that are mainly used in data warehousing are normal and bitmap indexes.

Now before going forward, let us get a clear idea of how an index works in Oracle.

In Oracle, everything accessed is in terms of block.

What is a block?

Oracle stores data in blocks or logical blocks. One data block corresponds to a specific number of bytes of physical database space
on disk.It is the smallest unit of disk that oracle reads. As rows in a table are very small, so many rows can fit into
a block in oracle.

The size of a block in Oracle can be configured but generally is of size 4kb, 8kb,16kb, 32kb.

What is a B-Tree structure?

A B Tree or a balanced tree is a database tree structure, in which each node can contain multiple childs.
If d is the depth of the tree, then usually the number of childs vary between d and 2d. If a node starts filling
then it is split to balance nodes.

In Oracle, a B - Tree contains database blocks as nodes in the tree.

How an index works?

For example, in a telephone directory you want to search for a name James. You will move to index and see that
page 5 starts with Jack and page 10 starts with Jaqueline. So James would be some where in between page 5 and
page 10.

Now on page 5, you have another sub index containing entries for names between Jack and Japper which are in pages 5,6,7,8,9.
From this index you get that page 8 starts with Jame and ends with Jamy. So now you know that James would be in this
page only. Now you can easily search for james in this page.

Similarly in a b tree index, each child blocks gives us next block to search for a particular row in a table. And finally
leaf node or block gives us the required row.

Eg.

In main index,
Page 5 starts with Jack.
Page 10 starts with Jaqueline.

In Page 5 sub index,
Page 5 starts with Jack and page 9 starts with Japper.
Page 8 starts with jame and ends with Jamy.

So Page 8 is the target page.

Surrogate keys

Surrogate key is a unique identifier used to uniquely identify records in a table. Surrogate keys are generally used in data warehousing.

Then what is the difference between surrogate key and a primary key?

Well, it depends on the type of database model being used. For a current database, where only current data are kept, there will be one to one correspondence between surrogate key and primary key.

But in a temporal database, where there are lots of data with same primary key value, as historical data are also saved.

In a temporal database, it is necessary to distinguish between the surrogate key and the primary key. Typically, every row would have both a primary key and a surrogate key. The primary key identifies the unique row in the database, the surrogate key identifies the unique entity in the modeled world; these two keys are not the same. For example, record containing a customer location details when he lived in US and now when he lives in UK. Both will have same customer_id which is the primary key. But in a temporal database where historical database are stored, surrogate key will be required to uniquely identify record between these two records. For such kind of situations, surrogate keys are required.

Example:

A customer table containing customer details.

Customer_id	Customer_name	Customer_address	Customer_city	Phone_number	Email_id
1002	John Smith	10, Wall Street	New York	1123301111	john.smith@abc.com
1002	John Smith	12, Grand Street	London	1122331111	john.smith@abc.com

As you see here, we cannot identify difference between the two records. As the customer_id are same. But as first record was input 10 years before and now the customer has moved to a new location. So, new record also contain the record. Such situation can be dealt using surrogate keys.

Customer_key	Customer_id	Customer_name	Customer_address	Customer_city	Phone_number	Email_id
1	1002	John Smith	10, Wall Street	New York	1123301111	john.smith@abc.com
2	1002	John Smith	12, Grand Street	London	1122331111	john.smith@abc.com

Now you can see that surrogate keys are unique even if there is temporal data in a database.

A surrogate key are generally generated using database sequence objects.

Advantages of a surrogate key:

Immutability

Surrogate keys do not change while the row exists. This has the following advantages.

Requirement changes:

Attributes that uniquely identify an entity might change, which might invalidate the suitability of the natural, compound keys.

Performance

Surrogate keys tend to be a compact data type such as a four-byte integer. This allows the database to query the single key column faster than it could multiple columns.

Modelling a STAR schema from ER model

An ER model is a two dimensional data model used in transactional databases. These models does not provide features required in a data warehouse. For this purpose star schema model is used. We can design a star schema model from a ER data model.

Few points that needs to be considered for converting an ER model to a star schema are as fallows:

• In an ER model, generally a FACT table is considered a table in ER model where there is maximum cardinality for all the relationships viz many in one to many or many to many relations.


• Dimensions are the tables with cardinality of 0 or 1.

A star schema can also be modeled from scratch using top down approach. Following are the steps to be followed for modeling a STAR schema:

• Identify the business process for analysis.


• Identify measures or facts.


• Identify dimensions for facts.


• Determine lowest grain i,e lowest level of cardinality for fact table.

In the next post we will see the concept of surrogate keys.

Business Intelligence and reporting:

Business intelligence is used for reporting purpose, to show the performance of any business using different dimensions like time, region, product etc.
Reporting requires data to be presented in a manner so that decision making can be done on that. For this purpose concept of data warehousing has been introduced.

The data warehousing concept was intended to provide an architectural model for the flow of data from operational systems to decision support environments. The process of gathering, cleaning and integrating data from various sources, usually from long-term existing operational systems to a target data warehouse is called data warehousing.

Design model for Data warehouse:
Generally two models are followed for storing data in data warehouse.
• Normalized approach – In this approach of storing data, the dimensional tables are normalized usually in 3NF. This approach is called snowflaking.
The advantage of this schema :
• Loading data from OLTP(source database) is easy .
Disadvantages of this schema :
• join data from different sources into meaningful information and then
• access the information without a precise understanding of the sources of data and of the data structure of the data warehouse.

• De normalized or Dimensional approach : In this approach the data are divided into dimensions and facts. The fact table is used to store measures to be analyzed while dimension tables are used to store the context on which facts in fact table are analyzed.

Advantages of dimensional approach :
• Complex query processing can be done efficiently.
• The schema can closely depict the business model.
• It incorporates the concept of slowly changing dimensions.

Difference between dimensional model and relational model:
• An E-R diagram (used in OLTP or transactional system) has highly normalized model (Even at a logical level), whereas dimensional model aggregates most of the attributes and hierarchies of a dimension into a single entity.
• An E-R diagram is a complex maze of hundreds of entities linked with each other, whereas the Dimensional model has logical grouped set of star-schemas.
• The E-R diagram is split as per the entities. A dimension model is split as per the dimensions and facts.
• In an E-R diagram all attributes for an entity including textual as well as numeric, belong to the entity table. Whereas a 'dimension' entity in dimension model has mostly the textual attributes, and the 'fact' entity has mostly numeric attributes.
Dimensional approach of data modeling leads to a design approach called OLAP (Online Analytical Processing). The core of OLAP system is an OLAP cube. It consists of numeric facts called measures which are categorized by dimensions. The cube metadata is typically created from a star schema or snowflake schema of tables in a relational database.
Data warehousing uses the OLAP design approach for data modeling. An OLAP uses a technique of OLAP cube for modeling and building a decision support system.

To understand an OLAP cube, lets take an example.
A company has got a project to warehouse all the sales data of different soft drinks company. The data will be used to create report of sales according to region, time and product of sale.
Here the data from analytical system will be extracted and stored in a data warehouse. The data warehouse will be designed using an OLAP cube as shown below.





Here, there are three dimensions on which our data warehouse reporting will be done. Time, Geography and Product.
If we have to find out the sales of a product class in a particular year for a particular region, we will move to that product class on product axis. Then move to that particular year on time axis and then for a particular geography, move in direction of geography axis. This will lead us to a cube that will give the value of sales we require. This multidimensional approach of warehouse design will help us drill to sales related data for any product class, for any year, month, week based in any geography.
This OLAP cube now has to be implemented on any data storage system. An OLAP designed on a relational database system is called as a ROLAP (Relational OLAP).
For a ROLAP system the data modeling schema followed to implement this multidimensional structure is STAR schema.

In te next post we will see how to model a star schema from a ER model schema..

Business Intelligence:

Business Intelligence refers to computer based technique, used in identifying and analyzing business data to get an insight into the trends of business health. The sales summary, market summary etc. of any business by region, by time period, by product, by product class etc.
Business intelligence aims to support business decision making by creating processes using transformation of business data in a form that helps in decision making.
Often transforming of business data is done using the concept of data warehousing.
PS: Remember that all Data warehouse is used in business intelligence, but all business intelligence does not require data warehousing.
In simple terms “Business Intelligence is a set of methodologies, processes, architectures, and technologies that transform raw data into meaningful and useful information used to enable more effective strategic, tactical, and operational insights and decision-making”.
Common uses of business intelligence are reporting, data mining, process mining, business performance intelligence, predictive analysis.

In the next post I will describe about business intelligence and its use in reporting.