OLAP

1
OLAP

• Niarcas Jeffrey Rick Ratkowski

2
What OLAP is Good For

• Bulk data loading
• Business Intelligence
• Sales Forecasting
• Budgeting, Financial Modeling
• Aggregation of data
• Time series analysis
• Queries with large complexity
• Calculations of new data based on Business Models

3
OLAP Benefits

• Increased Productivity by end users
• Reduces IT backlog (more emphasis on end user)
• Reduces query and network traffic
• Organizational Control over data
• Increases Revenue and lowers cost (?)

4
Representing Data

• OLAP can be thought of as a cube of data
• Imagine 3 Dimensions (Time, Products, Market) (x,
  y, z)
• Dimensionality can explode if unchecked imagine
  Wal-Mart
• 5000 dates X 600,000 Products X 10 Measures X
  4000 X 100,000,000 (Customers)

5
Representing Data
Two-dimensional matrix
Three Field Table
6
Representing Data
Four-field Table
Three-dimensional Cube
7
Dense vs. Sparse Dimensions

• In order to help control dimensionality, data is
  segmented between dense and sparse dimensions
• A dimension is defined as sparse relative to
  another dimension if it is lowly populated
  compared to another dimension
• For example, given a time period there is a lower
  likelihood of every product containing data, so
  products are considered sparse relative to time
• Furthermore, given a product it has a high likely
  hood of been sold across all time dimensions
• Essbase stores data in blocks, dense dimensions
  act as blocks, while sparse dimensions acts as
  indexes to those blocks
• Optimization of Indexes vs. Data Blocks

8
Codds Rules for OLAP Tools

• Multi-Dimensional Conceptual View OLAP tools
  should provide users with a model that coincides
  with users view of the enterprise (this does not
  mean multidimensional storage)
• Transparency Underlying OLAP database should be
  transparent to the users. Basically all data
  sources must be integrated

9
Codds Rules (Cont)

• Accessibility The OLAP Tools should have access
  to ALL data in a corporation, relational,
  non-relational, legacy (possibly at a transaction
  level)
• Consistent Reporting Performance Reporting
  performance should not degrade with the addition
  of additional data, or change in enterprise model

10
Codds Rules (Cont)

• Client-Server Architecture
• OLAP Should operate in client server environment,
  while providing
• Optimal Performance
• Flexibility
• Adaptability
• Scalability
• Interpretability
• Generic Dimensionality
• Every dimension should be treated equally.
  Reporting should not be biased toward any
  dimension

11
Codds Rules (Cont)

• Dynamic Sparse matrix handling
• Multi-User Support
• Users must be able to work concurrently
• Unrestricted cross-dimensional operations
• Must recognize hierarchies automatically and
  perform roll-up calculations across dimensions

12
Codds Rules (Cont)

• Intuitive Data Manipulation
• Slice and Dice (pivot), drill down, point click
• Flexible Reporting
• Users should be able to retrieve any view of the
  data
• Unlimited Dimensions and aggregation levels
• OLAP Should impose zero restrictions on
  dimensionality

13
Architectures

• Codds rules provide a basis for what systems
  should do, their implementations vary
• MOLAP Multi-Dimensional OLAP
• ROLAP Relational OLAP
• HOLAP Hybrid OLAP
• DOLAP Desktop OLAP

14
Multi-Dimensional OLAP

• Data modelled in a multi-dimensional array
• Fast queries
• pre-calculating or pre-consolidating data
• Good for smaller storage space
• lt 50 GB

15
Multi-Dimensional OLAP

• Data is aggregated and stored according to
  predicted usage
• Systems are best used when data is desired for a
  specific application
• Tight Coupling between application and
  presentation layer

16
Multi-Dimensional OLAP
Relational database server and/or legacy systems
End-User
MOLAP server
Data Request
Load
Result Set
17
MOLAP Issues

• Limited amount of data can be stored an analyzed
• Navigation of Data is limited
• Costly to maintain
• Does not scale well

18
Relational ROLAP

• Fastest growing type of OLAP
• Takes advantage of the Relational Architecture
• Supports RDBMS through use of metadata
• Requires use of Highly De normalized Database
  Scheme (Star Schema)

19
Relational ROLAP

• Both disciplined and ad hoc usage
• Can contain both detailed and summarized data
• Platform portability
• Exploitation of hardware advances
• (parallel processing)
• Scales well

20
Relational ROLAP
Relational database server
End-User
Data Request
ROLAP Server
Load
Result Set
21
ROLAP Issues

• Problems when making multiple relational passes
• Middle ware developed which takes Relational and
  makes it a multi dimensional structure

22
ROLAP vs MOLAP

• ROLAP
• Data gt 50GB
• Platform portability
• Read-only access
• Need to scale

• MOLAP
• Data lt50GB
• Easier to implement
• Require write access
• Fast responses

23
Hybrid OLAP

• Typically thought of as poor model
• Deliver select data via MOLAP server to end
  users. Utilizes MOLAP and ROLAP architectures
  giving it the problems of both
• Development Issues
• Results in lots of data redundancy
• It allows users to build custom cubes causing
  data inconsistencies
• Only limited amounts of Data can be maintained
  efficiently
• Almost all systems utilize HOLAP to some respects

24
Desktop OLAP

• After data has been narrowed the result set is
  passed to the users machine, and then the users
  PC performs all the calculations.
• Many DOLAP products allow only read access
• Basic Processing Centralized, everything else
  decenteralized
• Development Issues
• Trending toward thin client machines (?)
• Reduces efforts in maintaining server
• Security Issues

25
Desktop OLAP
Relational database server
OLAP data is deployed from relational database or
MOLAP server to desktop PC or laptop through
e-mail, web, or traditional client-server
architecture.
MOLAP server
26
OLAP extensions of SQL

• ROLLUP aggregates a field in a relational
  database providing totals and subtotals.
• CUBE results are the cross tabulation of all
  possible combinations of the dimensions

27
Hyperion vs. Analysis Services
28
Sources

• Our textbook
• http//www.analysisteam.com/Newsletter_VIIn1_sube.
  html
• http//en.wikipedia.org/wiki/MOLAP
• http//www.windowsitpro.com/Windows/Article/Articl
  eID/3786/3786.html
• http//www.donmeyer.com/art3.html