Questions and Answers
What does dealing with changing dimension attributes refer to?
Dealing with changing dimension attributes involves managing the changes in characteristics of dimension data in databases.
What 3 types are there to change the dimension? (Select all that apply)
Explain the Type-1 dimension changing.
Overwriting the dimension attribute; it's easy to maintain and is often used for processing corrections.
What are the pros and cons of Type-1 dimension changing? (Select all that apply)
Signup and view all the answers
Explain the Type-2 dimension changing.
Signup and view all the answers
What are the pros and cons of Type-2 dimension changing? (Select all that apply)
Signup and view all the answers
Explain the Type-3 dimension changing.
Signup and view all the answers
What are the pros and cons of Type-3 dimension changing? (Select all that apply)
Signup and view all the answers
What is the rapidly changing large dimension problem?
Signup and view all the answers
How could we apply a Type-2 change in the context of rapidly changing dimension tables?
Signup and view all the answers
What are mini-dimensions?
Signup and view all the answers
Which entities might have rapidly changing large dimensions? (Select all that apply)
Signup and view all the answers
The logical data model is usually structured as a _____. Why?
Signup and view all the answers
What is a fact table?
Signup and view all the answers
A fact table holds...... A dimension table stores.....
Signup and view all the answers
The fact table consists of ____ types of columns. Name and describe them.
Signup and view all the answers
What is an issue with performing relational queries using the star schema?
Signup and view all the answers
Define Measures.
Signup and view all the answers
What are the constructs of a STAR Schema?
Signup and view all the answers
Define Dimensions.
Signup and view all the answers
Define facts.
Signup and view all the answers
Define Granularity.
Signup and view all the answers
What is an OLAP Cube?
Signup and view all the answers
What is an issue with the OLAP Cube?
Signup and view all the answers
A ____________ _____________ is a good relational DB for building a ______________ ____________________.
Signup and view all the answers
OLAP cubes offer _____.
Signup and view all the answers
What are key decisions made during the design of a dimensional model?
Signup and view all the answers
What are Additive Measures?
Signup and view all the answers
What are Semi-Additive Measures?
Signup and view all the answers
What are Non-additive measures?
Signup and view all the answers
What 2 types of fact nodes/tables are there?
Signup and view all the answers
What is a Transactional Fact Node?
Signup and view all the answers
What is a Snapshot Fact Node?
Signup and view all the answers
Snapshots usually only have....
Signup and view all the answers
A _____________ fact node represents events occurring at a point in time.
Signup and view all the answers
Does a transactional or snapshot fact node have more dimensions?
Signup and view all the answers
Does a transactional or snapshot fact node have more measures?
Signup and view all the answers
What is a Surrogate Key? Why are they important?
Signup and view all the answers
Smaller surrogate keys translate into.....
Signup and view all the answers
Databases adopt an entity relationship diagram model, while data warehouses adopt a.....
Signup and view all the answers
What is a multidimensional data model?
Signup and view all the answers
Explain Online Analytical Processing (OLAP).
Signup and view all the answers
Who has access to databases vs. data warehouses?
Signup and view all the answers
Data warehouses provide..... Databases provide....
Signup and view all the answers
Data warehouses manage _____________ data. Databases manage ______________ data.
Signup and view all the answers
Which contains different levels of granularity?
Signup and view all the answers
While data warehouses support complex transactions, databases can only do....
Signup and view all the answers
Transactions for data warehouses are:
Signup and view all the answers
What are Senior Business Management Sponsor(s)? Why are they important?
Signup and view all the answers
What is Compelling Business Motivation?
Signup and view all the answers
Define feasibility.
Signup and view all the answers
What's a good economical justification for developing a data warehouse?
Signup and view all the answers
When end-users are using the data warehouse, what is the main challenge? What's a potential solution?
Signup and view all the answers
What should be the first step when developing a data warehouse?
Signup and view all the answers
What are the 4 Steps to the Dimensional Modeling Design Process?
Signup and view all the answers
Explain 1. Select business process.
Signup and view all the answers
Explain 2. Declare granularity.
Signup and view all the answers
Explain 3. Identify Dimensions.
Signup and view all the answers
Explain 4. Identify Facts.
Signup and view all the answers
What question should you ask to determine if an entry is a fact or not?
Signup and view all the answers
What are dimensions?
Signup and view all the answers
What are the 2 main purposes of dimensions?
Signup and view all the answers
Define understandability regarding dimension modeling.
Signup and view all the answers
What's the effect on query performance when adding dimensions?
Signup and view all the answers
How should a dimension modeling/design be extensible?
Signup and view all the answers
What do we use if no property of the fact can fulfill the purpose of the primary key?
Signup and view all the answers
Although surrogate keys seem like they're replacements for primary keys, all DWs should....
Signup and view all the answers
What is Normalization? Why do it?
Signup and view all the answers
What is snowflaking?
Signup and view all the answers
What are disadvantages of normalization/snowflaking?
Signup and view all the answers
What are the 3 Measure Types?
Signup and view all the answers
Describe Additive Measures.
Signup and view all the answers
Describe Semi-Additive Measures.
Signup and view all the answers
Describe Non-Additive Measures.
Signup and view all the answers
What is a Periodic/Snapshot Fact Table?
Signup and view all the answers
Measures in the fact table represent an event that..... Measures in the fact node represent.....
Signup and view all the answers
Periodic fact tables allow one to measure the __________ of an organization.
Signup and view all the answers
In a PFT, here is _______ fact table row per ________ __________.
Signup and view all the answers
In a TFT, there are more ________________, but less _______________ than a PFT. Why?
Signup and view all the answers
What is a Factless Fact Table?
Signup and view all the answers
What is an example of a Factless Fact Table being needed?
Signup and view all the answers
How do you determine events that did not occur?
Signup and view all the answers
SURROGATE KEYS SHOULD BE -__ INTEGERS.
Signup and view all the answers
How do surrogate keys increase performance?
Signup and view all the answers
Study Notes
Data Warehousing Concepts
- The logical data model structure commonly uses a Star Schema due to its user-friendly navigation and better performance in relational queries.
- A fact table serves as the central table in a data warehouse, containing quantitative information for analysis, often stored in a denormalized format.
- Fact tables hold the data to be analyzed while dimension tables provide context about how the data can be analyzed.
- Fact tables consist of two types of columns:
- Foreign key columns for joining with dimension tables.
- Measures columns containing data to be analyzed.
- Access time can be an issue when performing relational queries using star schema structures.
- Measures are core elements in the dimensional model, representing quantitative values, and can be summed, averaged, or manipulated mathematically.
Star Schema Constructs
- A Star Schema includes:
- Fact Node: Central table with measures.
- Dimension Node: Dependent tables that provide context.
- Dimension Edge: Relationships between dimension tables and facts.
- Dimensions are companion tables containing characteristics that describe facts.
- Facts are specific measurements about the entity being managed, e.g., financial metrics like totals or success rates.
Granularity and OLAP
- Granularity refers to the level of division of an entity into attributes, impacting data detail.
- An OLAP Cube is a three-dimensional array that allows for complex data analysis.
- Data sparsity is a common issue with OLAP cubes, often resulting in many empty cells.
Measure Types
- Three types of measures:
- Additive: Can be summed across all dimensions (e.g., total sales).
- Semi-Additive: Can be summed in some dimensions only (e.g., account balances).
- Non-additive: Cannot be summed at all (e.g., percentages).
Fact Tables
- Two types of fact nodes:
- Transactional Fact Nodes: Represent events at a specific time (e.g., retail sales).
- Snapshot Fact Nodes: Represent data snapshots at the end of a period (e.g., monthly account balances).
- A periodic/snapshot fact table summarizes measurement events over standard time periods, whereas transactional tables capture every single transaction event.
Data Warehouse vs. Database
- Databases focus on current data and support operational transactions, while data warehouses manage historical data and offer insights for decision-making.
- Data warehouses often contain multiple granularity levels compared to databases.
Project Planning and Design
- Senior Business Management Sponsors are essential for funding and utilizing the data warehouse.
- Compelling Business Motivation aligns the data warehouse with strategic goals.
- Feasibility emphasizes the importance of clean data for operational success.
- Developing a data warehouse involves understanding user requirements and focusing on dimensions and measures.
Dimensional Modeling
- The four-step design process for dimensional modeling:
- Select business processes.
- Declare the granularity of data attributes.
- Identify dimensions providing context.
- Identify facts representing quantitative data.
Change Management in Dimensions
- Types of dimension changes:
- Type-1: Overwrites existing dimensions (loses historical data).
- Type-2: Adds a new record for changes (keeps historical data but uses more space).
- Type-3: Adds new attributes/columns to keep current and previous values while minimizing new rows.
Surrogate Keys
- Surrogate keys are system-generated unique keys beneficial for identifying rows in fact tables where no single primary key is available. They enhance performance through efficient indexing and joining.
- Dealing with attributes in data dimensions often requires consideration of whether to overwrite, add, or change attributes to maintain data integrity and historical context.### Rapidly Changing Large Dimension Problem
- Involves extremely large dimensions where attributes undergo constant change.
- Affects maintenance and performance as dimensions grow, especially when using Type-2 changes to track historical data.
- Relevant examples include:
- Government agencies managing dimensions with over 100 million people.
- Large retail stores with several million product entries.
- Insurance companies maintaining millions of automobile records.
Type-2 Change Application
- Type-2 changes involve adding new records to capture historical data.
- Implemented through the use of mini-dimensions to manage attributes that frequently change.
Mini-Dimensions
- Mini-dimensions isolate rapidly changing attributes of a larger dimension.
- These mini-dimensions function as stand-alone entities and connect directly to fact tables without snowflaking, optimizing database structure.
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.
Description
Test your knowledge of data warehousing concepts with these flashcards. Covering key elements such as star schema and fact tables, this quiz helps reinforce your understanding of data models and their structures. Ideal for students and professionals in data management.