Create advanced Works with: Any AI

Power BI Star Schema Data Model Plan From Source Tables

Table output Has example

• 📉 Power BI

Plan fact and dimension tables and relationships before building a report.

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.

Plan a Power BI data model. Business questions the report must answer: [QUESTIONS]. Source tables or files with their columns and row counts: [SOURCE_TABLES]. Keys and how tables relate: [KEYS]. Grain of the main transactions (one row per what?): [GRAIN]. Time period and calendar needs: [CALENDAR_NEEDS].

Propose a star schema: the fact table or tables, the dimensions, the relationship for each (one-to-many, single direction unless you can justify otherwise), the key columns, and columns to hide from report view. Specify a date table with the columns I need (year, quarter, month name, month number, week, fiscal period if I give the rule) and how to mark it [VERIFY menu names]. List columns to remove, rename or change type, and where each transformation belongs: source, Power Query or model. Output a list of tables with columns and roles, a relationship table (From, To, Cardinality, Direction), and the build order.

Worked mini model: Sales (OrderID, Date, CustomerID, ProductID, Amount), Customers (CustomerID, Name, Region) and Products (ProductID, Category), with relationships and a test: total Amount in the model equals the sum in the source (for example 482,300), and customers by region add to the total number of customers. Explain the problems the design avoids (ambiguous relationships, double counting from many-to-many joins, slow wide tables) and the three commonest failures: duplicates on the one side, dates stored as text, and a many-to-many chosen to get rid of an error. Validate with row counts and totals, test slicers, and check one number by hand. Keep the model as simple as the questions allow, say what you would not model yet, and list the questions for the data owner about grain and keys. Mark licence- or version-dependent features [VERIFY].

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.

Invented model. Fact table Sales: one row per order line (OrderID, LineNo, DateKey, CustomerKey, ProductKey, Quantity, NetAmount), 1,440,000 rows. Dimensions: Date (3,653 rows), Customer (18,402), Product (2,150). Relationships: Date[DateKey] one-to-many to Sales, Customer[CustomerKey] one-to-many to Sales, Product[ProductKey] one-to-many to Sales, all single direction. Hidden columns: keys. Validation: total NetAmount in the model 9,481,220.55 equals the source; customers by region add up to 18,402; no blank product in the visuals. Build order: tables, relationships, date table, measures, visuals. Common failure avoided: a relationship from Sales to Customer on a name column with duplicates would double counts, so keys are used instead.

• power-bi