| In part 1 of this article series, we described the | | | | The level of detail captured should be the lowest |
| general structure of a dimensional model. In the | | | | possible (atomic level). The lowest level of detail, |
| present article we shall describe the basic design | | | | includes the full scope of informational dimensions |
| principles of dimensional modeling. Dimensional | | | | related to an event. |
| modeling follows the four steps defined below. A. | | | | As soon as a higher level of detail is selected, by |
| Selection of the business process (or processes), the | | | | aggregating atomic data, certain event dimensions |
| performance of which shall be monitored. Business | | | | are lost. |
| processes the performance of which is considered | | | | Dimensional models aim to capture measurements, |
| critical, and relevant data are sufficient (e.g. | | | | according to the way an Analyst views data:o Events |
| operations data derived from these processes), | | | | that took place at a certain moment in time o |
| should be selected with priority. The selected | | | | Periodic measurements which provide a snapshot of |
| business process, may relate to a single organizational | | | | the situation at a given moment in timeo Complete |
| unit, or spanning more than one organizational unit. | | | | view of a transaction which had more than one steps |
| The capture of overlapping information by different | | | | (did not start and complete at a single event) |
| departments which can lead to many versions of | | | | C. Selection of the dimensions which form the event |
| truth, is avoided through the capture of a single data | | | | framework, within which the measurements were |
| stream for an 'end-to-end' process. B. Determination | | | | made. Common examples of dimensions are: date (or |
| of the level of detail at which the process shall be | | | | time) at which the event took place, Customer, |
| monitored (also called grain statement). The grain | | | | product, branch office. The concrete definition of the |
| statement is the first step in a dimensional model | | | | level of grain facilitates the selection of dimensions. |
| design. Examples of grain statement are:· | | | | The lower the level of detail, the richer the set of |
| Each product sold (meaning: an entry shall be created | | | | dimensions which accompany the facts. D. Preliminary |
| in the fact table for each product sold)o Each new | | | | determination of the analysis methods to be |
| service contract (e.g. insurance contract) o The daily | | | | implemented. Selection of the key performance |
| snapshot of the stock in a pharmacyo The | | | | indicators (KPI) for each monitored business process. |
| accumulated capture of all facts of a transaction | | | | Identification of the facts needed in order to derive |
| which has been completed in more than one steps | | | | these indicators. Given that conditions change, |
| (e.g. the lifecycle of a tax transaction: tax statement | | | | additional facts may be selected to be captured. |
| submission - statement control - tax clearance - | | | | These facts should relate to the same level of detail. |
| payment - final payment) | | | | The dimensional model should be flexible enough, in |
| Based on the grain statement, one can derive the | | | | order to allow its future enrichment with new facts in |
| facts which should be stored in the fact table as well | | | | the fact table and new dimensional attributes. |
| as the 'surrounding' dimensions. | | | | |