Star Schema Design for BI Reporting

A star schema organizes data into a central fact table (containing measures — numeric, additive values like revenue, quantity, or duration — plus foreign keys to related dimensions) surrounded by dimension tables (containing descriptive attributes — product name, customer segment, region, date — that give context to the facts). The single most important design decision is the fact table's grain — the precise level of detail one row represents (e.g., "one row per order line item," not "one row per order") — because getting the grain wrong (too coarse) forever limits what the fact table can answer, while getting it too fine adds unnecessary volume without added analytical value.

Conformed dimensions — a dimension (like Date, Customer, or Product) shared consistently across multiple fact tables with the same structure, keys, and meaning — are what allow the multi-fact modeling discussed earlier in the RPD topics to work correctly; without conformance, combining Sales facts and Support facts through a shared Customer dimension produces inconsistent or meaningless results. Compared to a fully normalized (3NF) transactional schema, a star schema deliberately denormalizes dimension attributes into wide dimension tables, trading some storage/update efficiency for dramatically simpler, faster-to-query joins — exactly what a BI tool's query generator (like OBIEE's BI Server) is optimized to exploit.

A senior-level distinction interviewers probe: star schema vs. snowflake schema (where dimensions are further normalized into sub-dimension tables) — snowflaking can reduce storage redundancy but adds joins and complexity that usually outweigh the benefit for BI reporting workloads, which is why star schema remains the default recommendation for most OBIEE/OAC-fed warehouses.