CampusLearn
← BIT 405 Business Intelligence and Data Warehousing

Data Warehouse Design and Star Schemas

Notes

Analytical storage and grain A data warehouse is an integrated historical repository designed for analysis rather than routine transaction entry. Operational systems prioritise reliable individual transactions, while warehouses support comparisons across periods and business functions. The grain defines exactly what one fact-table row represents, such as one product line on one sales receipt. Declaring grain before choosing measures prevents incompatible records from being mixed. Source identifiers, timestamps and transformation rules provide traceability. Historical retention and consistent definitions enable managers to compare results without repeatedly reconciling separate spreadsheets that use different meanings for customers, products or revenue. Facts and dimensions A star schema organises measurable events in a central fact table connected to descriptive dimension tables. Facts include quantities and monetary amounts, while dimensions describe dates, products, branches and customers. Surrogate keys are warehouse-generated identifiers that separate analytical relationships from changing operational codes. Some measures are additive across all relevant dimensions, while balances cannot safely be summed across time. Slowly changing dimension techniques preserve or overwrite attribute history according to reporting needs. Relationships must be designed carefully because an incorrect join can duplicate fact rows and inflate totals without generating an obvious database error. Practical example A Nakuru wholesale shop defines its sales grain as one product line per receipt. Three lines contain quantities of 4, 3 and 5 bags, each sold at KES 2,000. Line revenues are KES 8,000, KES 6,000 and KES 10,000, giving total revenue of KES 24,000 for 12 bags. Each line references date, product and branch dimensions. If a faulty product join matches every line twice, reported revenue becomes KES 48,000. The team therefore tests dimension-key uniqueness and compares warehouse totals with source receipts before publishing sales dashboards to management.

Quiz

1. Which is usually a descriptive dimension?

2. What does fact-table grain define?

3. What causes KES 48,000 in the example?

Sign in to submit and securely grade your quiz.