OLAP Concepts and Characteristics Quiz

Multidimensional Data Model Overview

• Slides are based on an essay written by Arthur P. Aguiar and prepared/organized by Dr. Motaz Abdul Aziz Al-Hami.
• Before analyzing data, it must be treated and stored. Incorrect storage can lead to high maintenance, processing costs, and useless data that wastes resources.
• Data needs organization before being passed to the data warehouse.
• The multidimensional data model is one solution to this.
• Understanding key concepts is important before using the model.

Online Analytical Processing (OLAP) & Online Transaction Platform (OLTP)

• OLAP analyzes and treats large amounts of data in a data warehouse; this can take minutes to hours for complex queries.
• Real-time OLAP (RTOLAP) is for analyzing and treating data in real-time; this is commonly used for massive information in various data warehouse dimensions.
• OLTP records all operational actions of a stock (e.g. bank transaction).
• OLTP has faster response times, typically milliseconds (real-time).
• OLAP is for analyzing; OLTP is for processing.
• Example: Analyzing temporal data (year, day, quarter, semester) requires OLAP.

Uses of OLAP & OLTP

• OLAP's primary purpose is analyzing aggregated data.
• OLTP's primary purpose is processing database transactions (e.g. orders, inventory).
• OLAP is used for generating reports, complex data analysis, and identifying trends.
• OLTP is used for processing orders, updating inventory, and managing customer accounts.

OLAP vs. Real-Time OLAP

• Real-time analytics (RTOLAP) is becoming popular, but some understand when to use it in their data stack.
• Some analytical queries can work with traditional OLAP, while others require real-time OLAP databases.
• OLAP is specifically designed for complex data analysis, including historical data and machine learning models. Use cases typically involve long-running queries for daily reports or occasional refreshed dashboards.

OLAP vs. Real-Time OLAP (continued)

• Real-time OLAP stores multidimensional data in seconds or milliseconds and supports more end users than traditional OLAP.
• High rates of queries per second (QPS) measured in the thousands to hundreds of thousands are common.
• Example: A banking transaction must be reversed if failed; if successful, immutable recording is required

OLTP vs. OLAP

• OLTP: High volume of transactions, fast processing, normalized data, many tables, "Who bought X?"
• OLAP: High volume of data, slow queries, denormalized data, fewer tables, "How many people bought X?"

Data Granularity

• Granularity directly affects data volume, search speed, and informational details.
• High granularity = less detail; low granularity = more detail.
• Example: A sales table with repeated salesperson names has low granularity, making it hard to identify top sellers. More data in a table typically means longer analysis times.
• Drill-down reduces granularity and increases detail.
• Drill-up increases granularity and reduces detail.
• Condensing data reduces redundancy, saves processing, and reduces space.

Multidimensional Data Analysis

• Analysis commonly uses structured data in a cube format (each side of the cube is a dimension).
• Multidimensional model (e.g., arithmetic queries with OLAP) offers superior query performance in creating complex queries.
• Smaller projects allow for more agile implementations.

Multidimensional Data Analysis (continued)

• A cube is used for visualizing data.
• Cubes are created by associating, summarizing, or aggregating tables. These tables form dimensions (e.g. sales per year).
• Granularity of the cube (low or high) is dependent on requirements.
• Data cubes are structures for storing and analyzing very large amounts of multidimensional data.

Multidimensional Data Analysis (continued 2)

• Data cube represents data in terms of dimensions and facts.
• Categorized into multidimensional data cubes and relational data cubes; often used in conjunction with each other as hybrid data cubes.
• Example data cube: (Time, Location, Sales, Item type)

Multidimensional Data Analysis (continued 3)

• Data cube is a multidimensional data model used in data analysis.
• It simplifies and summarized structured data with optimized storage, and ease analysis via OLAP Tools
• Data stored in a data cube is represented by dimensions & facts. Dimensions can be considered as viewpoints or entities.
• Understanding how data relationships impact analysis is emphasized.

Multidimensional Data Analysis (continued 4)

• The diagram represents how the tables function in a data cube.
• Each dimension has a dimension table which describes more detail & specifications for that dimension.

Data Cube Classification

• Data cubes can be classified into multidimensional arrays (MOLAP) or relational data cubes (ROLAP).
• MOLAP stores multidimensional view of large amounts of data and uses indexing to improve access, retrieval, and storage.
• ROLAP relies on relational databases and SQL; performance may be slower than MOLAP but is scalable for increasing data.
• Hybrid data cubes (HOLAP) blend both approaches.

Operations on Data Cubes

• Operations on data cubes can be used to view the contents through different angles.
• Four basic operations are used for this are roll-up, drill-down, slice, and dice, and pivot.
• Roll-up/down aggregates data through hierarchical dimensions.
• Slice/dice filters data based on dimensions.
• Pivot revolves dimensions used to view data.

Advantages of Data Cubes

• Data cubes simplify aggregation and summarization.
• Data cubes provide better data visualization.
• Data cubes store massive data very efficiently.
• Data cubes increase efficiency for the data warehouse.
• Data stored is fast to query and access.

Star Layout

• The star layout is the simplest multidimensional model.
• The fact table is central to the model.
• Dimensions are linked to the fact table through foreign keys.
• Large amounts of data are centrally managed without redundancy.

Snowflake Layout

• The snowflake layout is an extension of the star layout.
• The model reduces redundancy (compared to the star), and increase the number of dimensions.
• Increased dimensions can lead to more complex queries but improved performance.
• Snowflake model arrangements are designed so that dimensions become the central point of further details.

Constellation Scheme

• The constellation scheme groups dimensions, including multiple fact tables.
• Disadvantage: Complexity.

Star Schemas vs. OLAP Cubes

• Star schemas are often used as a foundation for OLAP cubes in relational databases.
• OLAP cubes offer significant performance advantages over relational databases for calculations and analysis tasks.
• OLAP cubes store and index data differently (optimized) for dimensionality, allowing faster analysis and aggregate queries.

Star Schemas vs. OLAP Cubes (continued)

• A star schema in a relational database provides a good physical foundation for creating an OLAP cube. It's generally regarded as a robust base for backups & recovery.
• OLAP cubes have advantages in speed and flexibility for complex analysis queries.
• OLAP cubes often use pre-calculated or aggregated data to improve speed during reporting/analysis.