Riassunti VERIFICATO
Riassunto Prima Parte
193 visualizzazioni
35 download
Nessun voto ancora
Di cosa parla
Riassunto AI · si legge in un minuto
This document outlines Data Warehouse (DW) design principles, contrasting Top-Down (Inmon, atomic data) and Bottom-Up (Kimball, data marts, star schema) approaches. It delves into conceptual and logical design, covering dimensional modeling with facts, dimensions, and measures, alongside schema types like Star and Snowflake. Key concepts of the ETL (Extraction, Transformation, Loading) process and Online Analytical Processing (OLAP) for multidimensional analysis are explained. The physical design section details various indexing strategies such as B+tree, bitmap, join, and hash structures, and the use of materialized views to optimize DW performance and query efficiency for data science and engineering applications.
Leggi il riassunto completo
- Data Warehouse Design Approaches:
- Top-Down (Inmon): Views a Data Warehouse as a subject-oriented, time-variant, non-volatile, and integrated repository for enterprise data, validated and stored in a normalized database. Stores "atomic" information. Advantages include easy data mart creation, but it can be inflexible to changing departmental needs and has high implementation costs.
- Bottom-Up (Kimball): Describes a Data Warehouse as a copy of transaction data specifically architected for query and analysis, often using a star schema. Data marts are created first for particular business processes. This approach is business-driven. Advantages include quick document generation and extendability to new business units.
- Conceptual & Logical Design:
- Facts: Descriptions of relevant events for the company.
- Measures: Aggregatable streams (e.g., sum, min, max), levels (not additive over time), and units.
- Aggregate Operators: Categorized as distributive (sum, min, max), non-distributive (average), and olistic (median).
- Factless Fact Schema: Used for events not characterized by measures, typically to count occurrences.
- Time: Can be a snapshot, a direct relationship to a time dimension, or mapped simultaneously.
- Data Volume: Estimation is crucial, depending on cardinality and sparsity.
- Star vs. Snowflake Schema: Star schema requires more memory but offers easier joins; Snowflake is better for materialized views.
- Multiple Edges: Can be resolved using bridge tables or by pushing down a boolean list into the fact table.
- Degenerate Dimensions: Dimensions with a single attribute, easily implementable within the fact table.
- Junk Dimensions: Can contain several degenerate dimensions, feasible for small cardinality.
- Data Warehouse: Data Analysis (OLAP):
- Online Analytical Processing (OLAP) is an approach to answer multi-dimensional analytical (MDA) queries swiftly.
- Controlled Query Environment: Encompasses complex queries and ad-hoc analysis for predefined reports, requiring ad-hoc code development.
- Ad Hoc Environment: Arbitrary OLAP queries can be designed on demand by users. An OLAP session allows successive refinements of the same query, useful when predefined queries are insufficient.
- OLAP Operators: Roll up, drill down, slice and dice, pivot, and sort. SQL extensions allow new aggregate functions based on computation windows and rank calculations.
- ETL Process (Extraction, Transformation, Loading):
- Data preprocessing before being loaded into the Data Warehouse.
- Extraction: Depends on how operational data is collected (historical, partly historical, or transient). Incremental extraction captures data modifications (e.g., via ad hoc applications, triggers, or timestamps).
- Data Cleaning: Addresses issues like duplicates, missing data, and unexpected values using data dictionaries, approximate fusion, and prevention techniques.
- Transformation: Converts operational data into warehouse format (formatting).
- Physical Design:
- Dependent on the workload; involves selecting suitable data structures to support the most frequent queries.
- Structures: Bitmap indexes, join indexes, bitmapped join indexes, B+trees, and materialized views.
- Indexing:
- B+tree: Provides direct access based on key field values, efficient for range queries, but susceptible to updates and deletes.
- Bitmap Index: Stores relationships as a boolean matrix, very efficient for boolean predicates but inappropriate for continuous attributes.
- Join Index: Precomputes joins between two or more tables (star index for multiple tables), efficient for column-based joins but can require significant storage space.
- Hash Structure: Guarantees direct and efficient access to data based on a key field via a hash function, very efficient for equality predicates, but inefficient for range queries.
Riassunto generato con l'intelligenza artificiale a partire dagli appunti. Come lo facciamo
Stai vedendo l'anteprima delle prime pagine. Il file completo è gratis: registrati per leggerlo tutto.
Studia questi appunti con l'AI
Riassunto e domande d'esame sono gratis: chiedili e ti mandiamo un'email quando sono pronti.
In preparazione: di solito è pronto in giornata.
Questo appunto è gratis. Registrati in 30 secondi per leggere tutte le pagine e scaricarlo.
Simula l'esame con domande costruite su questi appunti.
Di cosa parla l'insegnamento
Data management and visualization · 1° anno · 1° semestre · 8 CFU · ING-INF/05
Knowledge of data warehouse architecture and of the methodology for conceptual, logical, and physical design of a data warehouse. - Ability to design a data warehouse. - Knowledge of the SQL statements for OLAP queries in a data warehouse. - Ability to write OLAP queries by means of the SQL language. - Knowledge of the…