# Design a star schema for a reporting use case

A dimensional model with fact tables, dimensions, grain, keys, and slowly changing dimension choices for a BI or reporting need.

- Topic: Data & Analysis
- Framework: Few-Shot
- Level: Standard
- Tags: Explain & Teach, Fill-in Template
- Source: https://www.thinkwithprompt.com/prompts/design-a-star-schema-for-a-reporting-use-case

## Prompt

```text
Help me design a dimensional model (star schema) for a reporting use case.

**Business process to report on:** [e.g. "sales orders", "support tickets", "subscriptions"]
**Questions the reports must answer:** [LIST 5-10 real questions]
**Source tables:** [PASTE names, key columns, and how they relate]
**BI tool:** [e.g. Power BI, Tableau, Looker, Metabase]
**Data volume and refresh needs:** [rows per day, how fresh it must be]

Steps:
1. Declare the grain of each fact table in one sentence and explain why. Flag if more than one fact table is needed (e.g. transactions vs. periodic snapshots).
2. List the measures and say whether each is additive, semi-additive (like balances), or non-additive (like ratios).
3. Define the dimensions, their attributes, and hierarchies (e.g. day > month > quarter).
4. Choose keys: surrogate vs. natural keys, and how to handle unknown or late-arriving members.
5. Recommend a slowly changing dimension approach for attributes that change over time (type 1 overwrite vs. type 2 history) and why.
6. Point out traps: many-to-many relationships, role-playing dimensions (order date vs. ship date), degenerate dimensions, and fan-out risks.

Return:
- A text diagram of the schema.
- A table per fact and dimension: Column | Type | Description | Source.
- A check that each listed business question can be answered, and how.
- Notes on implementing it well in my BI tool.
```

## What to fill in

- e.g. "sales orders", "support tickets", "subscriptions"
- LIST 5-10 real questions
- PASTE names, key columns, and how they relate
- e.g. Power BI, Tableau, Looker, Metabase
- rows per day, how fresh it must be

Free to copy and use. From ThinkWithPrompt (https://www.thinkwithprompt.com/), a library of 2,663 curated AI prompts.