Data Warehouse Concepts in Breif

What is DataWareHouse?

A warehouse is a place where something is stored a goods is stored. e.g. An eCommerce Website have a warehouse where they will project a demand for a product they will procureth (get/obtain) from client and store in the warehouse. As soon as the supplier places an order the good is immediately dispatched.

Taking Warehouse out of this equation, what do you get? You have the customer directly going to the eCommerce website, the eCommerce site is not storing any goods, when an order is placed the eCommerce website people will directly go to the supplier and ask for the product. 

Now if 100 supplier go to the manufacturer asking for the product becasue there is demand from the consumer, imagine the strained (adj - showing signs of nervous tension or tiredness) that is placed on the manufacturer to supply those products. That is looking from manufacturer percepective.

Now looking from customer perspective there will be delay in delivering the product which customer has ordered. Nobody would like a delay. If there is delay of 2-3 days it will affect customer satisfaction level and you will loose customer.

Similar to this is the "Data WareHouse", where you will be storing the data that you will precure from the transaction system.

There are two terms:

1. OLTP - Online Transaction Processing.

2. OLAP - Online Analytical Processing.

Analytical processing is where you use the Data Warehouse. Transaction processing is something that you will use to record each and every transaction.

Example 1 - ATM

 We all use ATMs. Every transaction we do in ATM is record in OLTP system. Besides ATM when you go to bank account and perform transaction, even that is recorded. So there are multiple sources feeding into a particular system. Now if you want to perfrom query on the system you will have to join multiple sources. Which have different formating type of their own. Second disadvantage is number of transaction - 100 of customers use an ATM in a given day, and millions of queries are being hit on OLTP system. Imagine the load going into OLTP system. So this system is definetely not used for Query purpose or Analytical purpose. This system is only used to record a transaction. 

Example 2 - Railway Reservation

There are many ways in which you can reserve a railway ticket. You can go through mobile, Directly go to railway station, railway website, go to n number of agents that are spread across the city. These are multiple desperate sources. Format of ticket booking on website is entrily different from booking it from mobile. Data Type and Kind of data is changing. Multiple deseparate sources make it difficult for quering. 

Based on this the enduser, at the end of the day would want reporting. He want data for his reporting purpose. So we create an alternate system. This is called as OLAP system.

As you can see from the diagram there are multiple sources Source 1, Source 2, Source 3 feeding into the Data WareHouse. This is not simple loading. Lots of calculating, varification is done before loading the data into the WareHouse and at the end you have multiple users trying to access this warehouse and trying to get data and generate reports.

Why do we need DataWareHouse

We all know this is an era of competition, it is not just taking smart decision but taking the decision on time.  Assume a super market chain is not implementing any datawarehouse. It is very difficult for the supermarket to analyse:

• What product are sold
• What product are not selling
• When is the time selling goes up
• What is the age of person buying a particular product.

None of these analysis is possible. What actually happen is some goods getting out some good getting in without knowing anything.

That is not an ideal scenario. I want to make a decision when I say this particular product hit among the age group 18-25. I will intensify my effort to market that particular product to that particular age group. Also from the data I see this particular product is not selling as it is expected. So I will analyse why and the reason for this. This particular product is not selling during a particular time (like Dec-Feb) or it is not selling throughout the year. Then I will be in a better situation to take the decision of either not to take the product or suggest some improvement so that the product start selling.

These are smarter decision which if you do not take on time customer will move out. 

When I talk about stretegic value  that is given to a company. Taking example of procurement department. Every company procures certain things from suppliers. There will be hundred of suppliers supplying a product to company (for example Desktop, Laptops, stationary etc). Now before making purchase the company definitely ink a contract with the supplier, saying if you are charging 100 for a particular product please give us at a discount of 80 Rupees and what are the terms that should be followed when procurement is made etc.

Now what is the guarantee that the supplier is following all the terms set in the contract. When a particular purchase is made, the suppliers provide an invoice to the company. That invoice is record of that transaction. Now I want to match this invoice data with actual contract data to see whether all the terms are being matched or not. If the invoice says product has been charged 100 instead of 80,how you will analyse if the data is not there. 100 of suppliers are providing such invoices to the company. You cannot go and sit with each and every suppliers. 

