Data Warehousing & Data Mining

Chapter 3
Dimensional and Enterprise Data Modeling

Post Graduate Program in Data science
Data Warehousing & Data Mining   •   Post Graduate

Dimensional and Enterprise Data Modeling

Introduction and Context

Data warehousing is fundamentally a modeling discipline. A warehouse is not merely a large database populated from operational systems; it is an information environment designed to support analysis, comparison, historical interpretation, and organizational decision-making. Its model must make business events understandable to analysts while remaining sufficiently rigorous for integration, governance, and scalable implementation.

Dimensional modeling provides a business-oriented method for representing measurable events and the contexts in which those events occur. Enterprise data modeling provides the broader organizational perspective: it identifies shared business concepts, establishes integration rules, and coordinates multiple analytical domains. The two perspectives are complementary. Dimensional models optimize usability and query performance, whereas enterprise models promote consistency, reuse, and long-term architectural coherence.

This chapter examines conceptual, logical, and physical modeling; star, snowflake, and fact constellation schemas; grain; facts; dimensions; hierarchies; surrogate keys; slowly changing dimensions; and conformed dimensions. It also considers governance, implementation choices, and the trade-offs that arise in professional data warehouse projects.

Learning Objectives

By the end of this chapter, the reader should be able to:

  1. Distinguish conceptual, logical, and physical data models.
  2. Define the grain of a fact table before selecting measures.
  3. Design star, snowflake, and fact constellation schemas.
  4. Classify facts as additive, semi-additive, or non-additive.
  5. Design dimensions, hierarchies, surrogate keys, and role-playing dimensions.
  6. Select appropriate slowly changing dimension techniques.
  7. Explain the purpose of conformed dimensions and enterprise integration.
  8. Evaluate dimensional designs for correctness, usability, performance, and governance.

1. The Modeling Levels

1.1 Conceptual modeling

A conceptual model describes the major business objects and relationships without committing to database technology. Typical entities in a retail enterprise include Customer, Product, Store, Order, Promotion, and Date. At this level, the model answers questions such as:

  • What business processes matter?
  • What constitutes a customer or a sale?
  • Which entities are shared across departments?
  • What relationships and business rules exist?

Conceptual modeling is primarily a communication activity. Business stakeholders, data architects, and governance teams should be able to review it without knowing SQL or a particular cloud platform.

1.2 Logical modeling

A logical model translates business concepts into attributes, keys, relationships, and integrity rules, while remaining independent of a specific storage engine. It identifies candidate primary keys, foreign keys, optionality, cardinality, domains, and normalization requirements.

For a dimensional model, the logical design identifies a fact table and its dimensions. For example, a sales fact may contain references to Date, Product, Store, Customer, and Promotion, together with sales measures. The logical model should explicitly document the grain and the meaning of every measure.

1.3 Physical modeling

A physical model specifies implementation details, including:

  • Database tables and columns
  • Data types and precision
  • Indexes, partitions, clustering, and distribution keys
  • Compression and storage formats
  • Loading mechanisms and constraints
  • Security policies and access paths

A physical optimization must not change the business meaning of the logical model. For example, partitioning a fact table by transaction date may improve performance, but it does not alter the definition of a sale.

2. Enterprise and Dimensional Perspectives

An enterprise data model represents the information requirements of the organization as a whole. It seeks shared definitions for concepts such as customer, product, employee, supplier, location, and calendar. It may include subject areas, enterprise entities, canonical definitions, and integration rules.

Dimensional modeling begins with business processes rather than with organizational application boundaries. A process is an activity that generates measurable events, such as placing an order, shipping a product, receiving a payment, or recording an inventory balance. The model then identifies the dimensions that describe those events.

A useful enterprise architecture separates three concerns:

  1. Source systems: operational applications that capture transactions.
  2. Integration and transformation: processes that cleanse, reconcile, standardize, and historize data.
  3. Presentation layer: dimensional structures used for reporting, analytics, and data science.

This separation allows the warehouse to preserve stable business meaning even when source applications change.

3. Grain: The Foundation of Correct Design

The grain is the precise statement of what one row in a fact table represents. It must be declared before facts are selected. Examples include:

  • One row per product sold on one order line.
  • One row per account per day.
  • One row per shipment event.
  • One row per store, product, and day representing an end-of-day inventory snapshot.

The phrase “sales by product” is not a sufficient grain because it does not specify time, transaction detail, or the level at which quantities are recorded.

