Developing the Model

Step 3: Add Derived Data The third step in developing the data warehouse model is to add derived data. Derived data is data that results from performing a mathematical operation on one or more other data elements. Derived data is incorporated into the data warehouse model for two major reasons—to ensure consistency, and to improve data delivery performance. The reason that this step is third is the business impact—to ensure consistency; performance benefits are secondary. (If not for the business impact, this would be one of the performance related steps.) One of the common objectives of a data warehouse is to provide data in a way so that everyone has the same facts—and the same understanding of those facts. A field such as “net sales amount” can have any number of meanings. Items that may be included or excluded in the definition include special discounts, employee discounts, and sales tax. If a sales representative is held accountable for meeting a sales goal, it is extremely important that everyone understands what is included and excluded in the calculation. Another example of a derived field is data that is in the date entity. Many businesses, such as manufacturers and retailers, for example, are very concerned with the Christmas shopping season. While it ends on the same date (December 24) each year, the beginning of the season varies since it starts on the Friday after Thanksgiving. A derived field of “Christmas Season Indicator” included in the date table ensures that every sale can quickly be classified as being in or out of that season, and that year-to-year comparisons can be made simply without needing to look up the specific dates for the season start each year. The number of days in the month is another field that could have multiple meanings and this number is often used as a divisor in calculations. The most obvious question is whether or not to include Saturdays and Sundays. Similarly, inclusion or exclusion of holidays is also an option. Exclusion of holidays presents yet another question—which holidays are excluded? Further, if the company is global, is the inclusion of a holiday dependent on the country? It may turn out that several derived data elements are needed. In the Zenith Automobile Company example, we are interested in the number of days that a dealer is placed on “credit hold.” If a Dealer goes on credit hold on December 20, 2002 and is removed from credit hold on January 6, 2003, the number of days can vary between 0 and 18, depending on the criteria for including or excluding days, as shown in Figure 4.10. The considerations include: ■■ Is the first day excluded? ■■ Is the last day excluded? ■■ Are Saturdays excluded?