Dimensional Data Modeling

Introduction and Context

Dimensional data modeling is the principal design discipline used to organize data for analytical reporting, business intelligence, and decision support. Unlike highly normalized operational models, which are optimized for transaction integrity and update efficiency, dimensional models are optimized for understandable queries, predictable performance, and consistent business interpretation.

A dimensional model represents measurable business events as facts and the descriptive context surrounding those events as dimensions. The resulting structures commonly take the form of star schemas, snowflake schemas, fact constellations, and specialized designs such as factless fact tables and bridge tables.

The central design decision is not the choice of database platform. It is the definition of the grain: the precise meaning of one row in a fact table. If grain is ambiguous, measures may be double-counted, dimensions may be attached incorrectly, and analytical results may be unreliable. Consequently, dimensional modeling is both a technical and a semantic activity. It requires the designer to understand business processes, measurement rules, organizational definitions, historical change, and governance requirements.

Learning Objectives

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

1. Define and validate the grain of a dimensional fact table.

2. Distinguish facts, dimensions, measures, keys, and attributes.

3. Design star, snowflake, and fact constellation schemas.

4. Apply conformed dimensions across business processes.

5. Select additive, semi-additive, and non-additive measures appropriately.

6. Use surrogate keys, factless facts, bridge tables, and hierarchy structures.

7. Design slowly changing dimensions and temporal models.

8. Evaluate dimensional designs for correctness, performance, maintainability, and governance.

1. The Dimensional Modeling Perspective

A dimensional model begins with a business process rather than with a list of existing source tables. Examples of business processes include sales, shipment, payment, enrollment, claims processing, website interaction, and inventory movement. The designer asks four questions:

• What process is being measured?
• What is the grain of measurement?
• Which dimensions describe the event?
• Which facts quantify the event?

A useful design principle is: declare the grain first, then identify dimensions and facts. Dimensions describe the who, what, where, when, why, and how of a business event. Facts record numerical measurements or event occurrence.

For example, a retail sales fact may have the grain “one row per product per transaction line.” Its dimensions might include date, store, customer, promotion, and product. Its facts might include quantity sold, gross sales amount, discount amount, tax amount, and cost amount.

The grain must be stated at the lowest level needed for the intended analysis. A fact table at daily product-store grain cannot answer questions about individual receipts unless receipt-level data is retained elsewhere. Conversely, a receipt-line model can generally support daily summaries through aggregation, although it may require more storage and processing.

Important Terminology

• Business process: An organizational activity being measured.
• Grain: The exact meaning of one fact-table row.
• Fact table: A table containing measurements and foreign keys to dimensions.
• Dimension table: A table containing descriptive attributes used for filtering, grouping, and labeling.
• Measure: A numerical or event-based value recorded in a fact table.
• Dimensional key: A key used to identify a dimension member.
• Degenerate dimension: A dimension identifier stored directly in a fact table without a separate dimension table.
• Role-playing dimension: One physical dimension used in multiple semantic roles, such as order date and shipment date.
• Conformed dimension: A dimension whose definitions and keys are consistent across fact tables.

2. Grain: The Foundation of Correctness

Grain is a statement about what a row represents, not merely a description of columns. “Sales by product” is incomplete because it does not specify whether a row represents a product-day, product-store-day, invoice line, or customer-product-month.

A rigorous grain statement should identify:

1. The business event or state being represented.

2. The entities participating in the event.

3. The time precision.

4. Any conditions or scope limitations.

Examples include:

• One row per product per line item on a customer order.
• One row per account per day showing end-of-day balance.
• One row per student per course registration.
• One row per aircraft inspection event.

Grain and Additivity

The grain determines how measures may be aggregated. If a sales fact is recorded at order-line level, quantity and sales amount are usually additive across order lines, products, stores, and time. A balance fact recorded at daily account level is additive across accounts but generally not across time; summing daily balances produces a mathematically valid total but often a meaningless business result. Such a measure is semi-additive.

A ratio such as profit margin is normally non-additive. It should be calculated from additive components:

Profit margin = Total profit / Total sales

This is preferable to averaging row-level margins because a simple average gives equal weight to transactions of different sizes. The formula assumes sales are nonzero and that profit and sales use compatible monetary definitions and periods.

Mixed-Grain Errors