Grain determines which dimensions can be attached and which measures are valid. If the grain is one order line, order-level freight may be repeated on every line and therefore cannot safely be summed without allocation or separate modeling. Mixing order-line rows with monthly summary rows creates inconsistent aggregation and is a common source of double counting.

A professional design process therefore begins with a grain statement such as:

Each row represents one product line on one customer order, recorded at the time the order was accepted.

All dimensions and facts must be compatible with this statement. If multiple grains are required, separate fact tables should normally be created.

3.1 Declaring grain in practice

A grain specification should answer:

  • What event or snapshot does the row represent?
  • At what level of detail is it captured?
  • Which timestamp determines its analytical date?
  • What is the natural business identifier of the event?
  • Which measures are directly observable at that level?

The grain should also identify whether the fact is transactional, periodic snapshot, or accumulating snapshot.

4. Facts and Measures

A fact is a measurable observation associated with a business process. Common facts include quantity sold, extended price, tax amount, discount amount, duration, balance, and defect count. A fact table normally contains foreign keys to dimensions and numeric measures.

4.1 Types of facts

Additive facts can be summed across all relevant dimensions. Sales quantity and revenue are usually additive, assuming the currency and business definition are consistent.

Semi-additive facts can be summed across some dimensions but not others. An account balance may be summed across accounts and branches, but summing it across dates usually produces a meaningless result. Inventory balance is another typical example.

Non-additive facts cannot be meaningfully summed. Ratios, percentages, unit prices, and temperatures are common examples. Ratios should generally be recomputed from their underlying additive components rather than averaged across rows.

A weighted average price illustrates the issue. If revenue is R and quantity is Q, the overall unit price is:

P = R ÷ Q

where P is the overall price per unit. This is valid when revenue and quantity refer to the same population, currency, and time scope, and when Q ≠ 0. Averaging row-level prices without weighting can produce a misleading result.

4.2 Fact table categories

A transactional fact records one business event or event line. It preserves the greatest detail and supports flexible aggregation.

A periodic snapshot records the state of a process at regular intervals, such as daily inventory by product and warehouse. Snapshot facts are useful for balances and status measures.

An accumulating snapshot tracks a process with milestones, such as an insurance claim or order fulfillment. A row is created when the process begins and updated as milestones occur. This design is appropriate when the process has a known, limited sequence of stages.

4.3 Degenerate dimensions

A degenerate dimension is a dimension key, such as an order number or invoice number, stored directly in a fact table without a separate dimension table. It is useful when the identifier has analytical value but no meaningful descriptive attributes.

5. Dimensions and Their Structures

Dimensions provide the descriptive context used to filter, group, and interpret facts. Common dimensions include Date, Customer, Product, Geography, Employee, Supplier, and Promotion.

A dimension generally contains a surrogate key, business or natural key, descriptive attributes, and—where required—history-management metadata. Attributes should be selected according to their analytical usefulness, not merely copied from source systems.

5.1 Surrogate keys

A surrogate key is a warehouse-generated identifier with no business meaning. It is commonly an integer or other system-generated value. The fact table references the surrogate key rather than the source-system natural key.

Surrogate keys provide several benefits:

  • They isolate the warehouse from changes in source identifiers.
  • They permit multiple historical versions of the same business entity.
  • They support integration when different systems use different keys.
  • They allow special rows such as “Unknown,” “Not applicable,” or “Not yet available.”

A natural key remains valuable for matching incoming records and enforcing business uniqueness, but it should not usually serve as the historical foreign key in a dimensional warehouse.

5.2 Hierarchies

A hierarchy defines valid levels of aggregation. A product hierarchy may be Department → Category → Subcategory → Product. A geographic hierarchy may be Country → Region → City → Store.

Hierarchies support drill-down and roll-up. They should be based on explicitly governed relationships. A product should not be assigned to several categories unless the analytical design intentionally supports many-to-many relationships, perhaps through a bridge table.

A hierarchy can be balanced, in which every branch has the same number of levels, or ragged, in which branches have different depths. Ragged structures require special reporting treatment so that missing intermediate levels do not create misleading results.

5.3 Role-playing dimensions

A single dimension may be used several times in one fact table. The Date dimension may serve as Order Date, Ship Date, Delivery Date, and Return Date. These are role-playing dimensions: physically one dimension, logically several roles.

The roles must be named clearly in semantic models and reports. Otherwise, users may accidentally compare facts using the wrong date context.

