Business Intelligence and Data Warehousing.

Business Intelligence and Data Warehousing :

Business Intelligence is a process and methodology to convert raw data in to meaningful information for business use.

     Business Intelligence Tools can be Categorized Into :

• ETL Tools :

For Extracting Transforming and Loading the data to Data warehouse.

• Reporting Tools :

Read data, process the data and format the data in to structured reports

and dashboards those are delivered to business users.

• Data Mining Tools :

Data mining is commonly used for marketing, surveillance, and fraud detection.

• Knowledge Management Tools :

          Knowledge management tools are used capture, develop, share, and effectively                                          using organizational knowledge

              

What is Data Warehousing ?

A data warehouse is a collection of corporate information, derived directly from operational systems and some external data sources. Its specific purpose is to support business decisions, not business operations.

Characteristics of a Data Warehouse:

• Subject-oriented Data

 •        Collects all data  for a subject,  from different sources

•       Read-only Requests

•        Loaded during off-hours, read-only during day hours

• Interactive Features, Ad-hoc query.

•       Flexible design to handle spontaneous user queries.

•       Pre-aggregated data

•       To improve runtime performance

•  Highly demoralized data structures

Data Mart :

         It’s a smallest level or Subset of the data warehouse and is specific to a business.

          Example : Sales Data Mart, Orders Data Mart, Finance Data Mart.

Staging Area  :

•  Is an Intermediate storage area between the source and data warehouse.
• It’s an area where the cleansing of  raw data takes place
• It might be UNIX storage mount or relation staging table based on the requirement.

Sources :

It might any OLTP relational or file sources:

   Example : ERP, Mainframe, Oracle, Teradata , DB2.

Available BI Tools In market ?

ETL Tools 

• Informatica
• Abintio
• Data Stage
• Talend 

Reporting Tools.

• Cognos
• Business Objects (BOXI)
• OBIEE
• MicroStrategy

Data Bases and Operating Systems used for DW Design

Popular Relational Databases.

• Oracle

• IBM DB2

• Teradata

• Sybase

• MySQL

• Greenplum

• VectorWise

Popular OS Platforms.

• Red Hat Linux

• Solaris Unix

• Window 

Difference Between OLTP and OLAP

	OLTP	OLAP
1	OLTP is for Business process or Business Operations.	OLAP is for Business Decisions and Analytics
2	OLTP involves short and fast inserts and updates initiated by end users.	Periodic long-running batch jobs refresh the data
3	Highly normalized with many tables	De-normalized with fewer tables
4	Clerical Users	Managerial/Business Users
5	Detailed Oriented Data	Subject Oriented Data
6	Entity Attribute Relationship Model	Dimensional Modeling
7	Contains Instant Data	Contains Historical Data

 Types of OLAP

ROLAP :

·         Relational Online Analytical Processing.

·         ROLAP data will be stored in Standard Relational Database as a result it doesn’t need any pre computation.

·         ROLAP is scalable to handle large volumes of data.

                Ex : Business Object       

               

MOLAP :

·         Multi Dimensional Online Analytical Processing.         

·         MOLAP is alternative to ROLAP

·         Store pre-computed data in an optimized multidimensional array storage we call it as  cube

·         Query performance will be very high due to the optimized storage.

                 Ex : Cognos.

HOLAP :

·         Hybrid Online Analytical Processing.

·         It’s a combination of ROLAP and MOLAP.

·         Increased Aggregation and Query performance.

                Ex : Micro strategy.