A fact table should not combine rows with different grains. For example, combining order-line sales rows with monthly budget rows creates incompatible records. A query joining them may multiply budget values by the number of sales rows. Separate fact tables should be created, with shared conformed dimensions used for comparison.

3. Facts and Measures

Facts are commonly classified according to their behavior during aggregation.

Additive Facts

An additive fact can be summed across all relevant dimensions. Examples include units sold, revenue, discount amount, and shipping weight, provided their units and business definitions are consistent.

Semi-Additive Facts

A semi-additive fact can be summed across some dimensions but not others. Account balance can be summed across accounts and branches, but not across dates. Appropriate temporal operations include end-of-period selection, beginning-of-period selection, average balance, or minimum and maximum balance, depending on the business question.

Non-Additive Facts

Non-additive facts cannot be meaningfully summed. Percentages, ratios, rates, and rankings are typical examples. Store numerator and denominator components whenever possible, and calculate the ratio at query time.

Transaction, Periodic Snapshot, and Accumulating Snapshot Facts

A transaction fact table records individual events, such as each sale or payment. It is appropriate when event-level detail is important.

A periodic snapshot fact table records the state of a process at regular intervals, such as daily inventory or monthly account balances. It provides a consistent view of conditions over time.

An accumulating snapshot fact table records a process with milestones, such as an insurance claim or purchase order. A row may contain dates for submission, approval, fulfillment, and closure. The row is updated as the process advances. This design is useful for measuring cycle time, but it requires careful treatment of late events and revisions.

4. Dimensions and Their Design

Dimension tables provide descriptive context. They are generally wider and less normalized than operational master tables. A product dimension may include product name, brand, category, package size, supplier, department, and marketing classification.

A dimension should contain attributes that support understandable analysis. Attribute names should be business-oriented, stable, and documented. Codes from source systems may be retained for traceability, but they should not automatically become the primary analytical key.

Surrogate Keys

A surrogate key is a warehouse-generated identifier, usually an integer or equivalent system-generated value. It is used instead of relying on a source-system natural key.

Surrogate keys provide several benefits:

• They isolate the warehouse from source-system key changes.
• They support multiple source systems with overlapping identifiers.
• They permit multiple historical versions of one business entity.
• They allow special members such as “Unknown,” “Not applicable,” and “Pending.”
• They can improve join consistency and simplify integration.

A dimension may retain the natural key as an attribute for lineage and reconciliation. Surrogate keys should not be confused with opaque values that lack governance; their assignment and lifecycle must be controlled.

Special Dimension Members

Most dimensions should define controlled members for exceptional conditions. For example:

• Unknown: the source value is missing or not yet identified.
• Not applicable: the dimension does not apply to the event.
• Invalid: the source value failed validation.
• Not yet available: the value is expected but has not arrived.

These members are preferable to null foreign keys because they preserve referential integrity and make data-quality conditions visible.

Role-Playing Dimensions

A date dimension may be joined to a fact table as order date, shipment date, payment date, and cancellation date. These are semantic roles of one physical dimension. In a semantic layer, each role should have a clear name so that users do not confuse them.

Degenerate Dimensions

An order number or invoice number may have analytical value even when it has no additional descriptive attributes. Such an identifier can remain in the fact table as a degenerate dimension. It supports counting distinct orders and tracing records without creating a dimension table containing only one identifier.

5. Star, Snowflake, and Fact Constellation Schemas

Star Schema

A star schema contains a central fact table directly connected to relatively denormalized dimension tables. Its advantages include simple queries, clear semantics, fewer joins, and good compatibility with reporting tools. Its principal disadvantage is redundancy in dimension attributes.

A simplified sales star may contain:

• FactSales: date key, product key, store key, customer key, order number, quantity, sales amount, discount amount, and cost amount.
• DimDate: date, month, quarter, year, fiscal period, holiday indicator.
• DimProduct: product, brand, category, department, supplier.
• DimStore: store, city, region, country, format.
• DimCustomer: customer segment, geography, membership status.

Snowflake Schema

A snowflake schema normalizes portions of dimensions. Product may be separated into product, brand, category, and department tables. This can reduce redundancy and support centralized maintenance, but it increases joins and may make the model less intuitive for users.

Snowflaking can be justified when hierarchies are complex, shared, very large, or subject to independent governance. It should not be applied automatically merely because normalization is familiar from transactional database design.