What we will do now is: We will load the data into dimensions and extract reports and see whether there is a match or mismatch. If there is a mismatch we will schedule a meeting with the supplier and ask him why he has done so. This is strategic advantage and also it gives the company more negotiating power. You can question for mismatched. But in the absence of data you end up paying more.

•  Primary reason for a Data warehouse is, for a company to get the extra edge over its competitors.
•  This extra edge can be gained by taking smarter decisions in a timely manner.
•  Smarter decision can be taken only if the executives responsible for taking such decision have data at their disposal.

There was a time when fact based decision and experienced based decision making is much more prevelant. We are moving from that area and going into an area fact based decision have gain importance in our life. So data is very essential.

Some strategic Questions that a manager has to answer:

Q. How do we increase the market share of this company by 5%?

Q. Which product is not doing well in the market?

Q. Which agent need help with selling policies? 

Example from analysing data of Warehouse you can find an agent not selling policies as per expectations. You will identify whether there is deficiency in company or Employee. Does he need more training. 

Q. What is the quality of the customer service provided and what improvement are needed?

Why is Data WareHouse so important?

What approach manager follow to arrive at final decision?

What is the quality of the customer service provided and what improvement are needed?

This is a larger question. Now he will break it down into smaller questions.

Subset Question 1: How many customer feedback we have in last 6 months? 

Now he will fire query on database which has all the customer details.

Subset Question 2: How many customer have given a feedback of Excellent, How many Average and How many Bad?

You will go to the database and by grouping you will find how many excellent, Average or Bad feedback you got.

Subset Question 3: Now I need to know why are people giving bad, Average and Excellent? What are the comments or improvement areas highlighted by customers who have rated us bad or average?

May be one person said your distribution channel is not good, may be some say support is not good, and that is the reason why they have given.  So we can identify why they have given that feedback.

All these questions combined you will have an overall picture of the quality of customer service and what improvement needed.

We will hit the data warehouse to get results of these questions, then consolidate these results and arrive at a final conclusion. In the absense of data there is no way of knowing.

One more important factor is trends. Because data ware house hold the entrire history. Jan 2015 to Aug 2014. It holds data till august 2014. With this data we can see what trend is happening:

•  A Particular product is being sold at a particular month at higher level.
•  A Particular product sale goes down at a particular month. 
•  And Does that happens at a regular interval?

We can spot that trend this history. 

When we spot that trend, because of this history and when I spot that trend, I can make out common factor and I can take a decision to why that is happening. 

An operation system do not provide trends. They are transaction system and you may purge the data in an operation system over time. You cannot maintain from starting till end. But in DataWareHouse the main purpose is to maintain history. You will not be deleting history in immediate future.

Result are provided in ready to access format where you can take the analysis.

What is ETL?

The full form of ETL is Extract Transform and Load.

Now question are: 

• What to extract? 
• From where to Extract?
• What to Transform
• How to Transform?
• Where to Load it?

As you know there are multiple desperate sources that are loading the data into the datawarehouse so you need to extract from the multiple different sources. There is no consistency in the data in the OLTP system, so you need to standardize data that is coming in and then you have to load it into the datawarehouse. 

Usually Most of the companies (Banking and Insurance sector) use Mainframe system, which are very legacy system (Very old) and very difficult for reporting. And now they are trying to move to DataWareHouse system.  

Usually in a production environment the files( or data) is extracted from the mainframe and send to a unix or windows server in a file format. Each file will have a specific file format. They can send multiple file as well depending on the requirement. 

Let's say they send the file at 3 AM in the early morning, so we process those files using an ETL tool. Some of the ETL tool are: Informatica and Data Stage. These are not open source. Talent is one open source.

We use any one of these ETL tool to cleanse the data. Cleansing is important because special character might come, emailID not in proper format etc. So the Unwanted spaces, unwanted character can to be removed, Data need to be cleansed using the ETL tools.

