Nature of OLAP Analysis / Reporting

Nature of OLAP Analysis

• Aggregation Comparison
• Budget vs. Expenses
• Ranking
• Access to detailed and aggregate data
• Complex criteria specification
• Visualization

Reporting

Strengths of OLAP / Typical OLAP Query

Strengths of OLAP

• It is a powerful visualization paradigm.
• It provides fast, interactive response times.
• It is good for analyzing time series.
• It can be useful to find some clusters and outliners.

Typical OLAP Query

• Write a multi-table join to compare Budget for each Org YTD this year vs. last year.
• Repeat the above process to find the top 5 Donors to Funds
• Repeat the above process to find the Funds of a Org to new vs. existing Donor.

Reporting

• Business intelligence tools: These are software applications that simplify the process of development and production of business reports based on data warehouse data.

• Executive information systems (known more widely as Dashboard (business):
These are software applications that are used to display complex business metrics and information in a graphical way to allow rapid understanding.

• OLAP Tools: OLAP tools form data into logical multi-dimensional structures and allow users to select which dimensions to view data by.

• Data Mining: Data mining tools are software that allow users to perform detailed mathematical and statistical calculations on detailed data warehouse data to detect trends, identify patterns and analyze data.

Star Schema Data Star Schema Data Model

Star Schema Architecture

• Dimension tables
– Textual descriptions of the dimensions of data Textual descriptions of the dimensions of data
– Represents the data that you slice and dice Represents the data that you slice and dice

• Fact tables Fact tables
– Place where numerical measurements of business are Place where numerical measurements of business are stored

Goal: Unified Access to Data

•Collects and combines information
•Provides integrated view, uniform user interface
•Supports sharing

Heterogeneous Information Sources

• Different interfaces
• Different data representations
• Duplicate and inconsistent information
• Vertical fragmentation of informational systems (vertical stove pipes)
• Result of application (user)-driven development of operational systems
Digital Libraries

What are the users What are the users saying

•Data should be integrated across the Data should be integrated across the
enterprise enterprise

•Summary data had a real value to the Summary data had a real value to the
organization organization

•Historical data held the key to Historical data held the key to
understanding data over time understanding data over time

•What What-if capabilities are if capabilities are required

What What does a data warehouse do?

• Integrate divergent information from various systems which Integrate divergent information from various systems enable users to quickly produce powerful ad enable users to quickly produce powerful ad-hoc queries hoc queries and perform complex analysis and perform complex analysis

• Create an infrastructure for reusing the data in numerous Create an infrastructure for reusing the data in numerous ways ways

• Create an open systems environment to make useful Create an open systems environment to make useful information easily accessible to authorized users information easily accessible to authorized users

• Help managers make informed decisions Help managers make informed decisions

Data Warehouse VS OLTP

Data Warehouse V’s OLTP
Enterprise Data Warehouse	Traditional Database
Integrated Data	Application-specific Data
Current/Historical Data	Current Data
Organized by Subject	Organized for Data Entry
Non-Volatile Data	Updated Data
Denormalized Data	Normalized Data
Descriptive Data	Encoded Data
Detailed/Summarized Data	Raw Data
Knowledge user (Manager)	Clerical User

What is a Data Warehouse?

A data warehouse is a subject-integrated, time-varying, non-volatile collection of data in support of the management's decision-making process."
• ---Bill Inmon

“A data warehouse is simply a single,complete, and consistent store of data obtained from a variety of sources and made available to end users in a way they can understand and use it in a business context.”
• ---Barry Devlin, IBM Consultant