Star schema

A star schema is a dimensional model with a fact table at a declared grain, surrounded by dimension tables that describe the facts. In lending, a fact table might record credit decisions, with measures such as amount approved and dimensions such as product, channel, date and region.

Example

For illustration, suppose a lender records three decisions for one product in a month, with approved amounts of $2,000, $3,000 and $5,000. The total approved amount for that product and month is $2,000 + $3,000 + $5,000 = $10,000.

Set the grain before adding measures and dimensions

Start by stating what one row will represent. For a lending analysis, that could be one credit decision or one loan at each month end. Keep each fact table’s rows consistent with its declared grain.

Add measures that belong at that grain, such as amount approved or days past due. Then connect descriptive tables for date, product, branch and customer segment so the measures can be viewed by those attributes.

Transaction, periodic and accumulating snapshot facts

A transaction fact records one row per event. In lending, each row could represent a loan repayment, with the event’s measures and descriptive dimensions.

A periodic snapshot records one row per item at each period end, such as one row per loan at each month end. An accumulating snapshot records one row per item as it reaches milestones, such as an application progressing from submission to first payment.

Questions we get asked

How is a star schema different from a snowflake schema?

A star schema keeps each dimension in one table. A snowflake schema normalises dimension tables into further tables.

Is amount approved a measure or a dimension?

Amount approved is a measure, so it belongs in the fact table at the declared grain. A dimension holds descriptions, such as product, channel or date.

Can one fact table mix decision rows and month-end loan rows?

No. Those rows represent different grains, so use a separate fact table for each grain. One can record credit decisions, and another can record loans at each month end.

A star schema places a fact table at a declared grain among dimension tables, and the lending warehouse architecture article covers fact grain and decision records.