Database Reference
In-Depth Information
•
Are windows into microlevel transactions
•
Need data at detailed level to run the business
•
Suitable only for questions at level of individual transactions
•
Data consistency, nonredundancy, and efficient data storage are critical
These characteristics make E-R modeling technique appropriate for OLTP
systems. The E-R modeling technique removes data redundancy, ensures data
consistency, and expresses microscopic relationships.
On the other hand, data warehousing systems possess different characteristics
and serve other purposes. Here is a summarized list of data warehousing features:
•
Meant to answer questions on the overall processes
•
Focuses on how managers view the business
•
Reveals business trends
•
Information centered on a business process
•
Answers show how business processes are monitored and measured
•
Enables measures to be analyzed and studied along several business
dimensions
Fact and Dimension Tables
For modeling data warehouses, E-R modeling tech-
nique is not suitable. We need to use a different technique. For modeling data ware-
houses, we use the dimensional modeling technique. For a moment, consider how a
manager or an analyst performs a typical analysis. For example, if an analyst wants
to analyze sales, how does he or she perform the analysis? He or she needs data
such as sales quantities, sales dollars, sales price, sales cost, and margin. These pieces
of information are known as facts—metrics to be analyzed. How are the facts going
to be analyzed?
Let us consider a typical query: How much revenue did sales of product Widget-
1 to married customers generate for the past three years, in the northeast territory?
In the query, the fact being analyzed is the revenue or margin. The revenue is being
analyzed along four business dimensions—product, customer, time, and salesper-
son's territory.
The dimensional modeling technique enables us to model facts and dimensions.
The result is a star schema.
STAR schema
Figure 20-14 presents a STAR schema for the subject of sales.
Notice the fact table in the middle surrounded by dimension tables.
Inside the fact table, you will find all the measures or facts to be used for analy-
sis. In each dimension table, you will find the attributes of the particular business
dimension. Notice the primary keys of each table. Each dimension table is in a
one-to-many relationship with the fact table in the middle. With such an arrange-
ment in the STAR schema with the fact table in the middle, query executions are
optimized.
Database Software Considerations
Examine the features of the leading com-
mercial relational DBMSs (RDBMSs). As data warehousing becomes more preva-
Search WWH ::
Custom Search