An Introduction to MSBI & DWH

Presented By

Quontra Solutions Email Contact Website

: info@quontrasolutions.com : 404-900-9988 : www.quontrasolutions.com

DataBase (DB) – A place where the collection of records will be maintained in a structured format so that It can be easily retrieved when ever required is known as a database .

One of the most popularly used database model is the relational model. It was developed by Edgar Codd in 1969.

Example : How do you think the Organizations store their employee and customer information? they store it in a database. where do you think the website maintains the login information about their users? they store it in a database.

ERP– ERP, which is an abbreviation for Enterprise Resource Planning, is principally an integration of business management practices and modern technology.

ERP is a business tool that management uses to operate the business day-in and day-out.

OLTP– OLTP, which is an abbreviation for Online Transaction processing, handle real time transactions which inherently have some special requirements. If your running a Bank, for instance, you need to ensure that as people withdrawing money from ATM’S they are properly and efficiently updating the database also those transactions are properly effecting to their Accounts.

Data, Data everywhere yet ...

• I can’t find the data I need – data is scattered over the network

• I can’t get the data I need •

need an expert to get the data

• I can’t understand the data I found available • I•can’t usedata the poorly data Idocumented found • results are unexpected • data needs to be transformed from one form to other 6

What are the users saying...

• Data should be integrated across the enterprise • Summary data has a real value to the organization • Historical data holds the key to understanding data over time • What-if capabilities are required

7

In What way I can Answer the above question with my OLTP system...

Is Data Warehousing is the Solution ?? YES

Can I Improve my business using Data warehousing ??

YES.. How ??

8

Data warehouse helps any Business in Many Ways Let’s say A producer wants to know‌.

Which Whichare areour our lowest/highest lowest/highestmargin margin customers customers??

What Whatisisthe themost most effective effectivedistribution distribution channel? channel?

What Whatproduct productpromprom-otions -otionshave havethe thebiggest biggest impact impacton onrevenue? revenue?

Who Whoare aremy mycustomers customers and andwhat whatproducts products are arethey theybuying? buying?

Which Whichcustomers customers are aremost mostlikely likelyto togo go to tothe thecompetition competition??

What Whatimpact impactwill will new newproducts/services products/services have haveon onrevenue revenue and andmargins? margins?

9

DWH – (Data Warehousing) It usually contains historical data derived from transaction data, but it can include data from other sources. It separates analysis workload from transaction workload and enables an organization to consolidate data from several sources.

Raugh kimball – In simplest terms Data Warehouse can be defined as collection of Data marts.

-Data marts : Subjective collection of Data.

Bill Inmon – A data warehouse is a “subject-oriented, integrated, time variant and nonvolatile” collection of data in support of management’s decision-making process.”

OLAP – (Online Analytical Processing) The ability to analyze metrics in different dimensions such as time, geography, gender, product, etc. For example, sales for the company is up. What region is most responsible for this increase? Which store in this region is most responsible for the increase? What particular product category or categories contributed the most to the increase? Answering these types of questions in order means that you are performing an OLAP analysis.

OLAP servers provides better performance for accessing multidimensional data. The most important mechanism in OLAP which allows it to achieve such performance is the use of aggregations.

Aggregations are built from the fact table by changing the granularity on specific dimensions and aggregating up data along these dimensions. OLAP systems gives analytical capabilities that are not in SQL or are more difficult to obtain.

1. OLTP (on-line transaction processing)

1. OLAP (on-line analytical processing)

2. Day-to-day operations: purchasing, inventory, banking, manufacturing, payroll, registration, accounting, etc.

2. Data analysis and decision making

3. The tables are in the Normalized form.

3. The tables are in the De-Normalized form.

4. We Called the Storage objects as Tables. i.e., All the masters and the Transactions are stored in the tables.

4. We Called the Storage objects as Dimension and Facts. i.e., All the masters Are dimension and the Transactions are Facts.

5. For Designing OLTP we used data modeling.

5. For Designing OLAP we used Dimension modeling.

OLAP is classified into two i.e., MOLAP & ROLAP

De-Normalized Tables Product_Dim

Normalized Tables Product Prod_Id

Prod_Id

Prod_Nam e

