11LogicalDWDesign

Logical Data Warehouse Design

Outline

OLAP Technologies
Relational Data Warehouse Design
Relational Implementation of Conceptual Model
The Time Dimension

Online Analytical Processing (OLAP) technologies analyze large volumes of data for business intelligence. They organize data into a multidimensional model, often called a data cube, which allows for fast and complex queries. There are three main types: Relational OLAP (ROLAP), Multidimensional OLAP (MOLAP), and Hybrid OLAP (HOLAP). 📊

Relational OLAP (ROLAP)

ROLAP stores data in a traditional relational database. It uses a star schema or snowflake schema to represent the multidimensional data. This approach leverages familiar SQL (Structured Query Language) to query the data, often with specialized extensions for efficiency.

Pros: Handles vast amounts of data since it's stored in a scalable relational database, Doesn't require a separate data structure, so it's relatively easy to integrate with existing relational systems, Uses a single data model for both detailed and aggregated data.

Cons: Performance can be slower than other methods because each query requires complex SQL joins, which can be computationally intensive.Creating a multidimensional view on top of a relational database can be complex, Relies heavily on the underlying database's query performance.

Multidimensional OLAP (MOLAP)

MOLAP stores data in a specialized multidimensional array structure. This structure, often a "data cube," pre-calculates and stores aggregations of data, which dramatically improves query performance. The data is pre-processed and stored in a format specifically optimized for OLAP operations.

Pros: Extremely fast query performance due to pre-calculated aggregations and the efficient data structure. The cube structure is intuitive for multidimensional analysis.Supports complex calculations and functions easily.

Cons: Limited by storage capacity, as the cube can grow very large, especially with many dimensions and members. Pre-calculating all possible aggregations can be time-consuming and resource-intensive. Updating the data cube requires a full or partial reprocessing of the data.

Hybrid OLAP (HOLAP)

HOLAP combines the best features of both ROLAP and MOLAP. It typically stores the detailed data in a relational database (ROLAP) and the aggregated data in a multidimensional cube (MOLAP). When a user queries data, HOLAP first checks the faster MOLAP cube for aggregations. If the query requires more detailed, low-level data, it retrieves it directly from the ROLAP database.

Pros: Balances performance and scalability. It offers fast access to aggregated data while retaining the ability to drill down to large volumes of detailed data. Reduces storage requirements compared to a full MOLAP cube, as not all detailed data needs to be stored in the cube itself. Provides more flexibility than either pure ROLAP or pure MOL.

Cons: Can be more complex to implement and manage due to the need to maintain two separate data structures. Requires a more sophisticated query engine to determine whether to access the cube or the relational database for a given request.

OLAP Technologies

Relational OLAP (ROLAP): Stores data in relational databases, supports extensions to SQL and special access methods to efficiently implement the model and its operations
Multidimensional OLAP (MOLAP): Stores data in special data structures (e.g., arrays) and implement OLAP operations in these structures
- Better performance than ROLAP for query and aggregation, less storage capacity than ROLAP
Hybrid OLAP (HOLAP): Combines both technologies
- For example, detailed data stored in relational databases, aggregations kept in a separate MOLAP store, etc

Logical Data Warehouse Design

Outline

OLAP Technologies
Relational Data Warehouse Design
Relational Implementation of Conceptual Mode
The Time Dimension

Online Analytical Processing (OLAP) systems, especially those using a Relational OLAP (ROLAP) architecture, organize data into specialized table structures to efficiently handle complex queries. The most common structures are the star, snowflake, starflake, and constellation schemas. These schemas are designed to optimize for quick data retrieval and analysis rather than for transactional processing.

Star Schema: The star schema is the most common ROLAP structure. It consists of a central fact table surrounded by a set of dimension tables, resembling a star.

Fact Table: This table contains the core business metrics (e.g., sales amount, quantity sold). It has foreign keys that link it to the dimension tables. The fact table is typically normalized.

Dimension Tables: These tables hold descriptive attributes related to the facts (e.g., product name, store location, date). Each dimension table has a primary key that corresponds to a foreign key in the fact table. Dimension tables are often denormalized, meaning they may contain some redundancy to improve query performance.

Snowflake Schema The snowflake schema is an extension of the star schema where the dimension tables are normalized. This means that a dimension table is further broken down into multiple related tables to eliminate redundancy. ❄️

