Unit 2 OLAP Introduction to OLAP OLTP Vs

Unit 2 OLAP

Introduction to OLAP OLTP Vs OLAP q. OLTP: On Line Transaction Processing ØDescribes processing at operational sites q. OLAP: On Line Analytical Processing ØDescribes processing at warehouse OLTP Ø Ø Ø Ø Mostly updates Many small transactions Mb-Tb of data Raw data Clerical users Up-to-date data Consistency, recoverability critical OLAP Ø Ø Ø Mostly reads Queries long, complex Gb-Tb of data Summarized, consolidated data Decision-makers, analysts as users

Characteristic of OLAP Systems Data Mart v. Smaller warehouses v. Spans part of organization Øe. g. , marketing (customers, products, sales) v. Do not require enterprise-wide consensus Øbut long term integration problems?

Multidimensional View v. A data warehouse is based on a multidimensional data model which views data in the form of a data cube v. A data cube, such as sales, allows data to be modeled and viewed in multiple dimensions ØDimension tables, such as item (item_name, brand, type), or time(day, week, month, quarter, year) ØFact table contains measures (such as dollars_sold) and keys to each of the related dimension tables v. In data warehousing literature, an n-D base cube is called a base cuboid. The topmost 0 -D cuboid, which holds the highest-level of summarization, is called the apex cuboid. The lattice of cuboids forms a data cube.

Data Cube Implementations

Conceptual Modeling of Data Warehouses v. Modeling data warehouses: dimensions & measures ØStar schema: A fact table in the middle connected to a set of dimension tables ØSnowflake schema: A refinement of star schema where some dimensional hierarchy is normalized into a set of smaller dimension tables, forming a shape similar to snowflake ØFact constellations: Multiple fact tables share dimension tables, viewed as a collection of stars, therefore called galaxy schema or fact constellation

STAR

STAR Schema

Terms v Fact table v Dimension tables v Measures

Dimension Hierarchies

Cube

3 -D Cube

ROLAP Vs MOLAP v. ROLAP: Relational On-Line Analytical Processing v. MOLAP: Multi-Dimensional On-Line Analytical Processing Aggregates v Add up amounts for day 1 v In SQL: SELECT sum(amt) FROM SALE WHERE date = 1 • Add up amounts by day • In SQL: SELECT date, sum(amt) FROM SALE GROUP BY date

Another Example v Add up amounts by day, product v In SQL: SELECT date, sum(amt) FROM SALE GROUP BY date, prod. Id

Aggregates v. Operators: sum, count, max, min, median, ave v“Having” clause v. Using dimension hierarchy Øaverage by region (within store) Ømaximum by month (within date)

Cube Aggregation

Cube Operators

Extended Cube

Aggregation Using Hierarchies

Data Cube Operations v. Operators Øslice & dice Øroll-up, drill down Øpivoting Øother

Implementation of OLAP Software v Relational OLAP Server

v Multi-Dimensional OLAP Server

Overview on OLAP Software v. BI 2 M – Business Intelligence to Marketing and Management by Hand. M services. com v. Business Objects v. DB 2 Cube from IBM v. SQL Server 2000 from Micrososft v. Microstrategy 8