Star Schema

A star schema, as the name implies, is a star-shaped schema that consists of a star structure with one (primary) fact table and a number of associated dimension tables. The star schema supports querying for large datasets.

Figure 2.2 illustrates the star schema.

You have already learned a bit about fact tables and dimension tables. In this section, we’ll look at them more closely.

You already know that a fact table contains facts, and it is connected to a dimension table via a foreign key. Further, a fact table has two types of columns:

• Column that contains facts

• Column that contains foreign keys to the dimension table

The fact table consists of a primary key, which is usually a composite key made up of all the foreign keys. The facts in the fact table include either detail-level facts or aggregated facts.

The dimension table is composed of one or more hierarchies that classify the data. If there are no hierarchies in a dimension table, then it is termed a flat dimension or a list. The primary keys of a dimension table are components of composite primary keys of the fact table. The attributes of a dimension table are descriptive and textual values. A dimension table is smaller in size than a fact table.

To put all this information about fact and dimension tables into context, let’s look at an example. Consider the following fact table, called Sales_Revenue:

Table Sales_Revenue

Now consider the dimension tables Product_Number, Region_ID, and Customer_ID that expand on the Sales_Revenue fact table:

Tables Product_Number, Region_ID, Customer_ID, and Sales_RepID

Based on this example, Figure 2.3 outlines the way the star schema would look.

Following are the key characteristics of the star schema in this example:

• Each dimension is represented by only one dimension table.

• The fact table consists of a key and a measure.

• Each dimension table consist of a set of attributes.

• The dimension tables are joined with the fact table via foreign keys.

• The dimension tables are not joined to each other directly.

• The performance compared to that of the snowflake schema is better as the structure is simpler, and queries are faster.

Next, we will cover the snowflake schema.

Snowflake Schema

The snowflake schema is an expansion or extension of the star schema in that the dimension tables are further connected to sub-dimension tables. The representation of the fact table, dimension tables, and sub-dimension tables resembles the shape of a snowflake—hence the name snowflake schema. Figure 2.4 shows the structure of the snowflake schema.

As shown in Figure 2.4, the fact table is surrounded by dimension tables; and in addition, sub-dimension tables are associated with dimension tables through a many-to-one relationship. Each dimension table represents one level in the hierarchy that is extended by one or more sub-dimension tables. There is no limit to the sub-dimensions that can be created for a given dimension table.

Consider the sub-dimension tables for the dimension tables Product_ID and Sales_Rep_ID:

Dimension Table Product_Number with Sub-dimension Tables Product_PaaS and Product_SaaS

Figure 2.5 shows these tables in the snowflake schema.

The key characteristics of the snowflake schema are as follows:

• The performance is slower compared to that of the star schema due to the presence of multiple tables.

• The schema is easier to implement than the star schema primarily due to the extensive use of dimension tables.

• The snowflake schema normalizes information.

• Operational maintenance of the snowflake schema is difficult because there are many tables.

Table 2.1 provides an overview of the differences between the star and snowflake schemas.

Keeping Current and Historical Information

Information that is current—or the “now” information in a data warehouse—is typically leveraged for purposes of producing reports, dashboards, and data-driven business metrics, which enable an organization to make informed decisions. For example, the current information about how many employees have achieved their sales targets enables a business to project the near-future sales quota that should be created for the sales reps as well as define the market cap the company should pursue.

On the other hand, keeping historical information implies that an organization has access to historical records and can base future decision making on what happened a while ago. For example, an organization might look at how product A did vs. product B across the last 3 to 5 years to determine whether there’s any reason to keep the lower-performing product in the market when investment in R&D of the higher-performing product would yield better results that drive the organizational goals and keep it ahead of competition.

In order for an organization to effectively and efficiently use a data warehouse, the data warehouse must be designed such that users are able to analyze historical and current information and drive decisions based on data points.

Slowly Changing Dimensions

Slowly changing dimensions (SCDs) refers to the concept where data dimensions in a data warehouse contain both current and historical data, and the data dimensions change over time (though not necessarily periodically). SCDs are mostly seen as a progressive factor among data engineers, administrators, architects, and consultants as they have to manage and keep on top of changing attributes.

Why keep both current and historical data in a data warehouse—and why bother keeping historical data at all? The rationale for keeping historical data has already been mentioned: Analyzing historical data can help in making future decisions. Whether to keep historical data is largely a business decision that is likely to be governed by the business needs and goals of an organization. A business may require historical insights to reflect on what is possible tomorrow to drive sales, for example.

To further contextualize SCDs, let’s take a look at an example. In an organization, the sales department has to maintain data for its customers as well as sales reps and products. As time progresses, sales representatives may move on from the organization. Moreover, customer accounts may change as the organization acquires new customers and loses old ones. In addition, the organization’s products will continue to evolve, with new versions/releases replacing old ones. In this case, keeping track of how many customers have been acquired or lost is an important metric for the business to have and will drive its decisions related to attracting and retaining more customers. The product lines that are no longer relevant may just be archival data; this data would not have any impact on future products. Finally, the data related to sales reps who are no longer with the organization can provide valuable insights on controlling attrition in the workforce.

Let’s take a look at the various SCDs and how they can help organizations adopt effective ways to manage the information in their data warehouses.