5.4 Junk dimensions

A junk dimension combines low-cardinality flags and indicators, such as gift wrapping, online order, and loyalty membership, into one small dimension. This reduces numerous small columns in a fact table while retaining descriptive usability. It should not be used to hide unrelated, high-cardinality attributes.

6. Schema Patterns

6.1 Star schema

A star schema has a central fact table linked directly to denormalized dimension tables. The fact table contains foreign keys and measures; dimensions contain descriptive attributes and hierarchies.

The star schema is popular because it is easy to understand, requires relatively few joins, and works well with business intelligence tools. Denormalization deliberately introduces some redundancy in dimensions to make analytical queries simpler.

For example, a Sales fact may connect directly to Date, Product, Store, Customer, and Promotion. A query can group revenue by product category and region without navigating many normalized tables.

6.2 Snowflake schema

A snowflake schema normalizes some or all dimension hierarchies into additional tables. Product may be separated into Product, Subcategory, Category, and Department tables.

Snowflaking can reduce repeated attribute storage and improve control over shared hierarchical entities. However, it increases joins, can complicate self-service analysis, and may expose implementation details to users. It is most useful when dimensions are very large, hierarchies are independently governed, or reuse and maintenance outweigh query simplicity.

6.3 Fact constellation

A fact constellation, also called a galaxy schema, contains multiple fact tables that share dimensions. A retail warehouse might include Sales, Returns, Inventory Snapshot, and Shipment facts, all sharing Date, Product, Store, and possibly Customer.

The constellation supports integrated analysis across business processes. However, shared dimensions do not imply that facts can be joined directly. Facts usually have different grains, and joining them at row level can multiply records. Analysis should aggregate each fact to a compatible grain before combining results.

7. Conformed Dimensions and Enterprise Integration

A conformed dimension has consistent meaning, structure, keys, and attribute definitions wherever it is used. A conformed Date dimension uses the same calendar logic in sales, inventory, and finance. A conformed Product dimension uses governed product identifiers and classifications across processes.

Conformance enables cross-functional analysis. For example, sales revenue and inventory levels can be compared by the same product and date dimensions. Without conformance, similar-looking reports may use different definitions of product, fiscal period, or customer.

Conformance can occur at several levels:

  • Same dimension: identical attributes and keys.
  • Same domain: compatible values and definitions, even if physical implementations differ.
  • Same semantics: measures and attributes have explicitly aligned business meaning.

Enterprise data modeling should establish ownership for shared dimensions, change-control procedures, data-quality rules, and definitions of authoritative sources. A conformed dimension is therefore not merely a technical table; it is a governance commitment.

8. Slowly Changing Dimensions

Dimensions change. A customer may change address, a product may move to a new category, or an employee may transfer departments. Slowly changing dimension (SCD) techniques determine how those changes affect historical analysis.

8.1 Type 0: retain the original value

The original value is preserved permanently. This is suitable for attributes that should never be rewritten, such as an original enrollment date or birth date, subject to correction policies.

8.2 Type 1: overwrite

The old value is replaced by the new value. Type 1 is appropriate when history is not analytically relevant or when a correction should apply to all reports. It is simple but cannot answer historical questions based on the former value.

8.3 Type 2: create a new version

A new dimension row is created for each significant change. The row typically includes a surrogate key, natural key, effective-from date, effective-to date, and current-row indicator. Existing facts retain the old surrogate key, while new facts reference the new version.

A Type 2 dimension supports the question: “What was the customer’s classification when the transaction occurred?” The design must prevent overlapping effective periods for the same natural key and must define how boundary timestamps are handled.

8.4 Type 3: retain limited prior values

The current and a limited previous value are stored in the same row, such as Current Region and Previous Region. Type 3 is useful when the business needs only a narrowly defined comparison, but it does not preserve an unlimited history.

8.5 Hybrid approaches

Different attributes in one dimension may use different techniques. A customer address may be Type 2, while a spelling correction may be Type 1. The choice should be documented at attribute level and aligned with reporting requirements.

9. A Professional Modeling Method