Then they(File, data from different sources) are loaded into an area called the Staging area. In the Staging area all the business rules are applied. For example there is a Bussiness rule saying that a particular record that is coming in always be presend in the master table record. If it is not present we will not be moving it further. So we will have to look up on the master table, if the record is present we will move it further next level else left it in Staging.

Then we load in into the Dimention table. Schedular are also available to run the job exactly at 3 AM ( or any particular time) or you can run the job when the file arrives. There can be time dependency as well as file dependency. 

At the end we need to validate whether the job has been executed successfully or the data has been loaded successfully or not.

Data WareHouse Architecture

There are multiple transactional systems Source 1, Source 2. Now these source are not only Mainframes, it can be SAP, Flat files. So there can be combination of sources. ETL tool is used to load the DataWareHouse and the DataMarts.

Difference between DataMart and DataWareHouse is: 

DataWareHouse is used across the organization and DataMart are used for individual customized reporting. For example there are multiple department in the company. Finance department is different from the marketing department. They all draw data from different sources and they need customized reporting. Finance department is mainly concern with the statistics, Marketing department is mainly concern with the promotions. The marketing department may not get the finance information. So for customized report they create subset of the datawarehouse which are called Datamarts.

There are two approaches:

1. First load the DataWareHouse and then load the DataMart.

2. Load the DataMart and then Load the DataWareHouse.

Advantage of these approaches I will be post in upcoming posts.

Repoting (Data Access Layer):

User actually accesses the DataWareHouse and generates the report. All these reporting tool are meant to make the front interface very easy for the consumer because people at decision making level usually not concerned with technical details. They are not concerned with writing queries and extracting reports. They are mainly concern with neat usable report only.

So all these reporting tool does that at the front end but at the backend they generate the queries, hit the datawarehouse and user get the report just in time. These reporting tool can also schedule the job to run and generates the report depending on a particular schedule. 

Advantages of Data WareHouse

• Standardizes data across an organization.
• Smarter decision for companies - Move toward fact based decisions 
• Reduce Cost 
• Drop products that are not doing well.
• Negotiate for improvement with suppliers
• Increase Revenue
• Work on high selling products
• Customer Satisfaction - Know what is working and what is not.

Database Normalization Concepts

Description

Database normalization is the process of organizing the attributes and tables of a relational database to minimize data redundancy.

Normalization involves re-factoring a table into smaller (and less redundant) tables but without losing information; defining foreign keys in the old table referencing the primary keys of the new ones. The objective is to isolate data so that additions, deletions, and modifications of an attribute can be made in just one table and then propagated through the rest of the database using the defined foreign keys.

Normalization can be described as:

• It is a technique for designing relational database tables to minimize duplication of information.
• Normalization is a practice to safeguard the database against logical and structural anomalies.
• Normalization is also termed as Canonical synthesis by the experts.
• It is used to keep data consistent and check that no loss of data as well as integrity is there.
• Its complexity may lead to higher degree of join operations which sometimes lead to the degraded throughput times.

Normalization Objective

1. To free the collection of relations from undesirable insertion, update and deletion dependencies;
2. To reduce the need for restructuring the collection of relations, as new types of data are introduced, and thus increase the life span of application programs;
3. To make the relational model more informative to users;
4. To make the collection of relations neutral to the query statistics, where these statistics are liable to change as time goes by.

Database Modification Anomalies

When an attempt is made to modify (update, insert into, or delete from) a table, undesired side-effects may follow. Not all tables can suffer from these side-effects; rather, the side-effects can only arise in tables that have not been sufficiently normalized. An insufficiently normalized table might have one or more of the following characteristics:

Update Anomaly:

The same information can be expressed on multiple rows; therefore updates to the table may result in logical inconsistencies. For example, each record in an "Employees' Skills" table might contain an Employee ID, Employee Address, and Skill; thus a change of address for a particular employee will potentially need to be applied to multiple records (one for each skill). If the update is not carried through successfully—if, that is, the employee's address is updated on some records but not others—then the table is left in an inconsistent state. Specifically, the table provides conflicting answers to the question of what this particular employee's address is. This phenomenon is known as an update anomaly.

An update anomaly. Employee 519 is shown as having different addresses on different records.

