An Introduction to MSBI & DWH by Quontra

1
An Introduction to MSBI DWH
Presented
By Quontra Solutions
Email info_at_quontrasolutions.co.uk
Contact 44 (0)20-3734-1498 / 99 Website
www.quontrasolutions.co.uk
2
This Presentation will Helps you to understand
the following

• What Is ERP, In what way ERP BI Helps a
  Business
• What is Data warehousing ?? with different
  examples. How BI works with data warehouse
• What is MSBI ??
• Different BI Tools in the Market
• Understand the terminologies of Data warehouse
  (DWH)

3
Lets Understand this first

• What is DataBase Role of Database in any
  Business.
• What is ERP ?? What is OLTP ??
• What are master Transaction tables ??
• What is DWH ?? What is OLAP ??
• What are reports and why it is so important ??
• What are aggregations and how it is useful for
  Analysis.
• What is ETL ? What is the use of ETL in DWH ?

4
What is DataBase (DB)
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.
5
What is ERP OLTP
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 ATMS they are properly
and efficiently updating the database also those
transactions are properly effecting to their
Accounts.
6
Data, Data everywhere yet ...

• I cant find the data I need
• data is scattered over the network

7
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

8
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 ??
9
Data warehouse helps any Business in Many
Ways Lets say A producer wants to know.
10
What is DWH OLAP
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 managements decision-making
process.
11
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.
12
OLTP Vs OLAP
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 Dimension
and Facts. i.e., All the masters Are dimension
and the Transactions are Facts.
4. We Called the Storage objects as Tables.
i.e., All the masters and the Transactions are
stored in the tables.
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
13
OLTP OLAP
Masters Dimensions
Normalized Tables
De-Normalized Tables
Product_Dim
Prod_Id
Prod_Name
Base_Rate
Cat_Name
Cat_Desc
Group_Name
Group_Desc
Product
Prod_Id
Prod_Name
Base_Rate
Cat_Id
Category
Cat_Id
Cat_Name
Cat_Desc
Group_Id
Group
Group_Id
Group_Name
Group_Desc
Topics Later We will Cover
1. Types of Dimensions
2. Slowly changing Dimensions
3. Hierarchies
14
OLTP OLAP
Transactions Facts
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
Delivery_Date
Unit_Price
Qty
Total_Amount
Tax
Reference keys of Dimensions
Numeric fields called as Fact or measure
QtyUnit_PriceTaxTotal Amount Usually calculate
all the calculations before storing into OLAP
15
ROLAP MOLAP
Dimensions Facts
Cubes
Prod_Dim
Prod_Id

Org_Dim
Org_Id

SalesOrder_Fact
Cust_Id
Prod_Id
Order_Date
Delivery_Date
Org_Id
Unit_Price
Qty
Total_Amount
Tax
Time_Dim
Date
Year
Month

Cust_Dim
Cust_Id

STAR Schema
16
NOW WHAT IS OLAP ?
Dimensions
Facts
Cubes
SalesOrder_Fact
Cust_Id
Prod_Id
Order_Date
Delivery_Date
Unit_Price
Qty
Total_Amount
Tax
Product_Dim
Prod_Id
Prod_Name
Base_Rate
Cat_Name
Cat_Desc
Group_Name
Group_Desc
ROLAP
17
MOLAP SCHEMAS
Star Snow Flake

1. Dimensions will have only relation with the
Fact. (Normalized model)
1. Dimension will have a relation other than
Fact. (De-Normalized model)
2. One to many or One to One relation will Occur.
2. Used for many to many relation.
3. Performance is Low but required Less storage
space.
3. Performance is fast but required huge storage
space.
18
NOW WHAT IS DWH ?
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
19
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 organizations operational database

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

• Data Mining provides the Enterprise with
  intelligence

21
NOW WHAT IS MSBI ?
What is BI Implementing a data warehouse on any
business to provide the Analysis and decision
making solution using some set of tools is called
Business Intelligence.
Now what is MSBI Microsoft introduced a
complete tool kit inside the SQL SERVER to
implement Data warehoue.
22
The Different Roles of BI Developers
Administrator The person who can manage the
complete administration part of the Data
warehouse, similar to DBA. BI Architect The
person who designs the complete architecture of
Data Warehouse for any business. ETL Developer
The person who works on SSIS or any other ETL
Tools like Informatica. Cube Developer/OLAP
Developer The person who works on SSAS Cube
creations and MDX language. Report Developer
The person who works on SSRS Reports or similar
tools like BO , Crystal reports etc.,
23
Why Only MSBI ?
Oracle 10g
IBM DB2
Base Product
25K
40K
25K
24
Tuning 3K Diagnostics 3K Partitioning 10K
Performance Expert 10K
(included)
Manageability
Base Product
25K
40K
25K
56K
35K
25
DB2 OLAP 35K DB2 Warehouse 75K Cube Views 9.5K
OLAP 20k Mining 20k BI Bundle 20k
Business Intelligence
(included)
Manageability
Base Product
25K
35K
154.5K
56K
116K
26
Data Guard 116K
Recovery Expert 10k
High Availability
Business Intelligence
(included)
Manageability
Base Product
25K
154.5K
164.5K
232K
116K
27
116K - 232K
164.5K
Multi-core
High Availability
Business Intelligence
(included)
Manageability
Base Product
232K
25K
164.5K
329K
348k - 464k
28
DWH ARCHITECTURE
29
FRONTEND TOOLS
What happened?
Reporting
Why did it happen?
Interactive OLAP
What happened why and how?
Ad hoc-Queries
What will happen?
Data Mining
30
PRACTICAL SCENARIO
OLTP
O L A P
ROLAP
MOLAP
Stage DB Optional
CUBE
SSAS
Data Marts
SSIS
SSIS
SSRS
Analysis Services
Integration Services
Reporting Services
31
DWH TERMINOLOGIES
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 KPIs Key
performance indicator Dashboards combination of
reports,kpis,charts Data Marts Subjective
Collection of Data SCDs Slowly changing
Dimensions Perspectives Child Cube
32
THANK YOU ?