A disciplined dimensional project can follow these steps:

  1. Identify business processes. Interview stakeholders and examine operational events, reports, and performance indicators.
  2. Declare the grain. Write a single, testable sentence for each fact table.
  3. Identify dimensions. Determine who, what, where, when, why, and how the event is described.
  4. Identify facts. Select measures observable at the declared grain and classify their additivity.
  5. Define keys and history. Select natural-key matching rules, surrogate-key strategy, and SCD treatment.
  6. Conform shared dimensions. Align definitions across fact tables and subject areas.
  7. Validate with scenarios. Test drill-down, roll-up, late-arriving data, corrections, and unknown members.
  8. Map to physical infrastructure. Choose partitioning, indexing, distribution, loading, and security mechanisms.
  9. Document metadata. Record definitions, lineage, owners, quality rules, and refresh expectations.

A design review should include both business and technical stakeholders. A schema that performs well but misrepresents business meaning is not successful; neither is a semantically correct design that cannot meet operational service levels.

10. Worked Modeling Example

Consider an online retailer that wants to analyze orders, returns, and inventory.

The first fact table is defined as follows:

One row represents one product on one accepted customer order.

Dimensions include Order Date, Product, Customer, Shipping Address, and Promotion. Measures include Quantity, Gross Sales Amount, Discount Amount, Tax Amount, and Net Sales Amount. Quantity and monetary amounts are additive across products, customers, and dates, provided that currencies are standardized or currency is included as a reporting dimension.

A return is a separate process. Its grain is:

One row represents one returned product line authorized on one return transaction.

Although Return and Sales share Product, Customer, and Date dimensions, their facts should not be joined directly at row level. A report comparing monthly sales and returns should first aggregate both facts by the conformed dimensions Month and Product, then compare the resulting summaries.

Inventory has a different grain:

One row represents the end-of-day inventory position for one product at one warehouse.

On-hand quantity is semi-additive across time. It may be summed across warehouses for a date, but summing daily balances across a month is generally not meaningful. A report requiring average monthly inventory must apply an explicitly defined business rule, such as the average of daily balances, rather than blindly summing rows.

This example demonstrates why grain, conformed dimensions, and fact additivity must be designed together.

11. Illustrative Case: Retail Enterprise Warehouse

Background

The fictional Meridian Retail Group operates physical stores and an online channel. Its sales, returns, product, loyalty, and inventory systems use different customer and product identifiers. Executives receive conflicting monthly reports because departments apply different product categories and fiscal calendars.

Problem

The organization wants to measure revenue, returns, inventory availability, and customer value across channels. The existing reports cannot reliably answer whether a decline in sales reflects lower demand, stock-outs, product reclassification, or changes in customer behavior.

Analysis

The architecture team identifies four processes: sales transactions, returns, daily inventory snapshots, and customer interactions. Workshops reveal that “customer” has different definitions in the loyalty and e-commerce systems. The team also discovers that product categories change over time and that the finance department uses a fiscal calendar that differs from the operational calendar.

Approach and solution

The team creates a fact constellation with Sales, Returns, Inventory Snapshot, and Customer Interaction facts. All facts use conformed Date and Product dimensions. A governed Customer dimension is introduced with cross-reference mappings from source identifiers. Product classification history is implemented using Type 2 rows, while corrected product descriptions use Type 1 treatment.

The Date dimension contains both calendar and fiscal attributes. Sales and returns use transactional grains; inventory uses a daily snapshot grain. A semantic layer exposes role-playing dates such as Order Date, Return Date, and Inventory Date. Unknown and late-arriving dimension members are supported through controlled default surrogate keys.

Outcome

The organization obtains consistent cross-channel reporting. Executives can distinguish sales decline from availability problems, while analysts can reproduce historical reports using the product classification valid at the time of each transaction. Data quality dashboards monitor unmatched products, missing dates, duplicate natural keys, and late-arriving records.

Learning points

The case illustrates that an enterprise warehouse is not produced by simply copying source tables. It requires process identification, grain discipline, conformed dimensions, historical policy, metadata, and governance. It also demonstrates that different facts must remain separate even when they share dimensions.

12. Physical Design, Performance, and Operations

Dimensional models are often implemented in columnar analytical databases or cloud data platforms. Physical design should reflect workload rather than follow universal rules.

Large fact tables may be partitioned by a date key when queries commonly restrict time ranges and when partition maintenance is manageable. Clustering or sorting can improve access to frequently filtered attributes. Distribution strategies should reduce data movement in massively parallel systems. Columnar storage and compression are generally effective for analytical workloads because queries often read a subset of columns.

Indexes, materialized views, aggregate tables, and semantic caches can improve response time, but each introduces maintenance cost. Physical optimization should be evaluated using representative workloads and execution plans. Premature aggregation can reduce flexibility and create reconciliation problems if definitions change.