Fact Constellation

A fact constellation, or galaxy schema, contains multiple fact tables sharing dimensions. For example, sales, inventory, and returns may share date, product, and store dimensions. This arrangement supports cross-process analysis, provided the grains and measures remain distinct.

6. Conformed Dimensions and Enterprise Consistency

A conformed dimension has the same business meaning, attribute definitions, and compatible keying across fact tables. A conformed date dimension ensures that “month,” “quarter,” and “fiscal year” mean the same thing in sales, inventory, and finance analyses.

Conformance may occur at several levels:

• Key conformance: the same dimension member is identified consistently.
• Attribute conformance: names and values have common definitions.
• Semantic conformance: business rules, such as customer classification, are shared.
• Subset conformance: one dimension may contain a valid subset of another while preserving definitions.

Conformed dimensions enable drill-across analysis. For example, sales and inventory can be compared by product, store, and date. However, drill-across queries must aggregate each fact table to a common dimensional grain before joining. Directly joining detailed fact tables can produce a many-to-many multiplication error.

Conformance requires governance. A data warehouse team should maintain definitions, ownership, acceptable values, change procedures, and certification status for shared dimensions.

7. Hierarchies and Advanced Relationships

Hierarchies enable navigation from detailed to summarized levels. A geographic hierarchy might be store → city → region → country. A product hierarchy might be SKU → brand → category → department.

Balanced and Ragged Hierarchies

A balanced hierarchy has the same number of levels for every member. A ragged hierarchy has missing or variable levels, as occurs when some organizational units report directly to a regional office while others report through intermediate divisions.

Ragged hierarchies can be represented using parent-child structures, flattened level columns, bridge tables, or path attributes. The choice depends on query requirements and platform capabilities.

Parent-Child Hierarchies

In a parent-child dimension, each member stores a parent identifier. This is flexible for organizational charts but can be difficult for aggregation and performance. Materialized paths, hierarchy bridge tables, or periodically flattened reporting structures may improve usability.

Many-to-Many Relationships and Bridge Tables

A normal dimension-to-fact relationship assumes one dimension member applies to a fact row. Many-to-many relationships occur when an event has multiple valid dimension members. Examples include a sale credited to several sales representatives, a patient associated with multiple care programs, or a product belonging to multiple categories.

A bridge table resolves this relationship. It generally contains the fact-side identifier, dimension member key, and sometimes an allocation factor. If a revenue amount is allocated among representatives, the allocation factors should be governed so that their sum equals one for each event, unless the business intentionally permits over-allocation.

Bridge tables can cause double counting if users aggregate the original measure without applying allocation logic. Semantic-layer measures and documentation are therefore essential.

Factless Fact Tables

A factless fact table records that an event or relationship occurred without a numeric measure. Examples include student attendance, customer eligibility, promotion coverage, and room occupancy. Counts of rows can answer questions such as “How many students attended?” or “Which stores were covered by the promotion?”

Two broad types are event factless tables, which record occurrences, and coverage factless tables, which record possible or planned relationships. The distinction matters because absence in an event table may mean non-occurrence, whereas absence in a coverage table may indicate that coverage was never defined.

8. Slowly Changing Dimensions

Dimensions change over time. A customer may move, change segment, or alter membership status. A product may be reclassified. The model must specify whether reports should reflect current values or historical values as they were known at the time of the event.

Type 0: Retain Original Value

The original value is never changed. This is appropriate for immutable attributes such as original birth date, although corrections may require governance exceptions.

Type 1: Overwrite

The existing value is replaced. Type 1 is simple and appropriate when history is irrelevant or data correction is required. Historical reports will show the new value even for old facts.

Type 2: Create a New Version

A new dimension row is created for each relevant change. Fact rows use the surrogate key corresponding to the version valid at the event time. Common columns include effective start date, effective end date, current-row indicator, and version number.

A typical Type 2 rule is:

• New version start date = change effective date.
• Previous version end date = one instant before the new version starts, or an exclusive boundary equal to the new start date.
• Current indicator identifies the active version.

Using half-open intervals—valid from start date inclusive to end date exclusive—reduces ambiguity at boundaries. The model must also define how late-arriving events are handled.

Type 3: Limited Previous Value

A current value and a previous value are stored in the same row. This supports limited comparison but does not preserve an unlimited history.