Insertion Anomaly:

There are circumstances in which certain facts cannot be recorded at all. For example, each record in a "Faculty and Their Courses" table might contain a Faculty ID, Faculty Name, Faculty Hire Date, and Course Code—thus we can record the details of any faculty member who teaches at least one course, but we cannot record the details of a newly hired faculty member who has not yet been assigned to teach any courses except by setting the Course Code to null. This phenomenon is known as an insertion anomaly.

 An insertion anomaly. Until the new faculty member, Dr. Newsome, is assigned to teach at least one course, his details cannot be recorded.

Deletion Anomaly:

Under certain circumstances, deletion of data representing certain facts necessitates deletion of data representing completely different facts. The "Faculty and Their Courses" table described in the previous example suffers from this type of anomaly, for if a faculty member temporarily ceases to be assigned to any courses, we must delete the last of the records on which that faculty member appears, effectively also deleting the faculty member, unless we set the Course Code to null in the record itself. This phenomenon is known as a deletion anomaly.

A deletion anomaly. All information about Dr. Giddens is lost if he temporarily ceases to be assigned to any courses.

First Normal Form (1NF)

As per First Normal Form, no two Rows of data must contain repeating group of information i.e each set of column must have a unique value, such that multiple columns cannot be used to fetch the same row. Each table should be organized into rows, and each row should have a primary key that distinguishes it as unique.

The Primary key is usually a single column, but sometimes more than one column can be combined to create a single primary key. For example consider a table which is not in First normal form

Student Table :

Student	Age	Subject
Adam	15	Biology, Maths
Alex	14	Maths
Stuart	17	Maths

In First Normal Form, any row must not have a column in which more than one value is saved, like separated with commas. Rather than that, we must separate such data into multiple rows.

Student Table following 1NF will be:

Student	Age	Subject
Adam	15	Biology
Adam	15	Maths
Alex	14	Maths
Stuart	17	Maths

Using the First Normal Form, data redundancy increases, as there will be many columns with same data in multiple rows but each row as a whole will be unique.  

Second Normal Form (2NF)

As per the Second Normal Form there must not be any partial dependency of any column on primary key. It means that for a table that has concatenated primary key, each column in the table that is not part of the primary key must depend upon the entire concatenated key for its existence. If any column depends only on one part of the concatenated key, then the table fails Second normal form.

In example of First Normal Form there are two rows for Adam, to include multiple subjects that he has opted for. While this is searchable, and follows First normal form, it is an inefficient use of space. Also in the above Table in First Normal Form, while the candidate key is {Student, Subject}, Age of Student only depends on Student column, which is incorrect as per Second Normal Form. To achieve second normal form, it would be helpful to split out the subjects into an independent table, and match them up using the student names as foreign keys

New Student Table following 2NF will be :

Student	Age
Adam	15
Alex	14
Stuart	17

In Student Table the candidate key will be Student column, because all other column i.e Age is dependent on it.

New Subject Table introduced for 2NF will be :

Student	Subject
Adam	Biology
Adam	Maths
Alex	Maths
Stuart	Maths

In Subject Table the candidate key will be {Student, Subject} column. Now, both the above tables qualifies for Second Normal Form and will never suffer from Update Anomalies. Although there are a few complex cases in which table in Second Normal Form suffers Update Anomalies, and to handle those scenarios Third Normal Form is there.

Third Normal Form (3NF)

Third Normal form applies that every non-prime attribute of table must be dependent on primary key. The transitive functional dependency should be removed from the table. The table must be in Second Normal form. For example, consider a table with following fields.

Student_Detail Table:

Student_id	Student_name	DOB	Street	city	State	Zip

In this table Student_id is Primary key, but street, city and state depends upon Zip. The dependency between zip and other fields is called transitive dependency. Hence to apply 3NF, we need to move the street, city and state to new table, with Zip as primary key.

New Student_Detail Table :

Student_id	Student_name	DOB	Zip

Address Table:

Zip	Street	city	state

The advantage of removing transtive dependency is,

• Amount of data duplication is reduced.
• Data integrity achieved. 

Boyce and Codd Normal Form (BCNF)

 Boyce and Codd Normal Form is a higher version of the Third Normal form. This form deals with certain type of anamoly that is not handled by 3NF. A 3NF table which does not have multiple overlapping candidate keys is said to be in BCNF.

Normalizing an Example Table

These steps demonstrate the process of normalizing a fictitious student table.

1. Unnormalized table: 
   
   
   Student#	Advisor	Adv-Room	Class1	Class2	Class3
   1022	Jones	412	101-07	143-01	159-02
   4123	Smith	216	201-01	211-02	214-01

2. First Normal Form: No Repeating Groups
   
   
   Tables should have only two dimensions. Since one student has several classes, these classes should be listed in a separate table. Fields Class1, Class2, and Class3 in the above records are indications of design trouble. 
   
   
   Spreadsheets often use the third dimension, but tables should not. Another way to look at this problem is with a one-to-many relationship, do not put the one side and the many side in the same table. Instead, create another table in first normal form by eliminating the repeating group (Class#), as shown below:
   
   
   Student#	Advisor	Adv-Room	Class#
   1022	Jones	412	101-07
   1022	Jones	412	143-01
   1022	Jones	412	159-02
   4123	Smith	216	201-01
   4123	Smith	216	211-02
   4123	Smith	216	214-01

3. Second Normal Form: Eliminate Redundant Data
   
   
   Note the multiple Class# values for each Student# value in the above table. Class# is not functionally dependent on Student# (primary key), so this relationship is not in second normal form.
   
   
   The following two tables demonstrate second normal form: 
   
   
   Students:
   
   
   Student#	Advisor	Adv-Room
   1022	Jones	412
   4123	Smith	216

   
   
   Registration:
   
   
   Student#	Class#
   1022	101-07
   1022	143-01
   1022	159-02
   4123	201-01
   4123	211-02
   4123	214-01

4. Third Normal Form: Eliminate Data Not Dependent On Key
   
   In the last example, Adv-Room (the advisor's office number) is functionally dependent on the Advisor attribute. The solution is to move that attribute from the Students table to the Faculty table, as shown below:
   
   Students:
   
   
   Student#	Advisor
   1022	Jones
   4123	Smith

   
   
   Faculty:
   
   
   Name	Room	Dept
   Jones	412	42
   Smith	216	42

Initialization Parameter files: PFILEs vs. SPFILEs

When an Oracle Instance is started, the characteristics of the Instance are established by parameters specified within the initialization parameter file. These initialization parameters are either stored in a PFILE or SPFILE. SPFILEs are available in Oracle 9i and above. All prior releases of Oracle are using PFILEs.

SPFILEs provide the following advantages over PFILEs:

• An SPFILE can be backed-up with RMAN (RMAN cannot backup PFILEs)
• Reduce human errors. The SPFILE is maintained by the server. Parameters are checked before changes are accepted.
• Eliminate configuration problems (no need to have a local PFILE if you want to start Oracle from a remote machine)
• Easy to find - stored in a central location

What is the difference between a PFILE and SPFILE:

A PFILE is a static, client-side text file that must be updated with a standard text editor like "notepad" or "vi". This file normally reside on the server, however, you need a local copy if you want to start Oracle from a remote machine. DBA's commonly refer to this file as the INIT.ORA file.

An SPFILE (Server Parameter File), on the other hand, is a persistent server-side binary file that can only be modified with the "ALTER SYSTEM SET" command. This means you no longer need a local copy of the pfile to start the database from a remote machine. Editing an SPFILE will corrupt it, and you will not be able to start your database anymore.

How will I know if my database is using a PFILE or SPFILE:

Execute the following query to see if your database was started with a PFILE or SPFILE:

SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" 
       FROM sys.v_$parameter WHERE name = 'spfile';

You can also use the V$SPPARAMETER view to check if you are using a PFILE or not: if the "value" column is NULL for all parameters, you are using a PFILE.

Viewing Parameters Settings:

One can view parameter values using one of the following methods (regardless if they were set via PFILE or SPFILE):

• The "SHOW PARAMETERS" command from SQL*Plus (i.e.: SHOW PARAMETERS timed_statistics)
• V$PARAMETER view - display the currently in effect parameter values
• V$PARAMETER2 view - display the currently in effect parameter values, but "List Values" are shown in multiple rows
• V$SPPARAMETER view - display the current contents of the server parameter file.

Starting a database with a PFILE or SPFILE:

Oracle searches for a suitable initialization parameter file in the following order:

• Try to use the spfile${ORACLE_SID}.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database (Windows)
• Try to use the spfile.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database (Windows)
• Try to use the init${ORACLE_SID}.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database (Windows)

One can override the default location by specifying the PFILE parameter at database startup:

SQL> STARTUP PFILE='/oradata/spfileORCL.ora'

Note that there is not an equivalent "STARTUP SPFILE=" command. One can only use the above option with SPFILE's if the PFILE you point to (in the example above), contains a single 'SPFILE=' parameter pointing to the SPFILE that should be used. Example:

SPFILE=/path/to/spfile

Changing SPFILE parameter values:

While a PFILE can be edited with any text editor, the SPFILE is a binary file. The "ALTER SYSTEM SET" and "ALTER SYSTEM RESET" commands can be used to change parameter values in an SPFILE. Look at these examples:

SQL> ALTER SYSTEM SET open_cursors=300 SCOPE=SPFILE;

SQL> ALTER SYSTEM SET timed_statistics=TRUE
 COMMENT='Changed by Frank on 1 June 2003'
 SCOPE=BOTH
  SID='*';

The SCOPE parameter can be set to SPFILE, MEMORY or BOTH:

- MEMORY: Set for the current instance only. This is the default behaviour if a PFILE was used at STARTUP.

- SPFILE: update the SPFILE, the parameter will take effect with next database startup

- BOTH: affect the current instance and persist to the SPFILE. This is the default behaviour if an SPFILE was used at STARTUP.
The COMMENT parameter (optional) specifies a user remark.

The SID parameter (optional; only used with RAC) indicates the instance for which the parameter applies (Default is *: all Instances).

Use the following syntax to set parameters that take multiple (a list of) values:

SQL> ALTER SYSTEM SET utl_file_dir='/tmp/','/oradata','/home/' SCOPE=SPFILE;

Use this syntax to set unsupported initialization parameters (obviously only when Oracle Support instructs you to set it):

SQL> ALTER SYSTEM SET "_allow_read_only_corruption"=TRUE SCOPE=SPFILE;

Execute one of the following command to remove a parameter from the SPFILE:

SQL> ALTER SYSTEM RESET timed_statistics SCOPE=SPFILE SID=‘*’;
SQL> ALTER SYSTEM SET timed_statistics = '' SCOPE=SPFILE;

Converting between PFILES and SPFILES:

One can easily migrate from a PFILE to SPFILE or vice versa. Execute the following commands from a user with SYSDBA or SYSOPER privileges:

SQL> CREATE PFILE FROM SPFILE; 
SQL> CREATE SPFILE FROM PFILE;

One can also specify a non-default location for either (or both) the PFILE and SPFILE parameters. Look at this example:

SQL> CREATE SPFILE='/oradata/spfileORCL.ora' from PFILE='/oradata/initORCL.ora';

Here is an alternative procedure for changing SPFILE parameter values using the above method:

• Export the SPFILE with: CREATE PFILE=‘pfilename’ FROM SPFILE = ‘spfilename’;
• Edit the resulting PFILE with a text editor
• Shutdown and startup the database with the PFILE option: STARTUP PFILE=filename
• Recreate the SPFILE with: CREATE SPFILE=‘spfilename’ FROM PFILE=‘pfilename’;
• On the next startup, use STARTUP without the PFILE parameter and the new SPFILE will be used.

Parameter File Backups:

RMAN (Oracle's Recovery Manager) will backup the SPFILE with the database control file if setting "CONFIGURE CONTROLFILE AUTOBACKUP" is ON (the default is OFF). PFILEs cannot be backed-up with RMAN. Look at this example:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

Use the following RMAN command to restore an SPFILE:

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

Source: http://www.orafaq.com/node/5