On-line Analytical Processing OLAP

1
On-line Analytical Processing OLAP
Analysis is simplifying, breaking down things
into parts, picking out strands and elements.
Analysis is comparing unknown things with things
that are known. Analysis also involves picking
out relationships and putting them back together
as a whole. Edward de Bono
2
The Work Environment Today
Bad Decisions!
3
What is OLAP ??

• Multiple views
• Drill-Down
• Slice-n-Dice

On-Line Analytical Processing
Set of functionalities that facilitate
multi-dimensional data analysisfor faster, more
informed decision making

• Natural to users
• Cubes of data

4
Drill-Down Through a Dimension
5
Dicing - Ranging of Data
Clyde
Coupe
White
Blue
Diced Data
6
Slicing - Rotation of Data
Rotate the data cube by 90
Model
Model
Van
Van
Coupe
Coupe
Sedan
Sedan
Smith
Clyde
Miller
White
Red
Blue
Color
Dealership
7
What is OLAP ??

• Multiple views
• Drill-Down
• Slice-n-Dice

On-Line Analytical Processing
Set of functionalities that facilitate
multi-dimensional data analysisfor faster, more
informed decision making

• Natural to users
• Cubes of data

• Data
• Processes
• Relations
• Concepts

• Detection
• Measurement
• Comparison

8
OLAP Goals
FasterCognition
OLAP TOOLS
BetterComprehension
BetterCommunication
BetterDecision-Making
9
OLTP versus OLAP
10
Data Warehouse mining

• Operational databases are designed to process
  individual transactions efficiently. Once
  transaction data is no longer useful, it is
  transferred to a data warehouse, which is defined
  as a subject oriented, integrated, non-volatile,
  and time variant collection of data in support of
  managements decision.
• Three categories of decision support are
• Reporting data --- sql query design
• Analyzing data --- OLAP
• Knowledge discovery data mining technique
• This part of assignment is to help you
  understand OLAP tools

11
Four Characteristics of OLAP

• Use Multidimensional data analysis techniques
  (Most distinguishing)
• Multidimensional data analysis requires
  multidimensional data representation, which is
  usually provided by RDBMS.
• Provide advanced database support
• Provide easy-to-use end-user interfaces
• Support client/server architecture

12
Multidimensional Data Analysis Techniques

• Viewing data as part of a multidimensional
  structure
• Multidimensional data analysis techniques are
  augmented by the following
• Advanced data presentation functions
• Pivot tables
• 3-D graphics
• Crosstabs
• Data rotation
• 3-D cubes

13

• Advanced data aggregation, consolidation, and
  classification functions to
• create multiple data aggregation levels
• slice and dice data
• Drill-down and roll up
• Advanced Computational functions
• Statistical functions
• Financial/accounting functions
• OR/MS modeling techniques (LP, regression, etc.)

14
What is a Pivot Table?

• A summary table based on multiple conditions
• A PivotTable uses two-dimensional data to create
  a three-dimensional table.

15
MDDA Assignments

• Subject Multidimensional Data Analysis
  (OnLine Analytical Processing-OLAP)
• Purpose Data collected from transaction
  processing systems sometimes do not provide the
  users/decision makers with meaningful
  information. You need to learn how to analyze the
  data using OLAP.
• In order to complete the OLAP assignment, you
  will follow step-by-step procedure from data to
  pivot table. Here is an example for your
  practice before doing the assignment.
• Example of creating pivot table (1) Down load
  the data (dw_p2.xls) from the handout dropbox.
  (2) Using MS Excel program, create a pivot
  table.

16
data
17
Creating a Pivot Table/Chart

• Create a PivotTable or PivotChart report
• Open MS Excel 2007
• Load dw_p2.xls to the spreadsheet
• Select a cell in a range of cells, or put the
  insertion point inside of a Microsoft Office
  Excel table. Make sure that the range of cells
  has column headings.

18
Select the type of report to generate by doing
one of the following

• To create a PivotTable report, on the Insert tab,
  in the Tables group, click PivotTable, and then
  click PivotTable.
• Excel displays the Create PivotTable dialog box
  (next slide).

19
Create pivot table window appears
20

• Specify a location by doing one of the following
  
• To place the PivotTable report in a new worksheet
  starting at cell A1, click New Worksheet.
• Excel adds an empty PivotTable report to the
  specified location and displays the PivotTable
  Field List so that you can add fields, create a
  layout, and customize the PivotTable report.

21
Empty PT report
22
Pivot Table to produce
23

• Page field ? year
• Row field ? product
• Column filed ? region
• Data Field ? value

24
Laying Out the PivotTable

• The following list describes the four areas that
  are available to apply fields
• Page. Create a drop-down menu above the table
• Row. Summarize data from top down
• Column. Summarize data from left to right
• Data. This area adds, counts, or creates other
  analytical functions.

25
First PT done
26
(No Transcript)
27

• If you reached slide 24, click chart wizard icon
  to draw 3 dimensional graph.
• Slide 26 shows step 1 of 4 of the process of
  creating a pivot table.
• Select the last sub-chart type in slide 27 and
  click next.
• Enter the label of your pivot table your names
  pivot table as shown in slide 27.
• Slide 28 shows the pivot table.

28
(No Transcript)
29
(No Transcript)
30
Draw the 3D Bar Graph
31

• Here is an example of constructing a pivot table.
  
• To practice how to do OLAP/multi-dimensional data
  analysis, you may use the following data,
  downloadable from the dropbox

32
Question 1How many healthy males are in the
database?

• Step 1 delete rows 2 and 3.

33
Step 2 Select the range of data correctly
(a1n304).
34
Step 2 Click sex from pivot table field list
and drag it to drop row fields here
35
Step 3 Click class from pivot table field list
and drag it to drop column fields here
36
Step 4 Click either class or sex from pivot
table field list and drag it to drop data items
here
37
About page field

• Page fields are the fields that can be used to
  create multiple choice lists so that you can
  choose a specific item. Using the same question 1
  (How many healthy males are in the database?), I
  will demonstrate the use of page fields.

38
Step 4 Click sex from pivot table field list
and drag it to drop page fields here
39
Step 5 Click sex from pivot table field list
and drag it to drop page fields here. Now you
have two page fields. Therefore, you can choose
healthy and male to answer question1.