Transcript More on OLAP

Overview of Business Intelligence
Analytical
tools
Data
Sources
Data
Warehouse
Data
visualisation
OLAP
Data
Mining
Analysis
Results
Data
visualisation
....
From Turban, Aronson and Liang
Some Questions
• Where does the data come from?
• How can we decide what data is important?
• How can data from different sources be
joined together (consolidated and
integrated) securely?
• How can data be analysed?
• How can these analyses be viewed?
On Line Analytical Processing
OLAP is a category of software technology
that enables analysts, managers and
executives to gain insight into data through
fast, consistent, interactive access to a wide
variety of possible views of information that
has been transformed from raw data to
reflect the real dimensionality of the
enterprise as understood by the user.
Fast Analysis of Shared Multidimensional
Information. (FASMI)
Why use OLAP ?
• Goal: To capture the structure of real world data
and provide support to the decision maker.
• OLAP involves many data items (many thousands
or even millions) which are involved in complex
relationships
• Fast response (<20 seconds) is crucial in OLAP
• OLAP functionality is characterized by dynamic
multidimensional analysis of consolidated
enterprise data supporting end user analytical
and navigational activities.
What is Multidimensionality?
• Sometimes decision makers want to work with
data in 3 or more dimensions e.g.:A manager might want to know the sales of a
given product in a geographic area, by a specific
salesperson, for a particular time period.
• Multidimensional organisation of data allows a
user to easily and quickly change the structure of
tables so they will be more meaningful.
• Different presentations of the same data may be
arranged quickly and easily
• What do we mean by different dimensions?
• What do we mean by different measures?
Examples :Factors in Multidimensionality
• Dimensions –products, locations,
salespeople, distribution channels,
products, industries.
• Measures – money, sales volume, head
count, actual vs forecasted
• Time – daily, weekly, monthly, quarterly.
What does Using OLAP involve?
•
•
•
•
Generating queries
Requesting ad hoc reports
Conducting statistical analysis
Building DSS and multimedia applications
• OLAP helps the user synthesize enterprise
information through comparative, personalized
viewing, as well as through analysis of historical
and projected data in various "what-if" data
model scenarios.
Analytical and Navigational Activities
• Calculations and modelling applied across
dimensions, through hierarchies and/or
across members
• trend analysis over sequential time periods
• Consolidation/Drill down
• Reachthrough to underlying detail data
• Slicing and Dicing
• Pivoting/Rotation to new dimensional
comparisons in the viewing area
OLAP: examples of Navigational operations
• Consolidation - involves the aggregation of data
i.e. simple roll-ups or complex expressions
involving inter-related data e.g. sales offices can
be rolled-up to districts and districts rolled-up to
regions.
• Drill-Down can go in the reverse direction i.e.
automatically display detail data which comprises
consolidated data e.g.You drill-down, for
example, from annual to quarterly sales figures
or drill-up from shops to regions.
Slicing and Dicing - ability to look at the data base
from different viewpoints e.g.
– a slice could show sales figures for January or
sales where regions where sales were below
£100,000
– one slice of the sales database might show all
sales of product type within regions;
– another slice might show all sales by sales
channel within each product type
– often performed along a time axis in order to
analyse trends and find patterns.
Pivoting allows dimensions to be viewed from
any perspective. e.g. pivot a 3D cube to view
different aspects of data:
Sales for Q1 by Product and Region
Sales for North by Product and Quarter
OLAP tools
How does OLAP work?
• OLAP is implemented in a multi-user
client/server mode and offers consistently
rapid response to queries, regardless of
database size and complexity.
• OLAP functionality is achieved through the
use of an OLAP Server.
OLAP SERVER
• - a high-capacity, multi-user data manipulation
engine specifically designed to support and
operate on multidimensional data structures.
• The design of the server and the structure of the
data are optimized for rapid ad-hoc information
retrieval in any orientation, as well as for fast,
flexible calculation and transformation of raw
data based on formulaic relationships.
Example – Microsoft OLAP
How can data be analysed?
• Microsoft Online Analytical Processing
(OLAP) makes it quick and easy to perform
ad-hoc queries and analysis of large
amounts of complex data across all aspects
of your business.
Example – Microsoft OLAP
Microsoft OLAP is used to report
on...
•
•
•
•
•
•
sales
marketing
management issues
business process management
budgeting and forecasting,
financial issues etc..