SQL Data Mart Table Design for Reporting From an Operational Database
Design summary tables that make reports fast and consistent.
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.
Design a reporting layer for SQL. Operational tables and their grain: [SOURCE_TABLES]. Reports and metrics needed: [REPORTS]. Refresh needs: [REFRESH_NEEDS]. Database system: [DATABASE]. Define each metric once and reuse it. Worked design: fact_orders with one row per order line (order_id, line_number, date_key, customer_key, product_key, quantity, net_amount), dim_date, dim_customer (with segment and region) and dim_product, plus rules for slowly changing attributes. Transformation logic in words and example SQL: exclude cancelled orders, convert currency using the rate on the order date, net amount = price times quantity minus discount. Size: 200,000 orders a year over 3 years is 600,000 orders, and at 2.4 lines each about 1.44 million fact rows. Tests: the sum of net_amount for a month equals the finance report within a tolerance, every fact row has a matching customer, and no duplicate order lines. Refresh options (full rebuild or incremental) with nightly refresh and failure alerting, a test list and a naming standard. Mark syntax [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.