Transcript OLAP Services - Northwestern University Information Technology

OLAP Services
Business Intelligence Solutions
Agenda
Definition of OLAP
Types of OLAP
Definition of Cube
Definition of DMR
Differences between Cube and DMR
Design Philosophy for building an efficient cube
Examples
Questions
OLAP
Definition of OLAP
 On-Line Analytical Processing is multi dimensional analysis of data stored in a
database.
Why do we need it
 Easy
Multi-dimensional presentation for business information and analysis
Easy to use
 Fast
OLAP Technology is very fast – 98% of reports in less than 1-3 seconds
 Powerful
Calculated Columns enables calculations that are difficult using relational
technology
OLAP
OLAP applications provide the following features:
 Offer high-performance access to pre-summarized data (in the form of cubes)
 Give users the power to retrieve answers to multidimensional business questions
quickly and easily
 Provide slice-and-dice views of multiple relationships in large quantities of presummarized data
Types Of OLAP
Types of OLAP
In the OLAP world, there are mainly three different types to physical representation of
Data Warehouse data
 Multidimensional Online Analytical Process (MOLAP)
 Relational Online Analytical Process (ROLAP)
 Hybrid Online Analytical Process (HOLAP)
MOLAP
Multidimensional Online Analytical Process (MOLAP):
 This is the traditional mode in OLAP analysis.
 In MOLAP data is stored in form of multidimensional cubes and not in relational
databases.
 It provides excellent query performance and the cubes are built for fast data retrieval.
 All calculations are pre-generated when the cube is created and can be easily applied
while querying data.
 The disadvantages of this model are that it can handle only a limited amount of data.
ROLAP
Relational Online Analytical Process (ROLAP) :

The underlying data in this model is stored in relational databases.

Since the data is stored in relational databases this model gives the appearance of
traditional OLAP’s slicing and dicing and drill down functionality.

The advantages of this model is it can handle a large amount of data and can
leverage all the functionalities of the relational database.
HOLAP
Hybrid Online Analytical Process (HOLAP)

HOLAP technology tries to combine the strengths of the MOLAP and ROLAP.

For summary type information HOLAP leverages cube technology and for drilling
down into details it uses the ROLAP model.
Definition of cube
High Level definition of Cube
 A cube is a set of data that is organized and structured in a hierarchical,
multidimensional arrangement
Why do we need it
 Rollup or sum the data to higher levels
 The models are defined by dimension structures and measures which can be easily
customized
 Time periods are handled in a specific way which makes data delivery easy
 High flexibility and portability
Cube Interface
Dimension
Levels
Cube
Sign on
Measures
Data source
Definition of DMR
What is DMR



DMR stands for Dimensionally Modeled Relation, a Cognos modeling technique allowing
to present relational data sources as OLAP cubes.
DMR processes relational data on the fly and presents it back to end users in a
hierarchical view, allowing them to navigate from summary to more detailed levels of data
in a visual format
All OLAP-style queries, roll-ups\drill-downs are then transformed into appropriate sql
(group by's, aggregations) by Cognos Server
Why do we need it


Analysis studio is available.
Real time Analysis
DMR in Framework
Dimension
Hierarchy
Levels
Differences Between OLAP and DMR
Advantages of OLAP Cube





Easy to create
Fast Performance
Limited Data
Data is up to last build
Pre Aggregated
Disadvantages of DMR




Adds complexity
Requires local processing, potentially moving large amounts of data from the
database to the BI server for final processing
Complex to create
5-20% more time based on data size and query complexity
Analysis Studio for Cube and DMR
Granite Cube
Granite OLAP
Philosophy for building a cube
1. Analyze requirements
2. Access your source data
3. Identify measures and Dimensions
4. Specify Time dimension
5. Identify Hierarchies
6. Create Model
Optimization of Cubes
Shorten processing Time in Transformer
 Use multiple queries to reduce the size of each source file
 Optimizing querying
 Incremental updates (Only add new data)
Shorten access time in Power Play
 Reduce the number of categories
Auto _Partition (Divides large power cube into set of small pre summarized
cubes)
 Maximize data consolidation by adding a sort step before records are written to the
cube
 Improve the performance of queries since already summarized
Cube Optimization Interface
Questions
Q & A?