PATTERNS OF DATA MODELING- P22: Models provide the means for building quality software in a predictable manner. Models let developers think deeply about software and cope with large size and complexity. Developers can think abstractly before becoming enmeshed in the details of writing code. Although models are beneficial, they can be difficult to construct. That is where patterns come in. Patterns provide building blocks that help developers construct models faster and better. | 86 Chapter 6 Star Schema Template SQL Queries Typically there are two kinds of queries for this template querying facts and querying dimensions. Figure illustrates the first category of queries selecting groups of facts and summarizing them for various combinations of dimensions. Section discusses the store sales example. Such queries can involve massive amounts of data so performance is always a concern. Data warehouses use special techniques to speed performance Inmon-1993 Kimball-1998 . The colon prefix denotes variable values that must be provided. SELECT storelD SUM saleQuantity FROM Sale INNER JOIN Product AS P ON INNER JOIN Date AS D ON INNER JOIN Store AS S ON WHERE aProductID AND July 1 2000 GROUP BY storeID ORDER BY storeID Figure Star schema SQL query. Summarize facts for a combination of dimensions. The second kind of query searches dimension data to retrieve descriptive details Figure . Such queries involve a straightforward search through a table or a few related tables. SELECT storeName streetAddress cityName stateName postalCode FROM Store WHERE storeID aStoreID Figure Star schema SQL query. Retrieve dimension data. Sample Populated Tables Figure shows star schema tables populated with data. The values of the IDs are arbitrary but internally consistent. Also for a real problem the dimension tables would have more descriptive attributes than the ones shown. The data is a subset of data for store sales and is covered further in the next section. In practice there are a modest number of dimension records tens or hundreds per table and a large number of facts thousands or millions . Examples Figure illustrates the star schema template with a store sales model. Sale is a fact that is surrounded by the dimensions of product payment type cashier store date and customer. In data warehouse terminology Figure is .
