In other cases, fact tables can contain relationships between business objects, for example inventory levels at given days. These inventory levels are a form of relationship because they indicate which product is available at which warehouse location at a given point in time. Each relationship is represented in the fact table as one record.

Figure 7.2 shows an example of a fact table that represents a flight.

The fact table has two different types of columns: first, foreign key references to dimension tables which are covered in the next section. This includes all four references that are part of the primary key, but also TailNumberKey, CancelledKey, and DivertedKey. And second, the measure values, the facts themselves. Many of them indicate the length of delays, for example DepartureDelay, WeatherDelay, or SecurityDelay. Others indicate the length of subprocesses, for example the time to taxi in and out, airtime and the traveled distance.

Not all facts are numeric values, but they are of most value to business users, because they can be easily aggregated by dimension. The fact table is identified by a subset of foreign key references to dimension tables. This subset becomes the composite key that represents the primary key of the table. Not all foreign keys are required to be included in the primary key, because many times the dimension only describes the fact but does not identify it. In other cases, it might be required to add another identifying fact into the fact table’s primary key to guarantee uniqueness, for example a transaction number, such as an invoice number or a booking reference code [2].

Introducing keys for uniquely identifying the rows as the primary key of the fact table should be avoided, because of the storage space required for storing the key value but also for maintaining the index. The index itself is of no use, because it cannot be used by the end-user to identify individual facts. Only in some limited cases, mostly for technical reasons, a generated or derived key used as primary key actually makes sense. One example for such a reason is when it is required by the business to load identical rows into the fact table (for example, when dealing with history) or when other fact tables should be cross-referenced [2].

Unlike fact tables, a dimension table uses a PassengerKey key value as the primary key of the table. This is because this key is used quite often in the fact table that references the dimension table. In order to reduce the amount of required storage and to improve the join performance, it makes sense to do so and avoid the use of a natural key. Other than that, the key has no meaning to the business [3]. In addition to this technical key, dimensions often contain business keys that identify the entries in the dimension table [3]. In Figure 7.3, this is the PassportNumber attribute. The entries often represent business objects and the changes to these objects in the operational system. While the natural key for the same business object remains the same, even if descriptive data has been changed in the operational system, the key changes for each change of the data. This effect is used by the fact table to reference the version of the data that was active at the time when the fact happened in the business [3]. The hash key from the Data Vault 2.0 model is used as the key in the dimensional model. This simplifies the loading process and helps to virtualize information marts. The loading of information marts, both materialized and virtualized, is demonstrated in Chapter 14, Loading the Dimensional Information Mart.

Despite the keys in the table, the dimension table holds the descriptive data that describe the dimension entry. It is very common that dimension tables consist of many descriptive attributes. They can range up to 50 or 100 columns. On the other hand, they often don’t have many rows in the table. There are some dimensions, such as the Passenger dimension presented in Figure 7.3, that might hold many records, but most dimensions are rather small, holding maybe a hundred records at most [2].

Not all numeric attributes should be measures in the fact table. For example, the row number of seats within an airplane is not used in any calculations. It might be used as a natural key, but it is not aggregated by the business user. The same applies to the age of the passenger, as long as the business doesn’t want to calculate the average age of the airplane’s passengers. There are different types of measures in the fact table of a dimensional model [2]:

Therefore, it also depends on the business needs whether an attribute becomes a measure in the fact table or a descriptive attribute in one of its dimension tables [2].

Because this approach is so common, many database vendors have optimized their relational database engines to such requests, including Microsoft by the support of star join optimization, which we have already mentioned in section 7.2 [5].

The following statement could be used to query the fact table in Figure 7.2:

This statement would first join the dimension DimAirport to the fact table, using the OriginAirportKey. Note that DimAirport could be used two times: first for the origin airport, and second for the destination airport. Therefore, an alias, OriginAirport, is used for the dimension table in the statement.

Once the dimension has been joined, the state of the airport is used to filter the data to all airports in California. The airport’s state is a descriptive attribute in the dimensional table. In addition, the airport code is used to group the data in order to present the user a list of known airports in California with the average departure delay of that airport.

The statement also shows how the individual clauses of the statement are used in dimensional queries:

While the GROUP BY clause can be used to change the grain of the facts to a higher level, it is not possible to produce a lower grain level [3]. Consider a fact table that stores data about flight totals: how many passengers were on the plane, origin and destination airport, flight date, etc. If the fact table doesn’t store any information on the passenger level, it is not possible to analyze this information. Therefore, it is important to select the appropriate fact table grain when designing the dimensional model.

In this example, there are two star schemas, with their fact tables in the center: FactConnection on the left and FactAirportVisit from Figure 7.1 on the right. Each of them has a number of connected dimensions. Both share two conformed dimensions, DimAirport and DimDate, which are shown in the center of the diagram. In order to support cross-process analysis between FactConnection and FactAirportVisit, it is crucial that both conformed dimensions have exactly the same structure and data.