Hybrid Methods

Real implementations often combine methods. A customer segment may use Type 2, while a spelling correction may use Type 1. The decision should be made attribute by attribute, based on reporting requirements, regulatory obligations, and source reliability.

9. Temporal Modeling and Late-Arriving Data

Temporal modeling distinguishes at least two time concepts:

• Valid time: when a fact or attribute is true in the business world.
• Transaction or system time: when the warehouse recorded the information.

A record may arrive after its valid date. For example, a correction received on 10 May may state that a customer changed address on 1 April. A robust warehouse preserves both dates when auditability matters.

Late-arriving dimensions create a key-resolution problem. A fact may arrive before its dimension member. Common approaches include inserting an inferred member with limited attributes, loading the fact against an “unknown” member and later restating it, or delaying fact processing. The best choice depends on reporting latency and restatement policy.

Temporal designs must address time zones, daylight-saving transitions, fiscal calendars, effective dating, corrections, and retention requirements. A date dimension and a timestamp strategy should be designed together rather than treated as unrelated technical details.

10. Worked Design Example

Consider a university that wants to analyze course registrations, attendance, and student outcomes.

Business Requirements

Administrators need to answer:

• How many students registered for each course and term?
• What is attendance by student, course, and date?
• How do outcomes vary by faculty and program?
• What was the student’s program classification at the time of registration?

Grain Decisions

Three processes should be modeled separately:

1. Registration fact: one row per student per course section per registration event.

2. Attendance factless fact: one row per student per section per class meeting when attendance is recorded.

3. Outcome fact: one row per student per course attempt after final assessment.

Combining these facts would create mixed grain and duplication. A student may attend many meetings, while a course attempt has one final outcome.

Dimensions

Shared dimensions include Date, Student, Course, Faculty, Program, and Academic Term. Student may be a Type 2 dimension if program classification must be historically accurate. Course may use Type 2 when department ownership changes affect historical reporting.

The registration fact may contain registration status, credit value, and registration timestamp. The outcome fact may contain grade points and pass indicator. Attendance requires no numerical measure, although a row count can represent attendance events.

Analytical Caution

To calculate the attendance rate, the institution needs both attended meetings and expected meetings. Counting attendance rows alone measures attendance events, not attendance opportunity. A coverage or scheduled-meeting fact may therefore be needed to represent the denominator. This illustrates how business meaning, not merely available data, determines model completeness.

11. Illustrative Case: Retail Analytics Modernization

This is a fictional illustrative case.

Background

Northstar Retail operates 180 stores and an online channel. Its operational systems use different product identifiers and customer classifications. Executives report that online sales, store sales, returns, and inventory figures cannot be reconciled.

Problem

The existing warehouse joins store sales and inventory directly at transaction level. Inventory values are duplicated for products with multiple sales transactions. Customer segments also change without preserving historical versions, causing prior-year reports to change whenever customer attributes are updated.

Analysis

The team identifies three distinct grains:

• One row per order line for sales.
• One row per product per location per day for inventory snapshots.
• One row per returned order line for returns.

The team also discovers that product identifiers differ across systems and that online and store channels use inconsistent definitions of “region.”

Approach and Solution

The warehouse introduces a conformed Product dimension with surrogate keys, a conformed Date dimension, and a conformed Location dimension. Sales and returns remain transaction facts, while inventory becomes a periodic snapshot. Product category changes use Type 2 history; spelling corrections use Type 1 overwrite. Separate channel attributes are included in the location or sales context according to the declared grain.

Inventory is first aggregated to product-location-day before comparison with daily sales. The semantic layer defines margin from total revenue, cost, and returns rather than averaging transaction-level margins. Unknown and late-arriving product members are explicitly managed.

Outcome

Executives can compare sales and inventory without duplication, reproduce historical customer and product classifications, and reconcile online and store reporting. Query performance improves because common dimensions and pre-aggregated snapshots reduce unnecessary joins.

Learning Points

The main improvement did not come from a particular database feature. It came from declaring grain, separating business processes, conforming dimensions, and defining historical behavior. The case also demonstrates that a dimensional model is an institutional agreement about meaning as much as it is a physical schema.

12. Implementation, Governance, and Quality Assurance

A professional dimensional modeling project should include the following controls:

