Data Model

We will modify the dimension tables in the data warehouse to support what are known as late-arriving dimension members. Each dimension table needs a status column, which we'll call IsInferred. If a fact record shows up with a business key that we do not yet have in our dimension, and we have to create a dimension record as a placeholder, we will set IsInferred to true. Inferred members are described in detail later in this chapter. In Figure 7-2, an example using the Customer dimension table is shown with the added column. This column is also used by the Slowly Changing Dimension Wizard described in Chapter 8, "Managing Changing Data."

Figure 7-2. Data model

We also will create a new database to contain tables related to monitoring the ETL operations. We've named this database ETL_Operations. We will use a table named PackageExecutionLog to track the execution of each package and record when each package is run, how long it ran for, and how many rows were received from the source, written to the destination, or rejected as errors. For each fact table we load, we'll create a new table to hold the rejected facts and the reason for rejection. The schema for each reject table matches the input schema for the source records, with the addition of the error reason and date of rejection. There are no constraints on these reject tables because some portion isn't valid, so a constraint would just get in our way of determining what is wrong with the record contents. An example of a table for rejected facts using the Sales facts, named RejectedSales, is shown in Figure 7-2.