Fundamental -- <Two> Dimensional Design
Information systems fall into two major categories: those that support the execution of business processes and those that support the analysis of business processes. The principles of dimensional design have evolved as a direct response to the unique requirements of analytic systems.
The core of every dimensional model is a set of business metrics that captures how a process is evaluated, and a description of the context of every measurement.
Analytic systems and operational systems serve fundamentally different purposes. An operational system supports the execution of a business process, while an analytic system supports the evaluation of the process.
Operational Systems
An operational system directly supports the execution of a business process. The activities recorded by these systems are sometimes known as transactions. The systems themselves are sometimes called online transaction processing (OLTP) systems , or transaction systems for short. To facilitate the execution of a business process, operational systems must enable several types of database interaction, including inserts, updates, and deletes. Implemented in a relational database, the optimal schema design for an operational system is widely accepted to be one that is in third normal form.
Analytic Systems -- It may answe why star schema.
While the focus of the operational system is the execution of a business process, the analytic system supports the evaluation of the process. The principles of dimensional modeling address the unique requirements of analytic systems. A dimensional design is optimized for queries that may access large volumes of transactions, not just individual transactions.
=============================================================================================
Measurement and Context
Dimensional design supports analysis of a business process by modeling how it is measured.
Most importantly, each of these questions reveals something about how its respective business process is measured. The study of sales involves the measurement of gross margin. Financial institutions measure account balance. In human resources, they measure number of absences. The finance department measures payables. Purchasing managers watch the return quantities.
Without some kind of context, a measurement is meaningless. If you are told “sales were $10,000,” there is not much you can do with this information. Is that sales of a single product, or many products? Does it represent a single transaction, or the company’s total sales from conception to date? Without some context, the measurement is useless.
As with the measurements themselves, context is revealed in business questions or reports. In the preceding questions, for example, gross margin is viewed in the context of product categories and time (the month of January).
These two simple concepts, measurement and context, are the foundation of dimensional design. Every dimensional solution describes a process by capturing what is measured and the context in which the measurements are evaluated.
=============================================================================================
Facts and Dimensions
In a dimensional design, measurements are called facts, and context descriptors are called dimensions.
Facts tend to be numeric in value, and people want to see them at various levels of detail. You can identify facts by looking for things that people want to “roll up” or “break out.
Not everything that is numeric is a fact. Sometimes, a numeric data element is really a dimension. The key is to determine how it is being used. Is it something that can be specified at varying levels of detail? If so, it is a fact. Is it providing context? If so, it is a dimension.
A dimensional design organizes facts and dimensions for storage in a database management system. In a relational database management system (RDBMS), the design is referred to as a star schema. In a multidimensional database (MDB), the design is referred to as a cube.