• A grain statement for every fact table.
• A data dictionary for facts, dimensions, and calculated measures.
• Source-to-target mappings and lineage documentation.
• Uniqueness tests for dimension keys and grain-level fact keys where applicable.
• Referential-integrity tests for foreign keys.
• Reconciliation of totals against authoritative source systems.
• Tests for duplicate facts, missing dimensions, late arrivals, and invalid dates.
• Explicit treatment of nulls, unknown members, corrections, and restatements.
• Monitoring of load latency, row counts, rejected records, and historical changes.

Performance Considerations

Performance depends on platform, workload, data volume, and physical design. Useful techniques may include columnar storage, partitioning by a suitable date or event key, clustering, aggregate tables, materialized views, compression, and workload-aware indexing. These techniques should support a correct logical model; they should not be used to conceal ambiguous grain or poor joins.

Security and Privacy

Dimensions often contain personally identifiable information. Access controls should protect sensitive attributes while preserving authorized analytical capability. Techniques include role-based security, row-level security, masking, tokenization, pseudonymization, and separate restricted dimensions. Historical Type 2 records can retain sensitive information longer than operational systems, so retention and deletion policies must be aligned with applicable law and institutional policy.

Data minimization is important: not every source attribute belongs in an analytical dimension. Governance should define ownership, permitted use, retention, correction procedures, and auditability.

AI and Contemporary Developments

AI-assisted modeling tools can infer relationships, suggest dimensions, identify duplicate records, and generate transformation code. Such suggestions must be reviewed by domain experts because automated inference may mistake technical correlation for business meaning. Large language models can assist with documentation and test generation, but they cannot independently validate grain, regulatory interpretation, or measure definitions.

Modern cloud platforms increasingly support lakehouse architectures, semantic layers, data products, and open table formats. These developments do not eliminate dimensional modeling. Instead, dimensional concepts may be implemented as curated analytical products, governed semantic models, or marts over shared storage. The need for conformed definitions and controlled history remains.

Challenges and Limitations

Dimensional models simplify analysis, but they have limitations. Wide dimensions may contain redundancy. Type 2 history increases storage and complicates joins. Many-to-many relationships can produce confusing aggregations. Snapshot facts can be large, while transaction facts may be too detailed for some reporting workloads. Different departments may resist conformed definitions because local classifications support legitimate operational purposes.

A dimensional model also represents selected business perspectives rather than reality in its entirety. Its quality depends on source completeness, governance, change management, and user education. A technically elegant schema cannot compensate for unclear definitions of revenue, customer, active account, attendance, or profitability.

Best-Practice Design Sequence

A practical design sequence is:

1. Identify the business process and analytical questions.

2. Declare the grain in one precise sentence.

3. Validate the grain with business stakeholders and sample records.

4. Identify dimensions that describe the grain.

5. Identify facts that are true at that grain.

6. Classify facts as additive, semi-additive, or non-additive.

7. Select transaction, periodic snapshot, accumulating snapshot, factless, or hybrid structures.

8. Define surrogate-key and historical-change policies.

9. Conform shared dimensions across processes.

10. Test for duplication, reconciliation, temporal correctness, and security.

11. Document semantic definitions and usage restrictions.

12. Revisit the design as business processes and reporting requirements evolve.

Chapter Summary

Dimensional data modeling organizes analytical data around business processes, facts, and dimensions. The grain—the exact meaning of one fact row—is the foundational design decision. Facts may be additive, semi-additive, or non-additive, and may be stored as transaction, periodic snapshot, or accumulating snapshot facts. Dimensions provide descriptive context and commonly use surrogate keys to support integration and historical versions.

Star schemas favor simplicity and query usability, while snowflake schemas normalize selected dimensions. Fact constellations support multiple processes through conformed dimensions. Factless facts represent events or coverage without numerical measures, and bridge tables resolve many-to-many relationships but require allocation and aggregation controls. Hierarchies may be balanced, ragged, or parent-child.

Slowly changing dimension strategies determine whether history is overwritten, preserved, or partially retained. Temporal modeling should distinguish valid time from system recording time and should explicitly address late-arriving data. Effective governance requires documented grain, lineage, reconciliation, quality testing, security, privacy, and shared business definitions. Ultimately, dimensional modeling is a semantic discipline: reliable analytical results depend on correct business meaning as much as on physical implementation.

Key Terms

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