Advantage: Normalizing the dimension tables saves storage space.

Disadvantage: This structure can lead to more complex queries with additional joins, which may decrease query performance compared to a star schema.

Starflake Schema

The starflake schema, also known as a hybrid schema, combines elements of both the star and snowflake schemas. In this structure, some dimension tables are normalized (like in a snowflake schema), while others remain denormalized (like in a star schema). 🌟❄️

This approach offers a balance between reducing redundancy and maintaining good query performance. Dimensions that are frequently used for analysis might be left denormalized for speed, while less-critical dimensions are normalized to save space.

Constellation Schema

The constellation schema, also known as a galaxy schema, is a complex structure that involves multiple fact tables that share some of the same dimension tables. ✨

This schema is used for more sophisticated data warehouse designs where different business processes (represented by different fact tables, e.g., Sales and Inventory) need to be analyzed using the same descriptive attributes (e.g., Product and Time).

Relational Data Warehouse Design

In ROLAP systems, tables organized in specialized structures
Star schema: One fact table and a set of dimension tables
- Referential integrity constraints between fact table and dimension tables
- Dimension tables may contain redundancy in the presence of hierarchies
- Dimension tables denormalized, fact tables normalized
Snowflake schema: Avoids redundancy of star schemas by normalizing dimension tables
- Normalized tables optimize storage space, but decrease performance
Starflake schema: Combination of the star and snowflake schemas, some dimensions normalized, other not
Constellation schema: Multiple fact tables that share dimension tables

Example of a Star Schema

Star Schema

Example of a Snowflake Schema

Snowflake Schema

Example of a Constellation Schema

Constellation Schema

Logical Data Warehouse Design

Outline

OLAP Technologies
Relational Data Warehouse Desig
Relational Implementation of Conceptual Mode
The Time Dimension

Relational Implementation of the Conceptual Model

A set of rules to translate the conceptual model (the MultiDim model) into the relational mode
Rule 1: A level L, provided it is not related to a fact with a one-to-one relationship, is mapped to a table TL that contains all attributes of the level
- A surrogate key may be added to the table, otherwise the identifier of the level will be the key of the table
- Additional attributes will be added to this table when mapping relationships using Rule 3 below
Rule 2: A fact F is mapped to a table TF that includes as attributes all measures of the fact
- A surrogate key may be added to the table
- Additional attributes will be added to this table when mapping relationships using Rule 3 below

Rule 3: A relationship between either a fact F and a dimension level L, or between dimension levels LP and LC (standing for the parent and child levels, respectively), can be mapped in three different ways, depending on its cardinalities:

Rule 3a: If the relationship is one-to-one, the table corresponding to the fact (TF) or to the child level (TC) is extended with all the attributes of the dimension level or the parent level, respectively

Rule 3b: If the relationship is one-to-many, the table corresponding to the fact (TF) or to the child level (TC) is extended with the surrogate key of the table corresponding to the dimension level (TL) or the parent level (TP), respectively, that is, there is a foreign key in the fact or child table pointing to the other table

Relational Implementation of the Conceptual Model

Rule 3: A relationship between either a fact F and a dimension level L, or between dimension levels LP and LC (standing for the parent and child levels, respectively), can be mapped in three different ways, depending on its cardinalities:
Rule 3a: If the relationship is one-to-one, the table corresponding to the fact (TF) or to the child level (TC) is extended with all the attributes of the dimension level or the parent level, respectively
Rule 3b: If the relationship is one-to-many, the table corresponding to the fact (TF) or to the child level (TC) is extended with the surrogate key of the table corresponding to the dimension level (TL) or the parent level (TP), respectively, that is, there is a foreign key in the fact or child table pointing to the other table

Relational Implementation of the Conceptual Model

Rule 3c: If the relationship is many-to-many, a new table TB (standing for bridge table) is created that contains as attributes the surrogate keys of the tables corresponding to the fact (TF) and the dimension level (TL), or the parent (TP) and child levels (TC), respectively. If the relationship has a distributing attribute, an additional attribute is added to the table to store this information

Relational Representation of the Northwind Data Warehouse

Northwind Data

Relational Representation of the Northwind Data Warehouse

