Using Dimensional = Modeling

Creating and Using Data Warehouses (SQL Server = 2000)

Using Dimensional Modeling

Entity-relation modeling is often used to create a single complex = model of=20 all of the organization's processes. This approach has proven effective = in=20 creating efficient online transaction processing (OLTP) systems. In = contrast,=20 dimensional modeling creates individual models to address discrete = business=20 processes. For example, sales information may go to one model, inventory = to=20 another, and customer accounts to yet another. Each model captures facts = in a=20 fact table and attributes of those facts in dimension tables linked to = the fact=20 table. The schemas produced by these arrangements are called star or = snowflake=20 schemas, and have been proven effective in data warehouse design.

Dimensional modeling organizes information into structures that often = correspond to the way analysts want to query data warehouse data. For = example,=20 the question, "What were the sales of food items in the northwest region = in the=20 third quarter of 1999?" represents the use of three dimensions (product, = geography, time) to specify the information to be summarized.

A Data Warehouse Model

A simple dimensional model of sales information might include a fact = table=20 named Sales_Fact that contains one record for each line item of = each=20 sale, capturing the quantity sold, the unit cost, and the sale value. = Varieties=20 of information about a sales record might include the customer, the = store where=20 the sale occurred, the time and date of the sale, and the product sold. = Each of=20 these categories of information is organized into its own dimension = table.=20 Customer information is placed in a Customer dimension table, = store=20 information in a Store dimension table, time and date information = in a=20 Time dimension table, and product information in a Product = dimension table.

In a star schema, each dimension table has a single-part primary key = that=20 links to one part of the multipart primary key in the fact table. In a = snowflake=20 schema, one or more dimension tables are decomposed into multiple tables = with=20 the subordinate dimension tables joined to a primary dimension table = instead of=20 to the fact table. In most designs, star schemas are preferable to = snowflake=20 schemas because they involve fewer joins for information retrieval and = are=20 easier to manage.