ETLbeginner
Star Schema Design for BI Reporting
Explain star schema design principles — fact and dimension tables, grain, conformed dimensions — as the foundation for OBIEE/OAC reporting performance and usability.
A star schema is a fact table surrounded by dimension tables. The RPD's logical model assumes this shape, which is why a bad physical schema cannot be modelled around.
A star schema is like a hub-and-spoke airport model: the fact table is the central hub processing all the actual traffic (measures), and each dimension is a spoke airport with its own detailed local information (attributes) — conformed dimensions are like using the same airport codes and time zones across every airline's schedule, so combining different airlines' flight data still makes sense together.
Key Concepts
1
D_DATE D_PRODUCT
\ /
F_SALES measures + foreign keys
/ \
D_CUSTOMER D_STORE2
Fact tables hold measures and keys. Numbers you aggregate, plus a foreign key to each dimension. They
are long and narrow, and they are where the rows are.
3
Dimension tables hold descriptive attributes. Product name, category, brand; customer name, segment,
region. Short and wide, and they are what you group by.
4
Grain is the first decision and the one that cannot be changed later.
one row per order line
one row per order
one row per customer per day5
Everything follows from it. A fact at order grain cannot answer a question about line items, and no
modelling recovers information the grain discarded.
6
So the rule is to declare the grain in one sentence before designing anything, and to check every proposed
column against it.
7
Conformed dimensions are what let facts be compared. If Sales and Returns both join to the same
D_PRODUCT, a single analysis can show both by product.
D_PRODUCT
8
conformed one D_PRODUCT, used by every fact
not conformed each fact has its own product table9
Without conformance you cannot combine facts at all, because there is no shared dimension to join on — which
is the practical reason "conformed" is not a theoretical nicety.
10
Star against snowflake. A snowflake normalises the dimensions — product joins to category joins to
department.
11
star fewer joins, more redundancy, simpler for the tool
snowflake less redundancy, more joins, more complex12
Warehouses favour the star. Storage is cheap and joins are not, and the BI Server's modelling is simpler
over a star.
13
Surrogate keys rather than natural keys in the fact table. An integer, meaningless, generated by the
ETL — which is what makes Type 2 history possible, since each version of a dimension row gets its own
surrogate key while keeping one natural key.
14
A date dimension, always, with a row for every day whether or not anything happened. Time-series
functions navigate by position in the hierarchy, so a gap breaks them.
15
What the interviewer is probing.1. "Why a star rather than a normalised model?" Probing: the argument. Stalls: "Convention."
Moves up: fewer joins, a grain the business recognises, and a shape BI tools can navigate and
aggregate predictably.
16
2. "What is the first decision in designing a fact table?" Probing: the discipline. Stalls:
"The columns." Moves up: the grain — one row represents what, exactly — because every other
decision follows from it and changing it later is a rebuild.
17
3. "What is a conformed dimension?" Probing: the key concept. Stalls: "A shared table."
Moves up: one dimension used identically by several facts, which is what makes cross-process
analysis possible.
18
4. "When is a snowflake acceptable?" Probing: judgement. Stalls: "Never." Moves up: where
a dimension is genuinely huge and sparsely used, or compliance requires it — but it costs join
complexity for every consumer.