The Sales table includes one FK for each level related to the fact with a one-to-many relationship
For Time , several roles: OrderDate , DueDate , and ShippedDate
Order : related to the fact with a one-to-one relationship, called a degenerate, or a fact dimension
Fact table contains five attributes representing the measures:
- UnitPrice , Quantity , Discount , SalesAmount , and Freight .
The many-to-many parent-child relationship between Employee and Territory is mapped to the table Territories , containing two foreign keys
Customer has a surrogate key CustomerKey and a database key CustomerAltKey
SupplierKey in Supplier is a database key

Logical Data Warehouse Design

Outline

OLAP Technologies
Relational Data Warehouse Design
Relational Implementation of Conceptual Mode
The Time Dimension

The time dimension is a crucial component of a data warehouse, especially in ROLAP systems. Unlike an OLTP (Online Transaction Processing) database where time is typically derived on the fly, a data warehouse explicitly stores temporal information in a dedicated dimension table. This design simplifies and accelerates complex analytical queries.

Time in OLTP vs. Data Warehouse

In an OLTP database, a date or time attribute is usually stored as a DATE or DATETIME data type. When you need to analyze data by a specific time period, such as a weekend, you use functions to extract that information from the date field. For example, you might use a function like DAYOFWEEK() to check if a date falls on a Saturday or Sunday. This is efficient for small, individual transactions but can be slow and resource-intensive when querying large datasets for historical analysis.

In a data warehouse, time is handled differently. It is represented as a time dimension table that contains various pre-computed attributes related to time. For instance, instead of just storing 2026-08-03, the time dimension table would have columns for Year, Month, Quarter, DayOfWeek, WeekendFlag, HolidayFlag, and more.

The Structure and Benefits of a Time Dimension

In a star or snowflake schema, the time dimension table is linked to the fact table via a foreign key, often called a TimeKey or DateKey. This key is an integer that uniquely identifies each day.

The primary benefit of this structure is that it makes querying and aggregation much faster. Instead of using complex functions on millions of rows, you can simply join the fact table with the time dimension table and filter on the pre-computed attributes.

For example, to calculate total sales on weekends, a query in a data warehouse is straightforward:

SQL

SELECT SUM(SalesAmount)

FROM Sales S, Time T

WHERE S.TimeKey = T.TimeKey AND T.WeekendFlag = 'Y';

The Time Dimension

Data warehouse: a historical database
Time dimension present in almost all data warehouses.
In a star or snowflake schema, time is included both as foreign key(s) in a fact table and as a time dimension containing the aggregation levels
OLTP databases: temporal information is usually derived from attributes of a DATE data type
- Example: A weekend is computed on-the-fly using appropriate functions
In a data warehouse time information is stored as explicit attributes in the time dimension
- Easy to compute: Total sales during weekends

The granularity of a time dimension refers to the smallest unit of time it represents. This can be at the level of years, quarters, months, days, or even hours and minutes. The choice of granularity depends on the business's analytical needs.

Understanding Granularity

The more granular the time dimension, the more detailed the analysis you can perform. For example, a time dimension with a daily granularity allows you to analyze daily sales trends, whereas a monthly granularity only allows for monthly analysis.

Low Granularity: (e.g., years, quarters, months)1

Pros: Smaller dimension table, requires less storage.

Cons: Limits detailed analysis.

High Granularity: (e.g., days, hours, minutes)

Pros: Enables very detailed analysis.

Cons: Larger dimension table, requires more storage.

Calculating the Number of Tuples

The number of rows, or tuples, in a time dimension table is determined by the combination of its granularity and the total time span it covers.

If a time dimension has a granularity of month and spans 5 years, the number of tuples is calculated as follows:

Tuples=Time Span (years)×Granularity (months per year)

Tuples=5×12=60

This means the time dimension table would have 60 rows, each representing a distinct month within the 5-year period.

For a time dimension with a daily granularity spanning the same 5 years (and assuming 365 days per year), the calculation would be:

Tuples=5×365=1825

The larger the time span and the finer the granularity, the more tuples the dimension table will contain. Therefore, carefully choosing the appropriate granularity is essential for balancing analytical needs with database performance.

The Time Dimension

The granularity of the time dimension varies depending on their use
Time dimension with a granularity month spanning 5 years will have 5 * 12 = 60 tuples

References

A. VAISMAN, E. ZIMANYI, Data Warehouse Systems: Design and Implementation, Chapter 5 Logical Data Warehouse Design, Springer Verlag, 2014