ETL or ELT processes should support:

  • Deterministic surrogate-key assignment
  • Idempotent reprocessing
  • Late-arriving facts and dimensions
  • Duplicate detection
  • Reconciliation to source totals
  • Audit columns and load identifiers
  • Error quarantine and restartability

13. Governance, Security, and Ethical Considerations

Dimensional models influence organizational decisions, so their definitions require governance. Important controls include data ownership, business glossaries, lineage, quality thresholds, retention rules, and change approval.

Customer and employee dimensions may contain personal data. Access should follow purpose limitation and least privilege. Sensitive attributes may require masking, row-level security, encryption, tokenization, or restricted analytical views. Historical retention must comply with applicable privacy and sectoral regulations.

Historical modeling also has ethical implications. A Type 2 design can preserve outdated or sensitive classifications indefinitely. Organizations should establish retention and deletion policies, including how legally required deletion interacts with audit and reporting obligations. Analysts should be informed when measures are estimates, imputed, or affected by incomplete data.

AI and machine-learning systems depend heavily on dimensional consistency. Training features derived from a warehouse can suffer from leakage if future dimension values are joined to historical facts. Point-in-time correctness requires selecting the dimension version valid when the observation occurred. Thus, SCD design is directly relevant to trustworthy predictive analytics.

14. Challenges and Limitations

Dimensional modeling simplifies analysis but does not eliminate complexity. Important challenges include:

  • Ambiguous business definitions
  • Multiple grains within one subject area
  • Many-to-many relationships between facts and dimensions
  • Late-arriving dimensions and facts
  • Rapidly changing attributes
  • Source-system identifier conflicts
  • Conflicting fiscal calendars
  • High-cardinality dimensions
  • Reconciliation between detailed and summarized facts
  • Performance degradation caused by poorly chosen joins or distributions

No single schema is universally superior. Star schemas maximize usability in many settings, snowflake schemas can control complex shared hierarchies, and fact constellations support integrated process analysis. The correct choice depends on business requirements, platform capabilities, data volume, governance maturity, and user skill.

Best-Practice Checklist

Before implementation, verify that:

  1. Every fact table has a written grain statement.
  2. Every measure has a documented business definition and additivity classification.
  3. Dimensions have governed natural-key matching rules.
  4. Surrogate keys support history and unknown-member handling.
  5. Shared dimensions are demonstrably conformed.
  6. Hierarchies have explicit levels and valid relationships.
  7. SCD policy is defined for each historically relevant attribute.
  8. Different grains are not mixed in one fact table.
  9. Cross-fact comparisons aggregate to compatible grains.
  10. Metadata, lineage, security, retention, and quality controls are operationalized.

Chapter Summary

Dimensional and enterprise data modeling provide complementary foundations for analytical information systems. Conceptual models communicate business concepts; logical models define keys, relationships, and semantics; physical models implement those decisions on a chosen platform.

The grain of a fact table is the central design decision. It determines which dimensions and measures are valid and prevents double counting. Facts may be additive, semi-additive, or non-additive, and transactional, periodic snapshot, and accumulating snapshot designs serve different business processes.

Star schemas favor simplicity and usability, snowflake schemas normalize selected hierarchies, and fact constellations integrate multiple processes through shared dimensions. Surrogate keys isolate warehouse history from source-system change, while slowly changing dimension techniques determine how historical attribute changes are represented. Conformed dimensions establish consistent meanings across subject areas and enable enterprise analysis.

Successful implementation also requires physical optimization, metadata, data quality, security, privacy, and point-in-time correctness. A well-designed warehouse is therefore both a technical structure and a governed representation of organizational knowledge.

Key Terms

Grain: The precise meaning of one row in a fact table.

Fact: A measurable observation associated with a business process.

Dimension: Descriptive context used to filter, group, and interpret facts.

Additive fact: A measure that can be summed across all relevant dimensions.

Semi-additive fact: A measure that can be summed across some dimensions but not others.

Non-additive fact: A measure that should not be summed directly.

Star schema: A fact table directly connected to denormalized dimensions.

Snowflake schema: A schema with normalized dimension hierarchies.

Fact constellation: Multiple fact tables sharing conformed dimensions.

Surrogate key: A warehouse-generated identifier without business meaning.

Natural key: An identifier originating from a business or source system.

