Data Warehousing and OLAP: Exam Prep

• This is a summary of key points for exam preparation from lecture notes

Data Warehousing and OLAP Basics

• Relational databases store data in tables and are suitable for transactional systems and data warehouses
• Multi-dimensional databases represent data as dimensions, providing faster and easier analysis

Data Warehouse (DW)

• Data Warehouses act as a repository of integrated, cleansed, and historical data used for decision support
• Data Warehouses are subject-oriented, focusing on subjects like products and customers
• Data Warehouses integrate data from heterogeneous sources
• Data Warehouses are time-variant, storing historical data for analysis
• Data Warehouses are non-volatile, meaning data is not frequently updated but added to

Data Warehouse Models

• Enterprise Data Warehouses contain all organizational data, both detailed and summarized
• Data Marts are subsets of data tailored for specific user groups, such as marketing departments
• Virtual Warehouses provide views over operational databases

ETL Process (Extraction, Transformation, Loading)

• During the Extraction stage, data is pulled from multiple sources
• The Transformation stage involves cleaning and converting data to the warehouse format
• In the Loading stage, data loading involves sorting, summarizing, and loading the data into the warehouse
• The Refresh stage propagates updates from source systems to the data warehouse

Data Warehouse Applications

• Data Warehouses are widely employed in financial services, banking, retail, and manufacturing

Terminologies

• Metadata describes data, including its structure, lineage, and algorithms
• A Data Cube is a multidimensional representation of data with dimensions like product, store, customer, and time, and measures like sales and units sold
• Data Marts are subsets of data tailored for specific departments like marketing

OLAP (Online Analytical Processing)

• ROLAP (Relational OLAP) works with relational databases, using relational tables
• MOLAP (Multidimensional OLAP) employs multi-dimensional arrays and pre-computed data cubes
• HOLAP (Hybrid OLAP) combines ROLAP and MOLAP, utilizing both relational and multi-dimensional storage

OLAP Operations

• Consolidation involves aggregating data across dimensions
• Drill-down allows navigating from summary data to detailed data
• Slicing and Dicing provide the ability to view specific subsets of data from different perspectives

Data Warehouse Modeling

• Star Schemas consist of a fact table connected to dimension tables
• Snowflake Schemas feature normalized dimension tables, forming a snowflake shape
• Fact Constellations involve multiple fact tables sharing dimension tables (galaxy schema)

Applications of Data Cubes

• Data Cubes are used for reporting, summarizing data across dimensions
• Data Cubes facilitate analytics: to identify trends and predict future outcomes
• Data Cubes aid in data mining to discover patterns and insights

Key concepts for data warehousing and OLAP. Covers relational vs. multi-dimensional databases, data warehouse characteristics (subject-oriented, integrated, time-variant, non-volatile), and different data warehouse models including enterprise data warehouses and data marts.