OnLine Analytical Processing (OLAP)
1 What is OLAP?
• OLAP = On-Line Analytical Processing. OLAP tools are actually specialized query
programs that are used to perform special searches in databases and data warehouses.
• “Stated another way, OLAP is a category of applications and technologies for collecting,
managing, processing, and presenting multidimensional data for analysis and
management purposes”
• OLAP (as opposed to OLTP) is a mass of raw data to be processed into useful and
meaningful intelligence / information.
• OLAP includes a number of techniques and tools. Techniques: SQL, Pivot Tables, Pivot
Charts, etc; Tools: Excel, Cognos, Hyperion (Oracle), SQL Server BI environment, etc.
• OLAP is typically applied to DWHs, although of course it can be applied to operational
databases and other sources (e.g. spreadsheets, independent databases)
Note:
• Pivot table is a representation of an OLAP cube.
• All is about analysis projects, management questions. It’s 95% of the time about
furnishing that kind of info to a middle level manager so that he knows how to adapt.
• As opposed to OLTP, OLAP intelligence means information
Definition:
OLAP is an approach to answering multi-dimensional analytical (MDA) queries swiftly in
computing. OLAP is part of the broader category of business intelligence, which also
encompasses relational databases, report writing and data mining. Typical applications of
OLAP include business reporting for sales, marketing, management reporting, business
process management (BPM), budgeting and forecasting, financial reporting and similar
areas, with new applications emerging, such as agriculture. The term OLAP was created as a
slight modification of the traditional database term online transaction processing (OLTP).
OLAP tools enable users to analyze multidimensional data interactively from multiple
perspectives. OLAP consists of three basic analytical operations: consolidation (roll-up), drill-
down, and slicing and dicing. Consolidation involves the aggregation of data that can be
accumulated and computed in one or more dimensions. For example, all sales offices are
rolled up to the sales department or sales division to anticipate sales trends. By contrast,
the drill-down is a technique that allows users to navigate through the details. For instance,
users can view the sales by individual products that make up a region's sales. Slicing and
dicing is a feature whereby users can take out (slicing) a specific set of data of the OLAP
cube and view (dicing) the slices from different viewpoints. These viewpoints are sometimes
called dimensions (such as looking at the same sales by salesperson or by date or by
customer or by product or by region, etc.)
Databases configured for OLAP use a multidimensional data model, allowing for complex
analytical and ad hoc queries with a rapid execution time. They borrow aspects of
navigational databases, hierarchical databases and relational databases.
1
, OLAP is typically contrasted to OLTP (online transaction processing), which is generally
characterized by much less complex queries, in a larger volume, to process transactions
rather than for the purpose of business intelligence or reporting. Whereas OLAP systems are
mostly optimized for read, OLTP has to process all kinds of queries (read, insert, update and
delete).
2 Types of OLAP
1. Relational OLAP. Starts from a purely logical relational database model (including a
relational DW), and is used with specific SQL statements.
2. Multidimensional OLAP. The program considers the OLAP data warehouse (or data
mart) as a multidimensional cube of data. Key concepts:
i. Dimensions & Measures
ii. Slicing & Dicing
iii. Dimensions & Subdimensions
iv. Roll-up (Aggregation) & Drill Down
v. Nesting
3. Hybrid OLAP. We seek here to combine the advantages of relational OLAP and
multidimensional OLAP.
2.1 Relational OLAP
• Relational OLAP is based on some simple extensions of the SQL query language. More
specifically: Rollup and Cube (cf. the previous part)
• These extensions are not part of the SQL standard but are supported by such large
database systems like SQL Server, Oracle, MySql, etc. (MS Access does not support
these extensions!)
• For example:
o SELECT Sort, Place, SUM(amount) as amount
FROM animal
GROUP BY sort, place
WITH ROLLUP;
o SELECT Sort, Place, SUM(amount) as amount
FROM animal
GROUP BY sort, place
WITH CUBE; Section Category AverageSalary
• Another example Sales Laborer 18 850
Sales Employee 32 780
o SELECT Section, Category, AVG(Salary)
Sales Manager 58 610
FROM Workers
Marketing Employee 33 410
GROUP BY Section, Category;
Marketing Manager 59 230
Production Laborer 17 750
Production Employee 28 520
Production Manager 42 100
2
1 What is OLAP?
• OLAP = On-Line Analytical Processing. OLAP tools are actually specialized query
programs that are used to perform special searches in databases and data warehouses.
• “Stated another way, OLAP is a category of applications and technologies for collecting,
managing, processing, and presenting multidimensional data for analysis and
management purposes”
• OLAP (as opposed to OLTP) is a mass of raw data to be processed into useful and
meaningful intelligence / information.
• OLAP includes a number of techniques and tools. Techniques: SQL, Pivot Tables, Pivot
Charts, etc; Tools: Excel, Cognos, Hyperion (Oracle), SQL Server BI environment, etc.
• OLAP is typically applied to DWHs, although of course it can be applied to operational
databases and other sources (e.g. spreadsheets, independent databases)
Note:
• Pivot table is a representation of an OLAP cube.
• All is about analysis projects, management questions. It’s 95% of the time about
furnishing that kind of info to a middle level manager so that he knows how to adapt.
• As opposed to OLTP, OLAP intelligence means information
Definition:
OLAP is an approach to answering multi-dimensional analytical (MDA) queries swiftly in
computing. OLAP is part of the broader category of business intelligence, which also
encompasses relational databases, report writing and data mining. Typical applications of
OLAP include business reporting for sales, marketing, management reporting, business
process management (BPM), budgeting and forecasting, financial reporting and similar
areas, with new applications emerging, such as agriculture. The term OLAP was created as a
slight modification of the traditional database term online transaction processing (OLTP).
OLAP tools enable users to analyze multidimensional data interactively from multiple
perspectives. OLAP consists of three basic analytical operations: consolidation (roll-up), drill-
down, and slicing and dicing. Consolidation involves the aggregation of data that can be
accumulated and computed in one or more dimensions. For example, all sales offices are
rolled up to the sales department or sales division to anticipate sales trends. By contrast,
the drill-down is a technique that allows users to navigate through the details. For instance,
users can view the sales by individual products that make up a region's sales. Slicing and
dicing is a feature whereby users can take out (slicing) a specific set of data of the OLAP
cube and view (dicing) the slices from different viewpoints. These viewpoints are sometimes
called dimensions (such as looking at the same sales by salesperson or by date or by
customer or by product or by region, etc.)
Databases configured for OLAP use a multidimensional data model, allowing for complex
analytical and ad hoc queries with a rapid execution time. They borrow aspects of
navigational databases, hierarchical databases and relational databases.
1
, OLAP is typically contrasted to OLTP (online transaction processing), which is generally
characterized by much less complex queries, in a larger volume, to process transactions
rather than for the purpose of business intelligence or reporting. Whereas OLAP systems are
mostly optimized for read, OLTP has to process all kinds of queries (read, insert, update and
delete).
2 Types of OLAP
1. Relational OLAP. Starts from a purely logical relational database model (including a
relational DW), and is used with specific SQL statements.
2. Multidimensional OLAP. The program considers the OLAP data warehouse (or data
mart) as a multidimensional cube of data. Key concepts:
i. Dimensions & Measures
ii. Slicing & Dicing
iii. Dimensions & Subdimensions
iv. Roll-up (Aggregation) & Drill Down
v. Nesting
3. Hybrid OLAP. We seek here to combine the advantages of relational OLAP and
multidimensional OLAP.
2.1 Relational OLAP
• Relational OLAP is based on some simple extensions of the SQL query language. More
specifically: Rollup and Cube (cf. the previous part)
• These extensions are not part of the SQL standard but are supported by such large
database systems like SQL Server, Oracle, MySql, etc. (MS Access does not support
these extensions!)
• For example:
o SELECT Sort, Place, SUM(amount) as amount
FROM animal
GROUP BY sort, place
WITH ROLLUP;
o SELECT Sort, Place, SUM(amount) as amount
FROM animal
GROUP BY sort, place
WITH CUBE; Section Category AverageSalary
• Another example Sales Laborer 18 850
Sales Employee 32 780
o SELECT Section, Category, AVG(Salary)
Sales Manager 58 610
FROM Workers
Marketing Employee 33 410
GROUP BY Section, Category;
Marketing Manager 59 230
Production Laborer 17 750
Production Employee 28 520
Production Manager 42 100
2
