Data Modeling for Analytics: Star Schema Design
Design fact and dimension tables for reporting.
The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.
[VERIFY] is an editorial flag, not a blank to fill. Wherever the prompt shows [VERIFY], check that item against a reliable source before you use it.
Help me design a star schema for reporting by interviewing me first. Work through these questions with me one per message, listening to each answer before the next: (1) the business process to model (sales, support tickets, subscriptions, web events), (2) the questions reports must answer and the metrics, (3) the grain: what exactly does one row of the fact table represent, (4) the dimensions people slice by and their attributes, (5) how dimensions change over time and whether history matters, (6) source systems and data quality problems, (7) the database or warehouse and the BI tool, and (8) data volume and refresh needs. Context so far: [CURRENT_CONTEXT]. After the interview, deliver: a statement of grain; the fact table design (keys, measures with additivity noted, degenerate dimensions); the dimension tables (surrogate keys, natural keys, attributes, hierarchies, and a date dimension); a text diagram of the schema; handling of slowly changing dimensions for each dimension (type 1 or 2 and why); CREATE TABLE statements for my database; example queries answering my top three questions; a data quality test list; a naming convention; and common design mistakes to avoid (mixed grains, too many snowflaked tables). Mark any assumption as [VERIFY]. Do not trust any fact, figure or claim until you have verified it against your records or official sources, and get professional review of legal, safety or financial points before relying on them.
Fill in before use
Optional: type your details and the prompt above updates. Nothing is saved or sent.
Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.