OLAP & USING SSAS TO ANALYZE OLAP CUBE IN RETAIL
DATABASE
Group 9: Number Four
1. THANG MAI HOANG
2. TRAM TRAN THANH
3. LINH NGUYEN THI THUY
4. NGUYEN BUI BA
Advisor:
Associate Professor Phuc Do
INTRODUCTION
Hey Employee, how much money we made last year?
Employee: $1 billion dollars Sir !
How much did we make per year per quarter?
Which products did well and which failed?
What are the sales by region, country, by year, by quarter etc.
Employee (thinking ) : Hmm What is the best way to do this?? :=(
???
2
1. INTRODUCTION TO OLAP
2. OLAP CUBE
3. OLAP OPERATIONS
4. TYPE OF OLAP
5. OLAP ADVANTAGES & DISADVANTAGES
6. DATA WAREHOUSE
7. SQL SERVER ANALYSIS SERVICE
8. CASE STUDY
9. REFERENCES
10. Q/A
3
AGENDA
1. INTRODUCTION TO OLAP

•
OLAP processing is often used for data mining
•
OLAP products are typically designed for multiple-user environments, with the cost of the
software based on the number of users

What is OLAP?
1. INTRODUCTION TO OLAP
7
•
To derive summarized information from large volume database
•
To generate automated reports for human view

Purpose of OLAP
1. INTRODUCTION TO OLAP
8
1. INTRODUCTION TO OLAP
9
•
Increasing data storage
•
Data versus Information
•
Data layout

Why do we need OLAP ?
2. THE OLAP CUBE
10
•

city day
store
PRODUCT LOCATION TIME
3. OLAP OPERATIONS
15

Pivoting: aggregate on selected dimensions
◦
usually 2 dims (cross-tabulation)
3. OLAP OPERATIONS
16

Slice and Dice: select and project on one or more dimensions
p
r
o
d
u
c
t
customers
store
customer = “Smith”
4. TYPE OF OLAP
17
•
Relational OLAP(ROLAP)
•
Multidimensional OLAP(MOLAP)
•

Benefits

Provide a single common data model for all data of interest regardless of the data's
source.

Present the organization's information consistently

Enabling a central view across the enterprise

Maintain data history

Restructure the data so that it delivers excellent query performance, even for complex
analytic queries, without impacting the operational systems

Make decision–support queries easier to write
21
Data
Warehouse
Extract
Transform
Load
Refresh
OLAP Engine
Analysis
Query
Reports
Data mining
Monitor
&
Integrator

–
LOCATION(location_key,store,street_address,city,state, country,region)
–
dimension tables are not normalized
•
Transactions are described through a fact-table
–
each tuple consists of a pointer to each of the dimension-tables (foreign-key) and a list of measures (e.g.
sales $$$)
Fact Table
Location
Item Number
Buyer Number
Supplier Number
Time Period
Dollar Purchases
Unit Purchases
Dimension Table
Item Number
Item Name
Description
Category
Subcategory
Dimension Table
Buyer Number
Buyer Name
Department
Division
City
State

represents the most important variable of interest
Star Schema
7. SQL SERVER ANALYSIS SERVICE
25

Olap with SQL Server Analysis Service
•
Microsoft SQL Server Analysis Services, SSAS, is an online analytical processing (OLAP), data
mining and reporting tool in Microsoft SQL Server. SSAS is used as a tool by organizations to
analyze and make sense of information possibly spread out across multiple databases, or in
disparate tables. Microsoft has included a number of services in SQL Server related to business
intelligence and data warehousing. These services include Integration Services and Analysis
Services. Analysis Services includes a group of OLAP and data mining capabilities