Fact table: A table containing business measurements and dimensional foreign keys.

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

Measure: A value recorded for analysis, such as quantity or revenue.

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, such as a ratio, that should not be summed.

Surrogate key: A warehouse-generated identifier for a dimension member.

Natural key: An identifier originating in a source business system.

Conformed dimension: A shared dimension with consistent definitions across fact tables.

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

Snowflake schema: A schema with normalized portions of dimensions.

Fact constellation: Multiple fact tables sharing dimensions.

Factless fact: A fact table recording an event or relationship without a numerical measure.

Bridge table: An associative structure used to resolve many-to-many relationships.

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

Slowly changing dimension: A dimension whose attributes change over time under a defined history policy.

Valid time: The period during which a fact or attribute is true in the business world.

System time: The period indicating when information was recorded or known by the system.

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

Review Questions

Short Answer Questions

1. Why must the grain of a fact table be declared before selecting facts and dimensions?

2. Distinguish additive, semi-additive, and non-additive measures.

3. What problem does a surrogate key solve in a slowly changing dimension?

4. What is a conformed dimension, and why is it important?

5. Give one example of a factless fact table.

Long Answer Questions

1. Compare star and snowflake schemas with respect to usability, redundancy, maintenance, and query complexity.

2. Explain transaction, periodic snapshot, and accumulating snapshot fact tables, including suitable applications for each.

3. Describe Type 0, Type 1, Type 2, and Type 3 slowly changing dimensions and discuss when each is appropriate.

4. Explain how bridge tables support many-to-many relationships and why they can cause double counting.

5. Discuss the role of governance, privacy, and data quality in enterprise dimensional modeling.

Analytical / Application Questions

1. Design the grain, dimensions, and facts for an airline flight-booking warehouse. Justify whether booking, flight operation, and ticket payment should be separate fact tables.

2. A company joins daily inventory facts directly to detailed sales facts and obtains inflated inventory totals. Diagnose the error and propose a dimensional solution.

3. A customer changes region on 1 April, but a transaction dated 20 March arrives on 10 April. Design a temporal and late-arriving-dimension strategy that preserves historical accuracy and auditability.

Multiple Choice Questions with Answers

Question 1

What is the most important first step when designing a fact table?

A. Choose indexes

B. Declare the grain

C. Normalize all dimensions

D. Create aggregate tables

Correct Answer: B. Declare the grain

Question 2

Which measure is generally semi-additive?

A. Units sold

B. Sales revenue

C. Account balance

D. Discount amount

Correct Answer: C. Account balance

Question 3

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

A. To replace all business definitions

B. To support warehouse-managed identity and history

C. To eliminate the need for foreign keys

D. To prevent dimension attributes from changing

Correct Answer: B. To support warehouse-managed identity and history

Question 4

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

A. Star schema

B. Network schema

C. Document schema

D. Hierarchical operational schema

Correct Answer: A. Star schema

Question 5

Which technique preserves multiple historical versions of a dimension member?

A. Slowly changing dimension Type 1

B. Slowly changing dimension Type 2

C. Slowly changing dimension Type 0

D. Degenerate dimension modeling

Correct Answer: B. Slowly changing dimension Type 2

Question 6

What does a factless fact table primarily record?

A. Only monetary values

B. An event or relationship without a numeric measure

C. Dimension attributes that never change

D. Database indexes

Correct Answer: B. An event or relationship without a numeric measure

Question 7

Why is a bridge table used?

A. To create a date dimension

B. To resolve a many-to-many relationship

C. To replace every surrogate key

D. To store only semi-additive facts

Correct Answer: B. To resolve a many-to-many relationship

Question 8

Which statement best describes a conformed dimension?

A. It is used by only one report

B. It has consistent meaning across fact tables

C. It contains no descriptive attributes

D. It must always be fully normalized

Correct Answer: B. It has consistent meaning across fact tables

Question 9

Profit margin is usually calculated as:

A. Total sales divided by total profit

B. Average of all transaction margins in every case

C. Total profit divided by total sales

D. Total quantity divided by total sales

Correct Answer: C. Total profit divided by total sales

Question 10

Which type of fact table records the state of a process at regular intervals?

A. Periodic snapshot

B. Degenerate dimension

C. Bridge table

D. Role-playing dimension

Correct Answer: A. Periodic snapshot