Data Model

Our facts in this example are patient diagnosis records generated throughout a patient's hospital stay. These records are created by the medical staff as a patient's condition changes. They are entered into the hospital's patient monitoring application. The dimensions we are concerned with are Patient, Diagnosis, Date, Time, and Ward. The Patient dimension changes quickly; the other dimensions are static. This yields a data model like that shown in Figure 12-2.

Figure 12-2. Data model

The main thing you need to do in your data model to accommodate real-time OLAP is to support partitioning and incremental loading. We are relying on a partitioning scheme to minimize the amount of reprocessing time, and we are relying on incremental loading to minimize the number of partitions affected during the loading of facts. We'll use the DiagnosisDate column for both purposes. Our Patient dimension is also changing in real time, so we'll need a column to track which patient records are new or updated. For that, we'll use the DateLastUpdated. We can compare these column values against those in the source tables and load only the newer records.

In real-time applications, sometimes the facts arrive before new dimension members do. This would normally be classified as missing members. For example, unidentifiable patients might arrive at the hospital, and their symptoms and resource needs would be recorded, but we would not have a member for them in our patient dimension yet. We need to support inferred members, which we discussed in Chapter 7, "Data Quality."

Practical Business Intelligence with SQL Server 2005
Practical Business Intelligence with SQL Server 2005
ISBN: 0321356985
EAN: 2147483647
Year: 2007
Pages: 132 © 2008-2017.
If you may any questions please contact us: