MIS 430: Star Schema (Quiz 2)

What is dimensional modeling?

A data design methodology used for designing subject-oriented analytical databases, such as data warehouses or data marts.

Which modeling technique is tailored specifically for analytical database design?

Dimensional modeling.

What is the key difference between ER/relational modeling and dimensional modeling?

ER/relational modeling is optimized for running the business; dimensional modeling is optimized for analyzing the business.

What standard relational concepts does dimensional modeling use?

Primary keys, foreign keys, and integrity constraints.

What two types of tables are used in dimensional modeling?

Dimension tables and fact tables.

What do dimensions describe?

The Who, What, Where, When, and How of the data.

Which dimensions answer WHO, WHAT, WHERE, and WHEN?

WHO = Customer; WHAT = Product; WHERE = Store; WHEN = Date.

What type of data is most common in dimension attributes?

Textual or categorical data.

What are examples of categorical dimension attributes?

Product brand/color, customer gender/education, and store region/city.

How can numeric values be used as dimension attributes?

Convert them into categories, such as age ranges, income brackets, or Light/Medium/Heavy.

What is a useful rule for identifying a dimension attribute?

If you GROUP BY or FILTER on it, it is likely a dimension attribute.

Why may attributes in a dimension not be directly related to each other?

Logically different attributes can be stored together for analytical convenience.

Why are dimension tables intentionally denormalized?

To eliminate joins, producing faster queries and a simpler user experience.

What does denormalization in a dimension table intentionally violate?

Third Normal Form (3NF).

What are examples of dimension hierarchies?

Date → Month → Quarter → Year and Store → City → State → Region.

What analyses do dimension hierarchies enable?

Drill-down and roll-up analysis.

What is the role of a fact table in a star schema?

It sits at the center and contains quantitative measures plus foreign keys to dimensions.

What does each row of a fact table represent?

An intersection of all associated dimension records.

What determines the level of detail represented by a fact table row?

The grain or granularity.

What are measures or facts?

Quantitative performance measures or metrics related to the subject of analysis.

What type of data are measures typically?

Numeric data intended for mathematical computation.

What are examples of measures in a sales fact table?

Dollars sold and units sold.

What is a useful rule for identifying a fact measure?

If you SUM or AVERAGE it, it is likely a fact measure.

What does a fact table contain besides measures?

Foreign keys connecting the fact table to dimension tables.

What is a star schema?

The simplest and most widely used dimensional model, with one fact table surrounded by multiple dimension tables.

How many fact tables does a star schema contain according to this presentation?

Only 1 fact table containing measures for 1 subject.

How many dimension tables can a star schema contain?

Multiple dimension tables.

Why is it called a star schema?

The diagram resembles a star, with the fact table at the center and dimensions surrounding it.

In dimensional modeling, what do dimensions and facts represent?

Dimensions = attributes of decision making; Facts = outcome measures.

What type of keys do dimension tables use in the star schema examples?

Surrogate primary keys.

Where are dimension keys stored to connect dimensions to the fact table?

As foreign keys in the fact table.

What two questions should be asked for every possible dimension?

Is it relevant to analyzing the subject, and is it available from existing data sources?

When should a dimension be included in the model?

When it is both relevant to the analysis and available from existing data sources.

What are the main stages of the dimensional model design process?

Step 1: Requirements; Step 2: Grain; Step 3a: Dimensions; Step 3b: Facts.

What is done during Step 1: Requirements?

Identify data sources, high-level entities/measures, and possible dimensions.

For ZAGI Retail, what is the subject of analysis and what are its measures?

Subject = Sales; Measures = Dollars and Units.

For ZAGI Retail, what possible dimensions are identified in Step 1?

Customer, Product, Store, and Date.

What hint helps identify the fact among operational entities?

Identify which table captures the business event.

How was ZAGI's normalized source transformed conceptually in Step 1?

8 normalized tables became 1 Sales fact table and 4 dimensions.

What are the four dimensions surrounding ZAGI's Sales fact table?

Calendar/Date, Product, Store, and Customer.

What is Step 2 of dimensional model design?

Identify the grain.

What does the grain define?

What a single fact table record represents—the level of detail associated with the measurements.

How are detail and granularity related?

More detail = lower/finer granularity; less detail = higher/coarser granularity.

What is Kimball's Golden Rule for choosing grain?

Choose the lowest grain the source data supports.

Why should you choose the lowest grain supported by the source data?

You can aggregate detailed data upward, but cannot drill into detail that was aggregated away.

In the Date hierarchy, which level is finest and which is coarsest?

Date/Day is finest; Year is coarsest.

What are the finest levels of ZAGI's Date, Store, and Product dimensions?

Day, Store, and Product.

What is the grain of the ZAGI Retail dimensional model?

Product sold in one store on one day.

More specifically, what does each ZAGI fact row represent?

Sales of one line item/product in one transaction by a customer in a store on one day.

What is done during Step 3a: Dimensions?

Determine all dimensions, identify dimensional attributes, and identify attribute hierarchies.

What is done during Step 3b: Fact Table?

Identify facts/measures, identify foreign keys, and define the grain.

How is the ZAGI Product dimension created from the operational source?

Product, Vendor, and Category tables are consolidated into one denormalized Product dimension.

After completing the logical design, how is the dimensional model populated?

Create the tables and load data from operational sources.

What key rule applies when populating dimension tables?

Only populate unique records into the dimension table.

How are Calendar dimensions often populated?

Generated independently with pre-computed attributes such as DayOfWeek, IsWeekend, IsHoliday, and FiscalQuarter.

What are three major benefits of a star schema?

Intuitive for business users, improved query performance, and extensibility to data changes.

Why does a star schema improve query efficiency?

It uses fewer tables, fewer joins, and shorter join paths, producing simpler and faster queries.

In the PowerPoint's Query A, how does the operational database compare with the star schema?

Operational: 7 tables and 6 joins; Star schema: 4 tables and 3 joins.

What did Query A demonstrate about star schema efficiency?

About 50% fewer tables and joins while providing the same analytical power.

How does a star schema support ad-hoc analysis at scale?

The same schema can support thousands of ad-hoc queries by analysts with varying SQL skill levels.

What is the multi-source integration benefit of a star schema?

New data sources can enrich dimensions with additional attributes without changing the fact table.

What new information came from ZAGI's Facilities Department source?

Store characteristics such as layout type, square footage, and checkout/deli system details.

What new information came from the external customer demographic source?

Gender, marital status, education, and credit score.

What happened to ZAGI's fact table when the Store and Customer dimensions were enriched?

The fact table remained unchanged.

How did Query B expand Query A after adding new sources?

It added the Customer table/join plus Gender = Male and StoreLayout = Modern filters.

Why is multi-source enrichment easier in a star schema?

New attributes can be added modularly to dimensions without restructuring the entire model.

What are the three main Week 4 concepts summarized in the presentation?

Star schema modeling, the requirements→grain→dimensions/facts design process, and query efficiency/multi-source integration.