To support such conformed dimensions, it is required that any change to a conformed dimension should not break one of the dimensional stars which uses the conformed dimension. This might and probably will occur in later sprints of the project. Whenever the structure of the content of a conformed dimension is modified, the impact on each dependent star schema has to be reviewed and properly tested. Therefore, the use of conformed dimensions incurs a change impact that might limit the agility of the development team. This overhead of maintaining conformed dimensions often leads to the practice that developers don’t touch existing conformed dimensions in order to avoid testing. Instead, they create new, additional conformed dimensions to avoid the reengineering costs, leading to nonconformed dimensions. In other cases, they copy the structure and data of existing conformed dimensions into new stars and extend them with new features, leaving the conformed dimension in the old model untouched (therefore, no testing is required) and adding nonconformed dimensions again. We call this practice “dimension-itis,” which leads to an unmanageable number of dimensions that are all the same, somehow… nonconformed.

The opposite practice is to add more attributes and data into existing conformed dimensions in order to avoid adding new dimensions. This approach is practiced when developers want to avoid creating new conformed dimensions due to the amount of required testing of existing facts and dimensions. The end result of this approach is that the “conformed” dimension cannot sustain any further changes due to unclear and unmanageable changes to the structure and unclear or complex dependencies, and it becomes a “deformed” dimension: a nightmare requiring huge reengineering costs when touched in the future. Teams that produce such deformed dimensions often are likely to avoid documenting them, adding more headaches for their successors.

We don’t recommend preventing the use of conformed dimensions. What we need is a way to quickly create and change them, with low maintainability costs. Our recommendation is to use virtual information marts, which we describe in Chapter 14.

Slowly changing dimensions are used to handle changes in dimensional tables. This is because the pace at which dimensions change is relatively slow, especially compared to fast-changing fact tables [3]. Because there are multiple ways to track history in a dimension, Kimball introduced a classification for the dimensional tables [7].

A slowly changing dimension type 0 preserves the initial dimensional attribute values. If a change occurs in the source system, the value in the dimension is not changed. While this type is less common than the next three, it can be used to retain the initial credit score of a customer or any other initial value [7].

Changes in the source system overwrite the data in the slowly changing dimension type 1. The dimension’s attribute always reflects the most current value. Therefore, no history is tracked [2]. If there are aggregations based on this attribute (e.g., in the GROUP BY clause of the statement), the results of those aggregations change due to the new value [2]. Table 7.2 shows an example of a Type 1 dimension.

The dimension table presented in Table 7.2 holds address information about passengers. Each passenger is identified by a key, which is the primary key that is used by the fact table, and a natural key, the passport number. All other attributes in the dimension are of descriptive nature and describe the current address in detail.

When a passenger moves, the current record is overwritten. Therefore, no history is kept in the dimension table. The table only reflects the current address and no past addresses of the passenger.

Instead of overwriting existing data, changes in the source system add a new row to the type 2 dimension table. By using this approach, it is often possible to correctly keep track of history. Therefore, it is the predominant technique to track history in information marts [2]. An example of a Type 2 dimension is presented in Table 7.3.

The table (modified from Table 7.2 and fit for purpose) shows how changes are tracked in a slowly changing dimension type 2: each new version of the record is added as an additional row to the dimension table. In this example, Amy Miller, with key 1b3ba82…, has married Peter Heinz, with key 28ab342…, and moved to his place in California. Therefore, her new record with key 444bbaa… reflects these changes. The old record remains untouched to keep the historical information. Old facts keep using the record with key 1b3ba82…, but new facts reference the dimension entry with key 444bbaa….

Changes in a slowly changing dimension type 3 don’t add additional rows but track history in additional columns. For each attribute that should be historized, a new column is added per change that occurred in the source system and should be displayed to the business user. This makes it easier to analyze the changes over time. Table 7.4 shows an example.

This example shows how a business keeps track of the passenger’s previous state using an additional column. When the passenger moves, as is the case with Amy Miller (now Amy Heinz) in the entry with key 1b3ba82…, the old address is overwritten and the attributes that should be historically tracked are copied into additional columns. In this case, the Previous State column is updated to reflect the previous state of the passenger. History is not limited to only one entry. It is common to use a limited number of columns to keep a couple of changes – in most cases only the actual and the previous value. In other cases, key performance indicators (KPIs) are tracked over time, as in the example in Table 7.5.

Table 7.5 shows a dimension that keeps track of available flights, where they start from and their destination airport. In addition to this basic information, the dimension table provides three KPI values that show how the average number of passengers has evolved over the last three years (including the current year).

In addition to the presented dimension types, the following ones are found in some cases [7]:

The columns in Figure 7.5 show the financial years (FY) from the Date dimension that are available in the PivotTable. The rows show the geographic regions from the Geographic dimension. In addition, there are grand totals that show the overall value for each column or row. For example, the passenger revenue of FY 2008 in the United States was $19,471.989.04.