Base_Rate

Base_Rate Cat_Id

Prod_Name Category Cat_Id Cat_Name

Group

Cat_Desc

Group_Id

Group_Id

Cat_Name Cat_Desc Group_Name Group_Desc

Group_Name

Topics Later We will Cover

Group_Desc

1. Types of Dimensions 2. Slowly changing Dimensions 3. Hierarchies

SalesOrderDetails Cust_Id SalesPerson Prod_Id Order_Date Booked_Date Delivery_Date Unit_Price Qty Tax Created_By

SalesOrder_Fact Cust_Id Prod_Id Order_Date

Reference keys of Dimensions

Delivery_Date Unit_Price Qty Total_Amount Tax

Numeric fields called as Fact or measure

Qty*Unit_Price+Tax=Total Amount Usually calculate all the calculations before storing into OLAP

Prod_Di m Prod_Id ………

Org_Dim Org_Id SalesOrder_F act

………

Cust_Id Prod_Id Order_Date Delivery_Date Org_Id Cust_Di m

Unit_Price

Time_Di m

Qty

Date

Cust_Id

Total_Amount

Year

………

Tax

Month

STAR Schema

………

Product_Di m

SalesOrder_Fact

Prod_Id

Prod_Id

Prod_Name

Order_Date

Base_Rate

Delivery_Date

Cat_Name

Unit_Price

Cat_Desc

Qty

Group_Na me

Total_Amount

Group_Des c

Cust_Id

Tax

1. Dimensions will have only relation with the Fact.

(Normalized model) 2. One to many or One to One relation will Occur.

3. Performance is fast but required huge storage space.

1. Dimension will have a relation other than Fact. (DeNormalized model) 2. Used for many to many relation.

3. Performance is Low but required Less storage space.

A single, complete and consistent store of data obtained from a variety of different sources made available to end users in a what they can understand and use in a business context.

[Barry Devlin] 18

Data Warehousing -It is a process • Technique for assembling and managing data from various sources for the purpose of answering business questions.

Thus making decisions that were not previous possible • A decision support database maintained separately from the organization’s operational database 19

Also Data Mining works with Warehouse Data Data Warehousing provides the Enterprise with a memory

Data Mining provides the Enterprise with intelligence 20

Oracle 10g

Base Product

$ 25K

$ 40K

IBM DB2

$ 25K

Tuning $3K Diagnostics $3K Partitioning $10K

Performance Expert $10K

$ 56K 40K

$ 35K 25K

(included)

Manageability Base Product

$ 25K

OLAP $20k Mining $20k BI Bundle $20k

Business Intelligence

DB2 OLAP $35K DB2 Warehouse $75K Cube Views $9.5K

(included)

Manageability Base Product

$ 25K

$$116K 56K

$ $154.5K 35K

Data Guard $116K

Recovery Expert $10k

High Availability Business Intelligence

(included)

Manageability Base Product

$ 25K

$ 116K 232K

$ 164.5K 154.5K

$116K $232K

$164.5K

$$348k 232K $464k

$$164.5K 329K

Multi-core High Availability Business Intelligence

(included)

Manageability Base Product

$ 25K

Data Analysis

Reporting, OLAP, Data Mining

Data Storage

Repository Data-Migration

Operational Data Sources

Middleware (Populations-Tools)

What happened?

Why did it happen?

What happened why and how?

What will happen?

Additional Benefit

Number of Users

OLTP O

L

A

ROLAP

Stage DB Optional

MOLAP CUBE

SSAS SSIS

Data Marts

IS S S

Integration Services

P

SSRS Analysis Services

Reporting Services

OLTP – Online Transaction Processing OLAP – Online Analytical Processing MOLAP – Multidimensional OLAP ROLAP – Relational OLAP HOLAP – Hybrid OALP Dimensions – De-normalized master tables Attributes – Columns of Dimensions Hierarchies – sequential order of attributes Facts (Measure group) – Transactions tables in DWH Fact (Measures) Cubes – Multidimensional storage of Data KPI’s – Key performance indicator Dashboards – combination of reports,kpis,charts Data Marts – Subjective Collection of Data SCD’s – Slowly changing Dimensions Perspectives – Child Cube