Hierarchy: Ordered levels used for drill-down and roll-up analysis.

Role-playing dimension: One dimension used in multiple analytical roles.

Degenerate dimension: A dimension identifier stored in a fact table without a separate dimension table.

Conformed dimension: A shared dimension with consistent definitions, structure, and meaning.

Slowly changing dimension: A strategy for managing changes in dimension attributes.

Transactional fact: A fact recording an individual event or event line.

Periodic snapshot: A fact recording process state at regular intervals.

Accumulating snapshot: A fact tracking milestones in a process lifecycle.

Point-in-time correctness: Use of values valid at the time of the measured observation.

Review Questions

Short Answer Questions

  1. What is the purpose of a grain statement?
  2. Distinguish additive, semi-additive, and non-additive facts.
  3. Why are surrogate keys useful in dimensional warehouses?
  4. What is a conformed dimension?
  5. Name two differences between a star schema and a snowflake schema.

Long Answer Questions

  1. Explain the conceptual, logical, and physical levels of data modeling and show how decisions at one level constrain decisions at the next.
  2. Compare transactional, periodic snapshot, and accumulating snapshot fact tables, including an appropriate example for each.
  3. Explain Type 0, Type 1, Type 2, and Type 3 slowly changing dimensions and discuss when each is appropriate.
  4. Discuss how conformed dimensions support enterprise-wide analysis and identify the governance problems that arise when dimensions are not conformed.
  5. Evaluate the advantages and limitations of star, snowflake, and fact constellation schemas for a large retail organization.

Analytical / Application Questions

  1. Design the grain, dimensions, and measures for a daily inventory fact table. Identify which measures are semi-additive and explain how monthly inventory should be calculated.
  2. A company joins order-line facts directly to shipment facts and obtains inflated totals. Diagnose the modeling problem and propose a correct analytical approach.
  3. A product changes category in July, but management requires reports showing both current category performance and historical category performance. Select appropriate SCD techniques and explain how the reports should use them.

Multiple Choice Questions with Answers

Question 1

What must be established before selecting the measures for a fact table?

A. The dashboard color scheme

B. The fact table grain

C. The physical index type

D. The report export format

Correct Answer: B. The fact table grain

Question 2

Which measure is typically semi-additive?

A. Units sold

B. Revenue

C. End-of-day account balance

D. Number of orders

Correct Answer: C. End-of-day account balance

Question 3

What is the principal purpose of a surrogate key in a dimension?

A. To encode a business rule in the key

B. To replace all descriptive attributes

C. To support warehouse history independently of source identifiers

D. To guarantee that facts are additive

Correct Answer: C. To support warehouse history independently of source identifiers

Question 4

Which schema normally has dimensions directly connected to one central fact table?

A. Star schema

B. Network schema

C. Document schema

D. Graph schema

Correct Answer: A. Star schema

Question 5

A new dimension row is created whenever a historically relevant attribute changes. Which SCD technique is being used?

A. Type 0

B. Type 1

C. Type 2

D. Type 3

Correct Answer: C. Type 2

Question 6

Why should sales and inventory facts generally not be joined directly at row level?

A. They must always use different databases

B. Their grains may differ and cause row multiplication

C. Facts cannot share dimensions

D. Inventory cannot contain numeric measures

Correct Answer: B. Their grains may differ and cause row multiplication

Question 7

Which statement best describes a conformed dimension?

A. A dimension used only by one report

B. A dimension with no surrogate key

C. A shared dimension with consistent meaning across processes

D. A dimension containing only numeric facts

Correct Answer: C. A shared dimension with consistent meaning across processes

Question 8

Which fact type is most appropriate for tracking order milestones such as placed, packed, shipped, and delivered?

A. Accumulating snapshot

B. Junk dimension

C. Periodic snapshot

D. Degenerate dimension

Correct Answer: A. Accumulating snapshot

Question 9

If total revenue is R and total quantity is Q, which expression gives the overall unit price when Q ≠ 0?

A. R × Q

B. Q ÷ R

C. R ÷ Q

D. R + Q

Correct Answer: C. R ÷ Q

Question 10

Which design is most appropriate when a single Date dimension is used as Order Date, Ship Date, and Delivery Date?

A. Role-playing dimension

B. Junk fact

C. Degenerate hierarchy

D. Type 0 fact

Correct Answer: A. Role-playing dimension

Data Warehousing & Data Mining
Chapter 3 of 15