OLAP REPORT
NGUYEN HUU PHUC · 14520698
Lecturer: Prof. Do Phuc
Business Intelligence is the collection of best practices and software tools developed to support
strategic decision making. The complexity and volume of information required to support
decision making has exploded.
Thus, data warehouse, a new storage for decision support database has been developed.
Besides, there is a need to analyze and present data where OLAP fits perfectly. It provides
advanced data analysis and visualization tools, including multidimensional data analysis.
I will cover decision making steps, question for decision support system and build a data
warehouse from real database, then use OLAP operations to generate report.
OVERVIEW OF DECISION MAKING
All decisions are about problems, decision making is all about picking the best one
among available choices. In business context, managerial decision making is one of the
most important skills of a manager, it affects vastly to the business success.
Managers usually make decisions by four-step:
Define the problem
Construct a model that describe the real-world problem
Identify possible solutions to the modeled problem and evaluate the solutions
Compare, choose and recommend a potential solution to the problem
Data I use in this report is about tennis tournament: the conducting location, who play in
these tournament, the betting odds of winner and looser.
Assume the club manager want to buy a new player, or a gambler want to bet on future
match. Analyze this data can help them in making their decision wiser and more
accurate.
QUESTION FOR DECISION SUPPORT
For years, managers considered decision making is a skill can be achieved by experience
or inner talent. But nowadays, with the help of science, management has been
standardized to common approach.
Also, the aid of modern technology has transformed some activities of decision making
to computerized activity. Now the process of decision making require less manpower,
but more accurate and efficient. Set of facilities to support decision making is called
Decision support system (DSS).
To use DSS efficiently, we need to make some question about the data, what we want to
analyze, what can transform our data into information, knowledge.
In context of tennis topic, I have put several questions to analyze:
- Who won the most tournament
- Who has the highest betting rate
- Which location held the most tournament
- Timely report of matches
- Performance of player over time (how their rank change, increase or decrease)
- Different surface and environment condition that player has played
2
BUILD THE DATA WAREHOUSE
First take the look of our data:
There are a lot of data in this dataset, but to build a data warehouse, we only need some of
them. To handle this data and build it to data warehouse, we use star schema approach.
The star schema architecture is the simplest data warehouse schema. The center of the star
consists of fact table and the points of the star are the dimension tables. Usually the fact tables
in a star schema are in third normal form(3NF) whereas dimensional tables are de-normalized.
FACT table: a fact table typically has two types of columns: foreign keys to dimension tables
and measures those that contain numeric facts. a fact table can contain fact's data on detail or
aggregated level. In this topic, Fact table can contains these measures: maxW,maxL,avgW,AvgL
and other measures that are derived from raw data
Dimension table: The primary keys of each of the dimension tables are part of the composite
primary key of the fact table. Dimensional attributes help to describe the dimensional value.
Dimension attribute can have attribute hierarchy. Dimensions in our topic are:
- Tournament
- Location
- Court
- Surface
- Match
- Series
- Time (this has attribute hierarchy)
The final star schema of tennis database:
3
OLAP OPERATIONS TO GENERATE REPORT
In this section, I will show the reports are made used basic OLAP operation, including:
- Roll up
- Drill Down
- Slice and Dice
- Pivot
REPORT ON MATCHES OF EACH TOURNAMENT, RUNNING TIME BY YEAR AND SERIES
(ROLL UP)
4
REPORT ON WINNER BY TIME AND MATCH TYPE (DRILL DOWN, SLICE AND DICE)
This report can be expand (by
the + button) or collapse base
on hierarchy of time.
REPORT ON MAXIMUM BETTING RATE OF EACH TOURNAMENT (PIVOT)