The geographic hierarchy is made up of the following levels:

It is possible to drill-down to any level that is available in the PivotTable (which is based on an OLAP cube), but not any further. Figure 7.6 shows a logical model that shows the Geography dimension with its hierarchy.

The notation follows the ADAPT™ modeling methodology for dimensional databases [8]. The methodology is frequently used in the industry to model and document dimensional models. The first element depicts the Geography dimension itself. The dimension includes one hierarchy, also called Geography. The other elements are the individual levels of the hierarchy and have to follow in the given order. We are going to the ADAPT modeling methodology throughout the book.

Similar to the Geography hierarchy, the Fiscal Calendar hierarchy consists of multiple levels:

This hierarchy is very common in data warehouse systems and is frequently used by end-users. It uses a different start date for the year, a practice often performed by commercial organizations. For example, businesses in commerce and trade often start their fiscal year on another day than December 31. This is often due to the frequent post-Christmas returns made by customers who are not happy with their gifts. Because the volume of these returns is relatively high, it has an impact on the year’s total revenue. However, in most cases, the returns happen in January of the following calendar year. If the company closes the books by the end of December, the returns would account to the following fiscal period and skew the financial results. Therefore, these companies often choose another month as the end of the fiscal period. One example is Best Buy, an electronics retailer in the US. The company’s fiscal year ends on February 1 [9]. Figure 7.7 shows the logical model of the Date dimension.

Note that there are two hierarchies available in the dimension: Calendar and Fiscal Calendar. Both have their own Year, Semester and Quarter levels but share their Month and Date levels. Both hierarchies use all attributes of the dimension, but they are organized in a different way. Depending on the business analyst’s need, one or the other hierarchy is used. Nothing is wrong with either; the value of each hierarchy depends on the context of the business analysis.

To analyze the information in Figure 7.5 in more detail, it is possible to click the plus sign next to the row label and drill-down into the Geography hierarchy, as shown in Figure 7.8.

The total passenger revenue that is still displayed in the row marked bold is broken up by state, the next geographic level below the country level. For each state, the passenger revenue is displayed per year. It is possible to further break down this data, by drilling-down to the next level (Figure 7.9).

In Figure 7.9, the passenger revenue for the state of Alabama is further analyzed. Each city in Alabama is displayed and the passenger revenue for each city is presented per year. The date column represents a similar hierarchy. It is possible to drill-down this hierarchy, too.

Figure 7.10 shows only data from FY 2007, but in more detail. Note that the first three months of the financial year, which starts in July, are being displayed. For each month, quarter and half-year, the table shows total values in the columns on the right. By using this approach, business analysts are enabled to understand the information in the table in great detail.

The Geography hierarchy is implemented physically from the dimension table, as shown in Figure 7.11.

Each level of the hierarchy is based on one of the physical attributes of the dimension table. The association between the attribute and the hierarchy level is done in Microsoft SQL Server Analysis Services and is discussed in more detail in Chapter 15, Multidimensional Database. Similar hierarchies will be implemented in the chapter when the OLAP cube is being built and a date dimension is added as a hierarchy.

If a star schema should express this relationship, both dimensions, DimGroup and DimCarrier, have to be directly referenced by the fact table, as shown in Figure 7.12.

This example follows the one presented in Figure 7.4. However, it shows only one star schema. Note the two dimensions DimGroup and DimCarrier which are independently referenced by the fact table. However, there is an implicit relationship between these two dimensions. The relationship is implemented in the ETL job that loads the table. The ETL job makes sure that only valid CarrierKey and GroupKey combinations are loaded into the fact table. The model does not implement any rule that prevents the loading of invalid combinations. On the other hand, this model is easy to use by business users, because they can directly join all required context information. This is why, on the first hand, the star schema allows only directly referenced dimension tables. For example, in order to return the average delay of a group, the following statement could be used:

As the statement shows, there is not much effort required to join the group into the result set. The join is performed directly using the GroupKey field in the fact table.

A snowflake schema, on the other hand, allows indirect dimension tables. This is useful if the relationships between dimension attributes should be modeled explicitly. If the model in Figure 7.12 were modeled as a star schema, it would look similar, as in Figure 7.13.

As you can see from the diagram, the term “snowflake” comes from the appearance of the model. Dimension tables emerge from the fact table in the center, similar to the branches of a snowflake [3]. Instead of directly referencing DimGroup from FactConnection, the group is indirectly referenced using DimCarrier. The GroupKey which identified the group in the fact table has moved into DimCarrier. In order to return the average delay of a group, as similar to the previous statement, it is required to first join DimCarrier in order to join DimGroup:

This is not a problem if you are used to writing SQL statements and have no problem with indirectly joining tables as in this statement. For an experienced database developer, this statement poses no threat at all. However, if business analysts with less knowledge and experience with SQL statements have to deal with snowflake designs, they might become overwhelmed and not be able to cope with the database anymore. Despite this problem, snowflake designs are actually used very frequently.