Chapter 9 : Financial Services

Chapter 9 : Financial Services

The financial services industry encompasses a wide variety of businesses,

including credit card companies, brokerage firms, and mortgage providers.

This chapter will focus primarily on retail banks given that most readers have

some degree of personal familiarity with this type of financial institution. A

full-service bank offers a breadth of products, including checking accounts,

savings accounts, mortgage loans, personal loans, credit cards, and safe

deposit boxes. This chapter begins with a very simplistic schema. We then

explore several schema extensions, including handling of the bank’s broad

portfolio of heterogeneous products that vary significantly by line of business

Causal dimensions. These dimensions, such as promotion, contract, deal,

store condition, or even weather, provide additional insight into the cause

of an event.

Multiple date or time-stamp dimensions. Refer to Chapter 5 for sample fact

tables with multiple date stamps, especially when the fact table is an accumulating

snapshot.

Degenerate dimensions. These dimensions identify operational transaction

control numbers, such as an order, invoice, bill of lading, or ticket, as illustrated

initially in Chapter 2.

Role-playing dimensions. Role-playing occurs when a single physical

dimension appears several times in a fact table, each represented as a

separate logical table with unique column names through views.

Status dimensions. These dimensions identify the current status of a transaction

or monthly snapshot within some larger context, such as an account

status.

Audit dimension. As discussed in Chapter 8, this dimension is designed to

track data lineage and quality.

Junk dimensions. These consist of correlated indicators and flags, as

described in Chapter 5.

Minidimensions

It is unreasonable to rely on the type 2 SCD technique to track changes in the

account dimension given the dimension row count and attribute volatility,

such as the monthly update of credit bureau attributes. Instead, we break off

the browseable and changeable attributes into multiple minidimensions, such

as credit bureau and demographics minidimensions, whose keys are included

in the fact table. This recommendation was illustrated in Figure 6.4. The minidimensions

allow us to slice and dice the fact data based on a lengthy list of

attributes while readily tracking attribute changes over time, even though they

may be updated at different frequencies. While minidimensions are extremely

powerful, we need to be careful that we don’t overuse the technique. However,

account-oriented financial services are a good environment for using the minidimension

technique because the primary fact table is a very long-running

periodic snapshot. Thus a fact table row exists for every account every month.

This fact row provides a home for all the foreign keys and links them together

so that we can always see the account together with all the other minidimensions

for any month.

Minidimensions should consist of correlated clumps of attributes; each attribute

shouldn’t be its own minidimension or we’d end up with too many dimensions

in the fact table.

As described in Chapter 6, one of the compromises associated with minidimensions

is the need to band attribute values in order to maintain reasonable

minidimension row counts. Rather than storing extremely discrete income

amounts, such as $31,257.98, we store income ranges, such as $30,000-$34,999

in the minidimension. Similarly, the profitability scores may range from 1

through 1,200, which we band into fixed ranges such as less than or equal to

100, 101-150, 151-200, and so on in the minidimension.

Household Dimension

From the bank’s perspective, a household may be comprised of several

accounts and individual account holders. For example, consider John and

Mary Smith as a single household. John has a checking account, and Mary has

a savings account. In addition, John and Mary have a joint checking account,

credit card, and mortgage with the bank. All five of these accounts are considered

to be a part of the same Smith household despite the fact that minor

inconsistencies may exist in the operational name and address information

The decision to treat accounts and households as separate dimensions is somewhat

a matter of the designer’s prerogative. Despite the fact that accounts and

households are correlated intuitively, we decide to treat them separately

because of the size of the account dimension and the volatility of the account

constituents within a household dimension, as referenced earlier.

Multivalued Dimensions

As we just saw in the John and Mary Smith example, an account can have one,

two, or more individual account holders, or customers, associated with it.

Obviously, we cannot merely include the customer as an account attribute;

doing so violates the granularity of the dimension table because more than one

individual can be associated with an account. Likewise, we cannot include

customer as an additional dimension in the fact table; doing so violates the

granularity of the fact table (one row per account per month) again because

more than one individual can be associated with any given account. This is a

classic example of a multivalued dimension, which we’ll develop fully in

Chapter 13. For now, suffice it to say that to link an individual customer

dimension to an account-grained fact table requires the use of an account-tocustomer

bridge table, as shown in Figure 9.3. At a minimum, the primary key

of the bridge table consists of the surrogate account and customer foreign

keys.

An open-ended many-valued attribute can be associated with a dimension row by

using a bridge table to associate the many-valued attributes with the dimension

Point-in-Time Balances

Assuming that business requirements already have driven the need to make

transaction detail data available for analysis, we could leverage this transaction

detail to determine an arbitrary point-in-time balance. To simplify matters,

we’ll boil the account transaction fact table down to an extremely simple

design, as illustrated in Figure 9.5. The transaction type key joins to a small

dimension table of permissible transaction types. The transaction sequence

number is a continuously increasing numeric number running for the

lifetime of the account. The final flag indicates whether this is the last

transaction for an account on a given day. The transaction amount is selfexplanatory.

The balance fact is the ending account balance following the

transaction event.

Like all transaction-grained fact tables, we add a row to the fact table in

Figure 9.5 only if a transaction occurs. If an account were quiet for two weeks,

perhaps January 1 through 14, there would be no rows in the fact table for the

account during that time span.

Leveraging the transaction fact table for dual purposes requires that the fact

table is absolutely complete and accurate. Every transaction against the

account must appear in this fact table, or else the running balance will not be

accurate. A late-arriving transaction row would require sweeping forward

from the point of insertion in that account and incrementing all the balances

and transaction sequence numbers. Note that we haven’t explicitly used the

transaction sequence number in this discussion, although it is needed in this

design to reconstruct the true sequence of transactions reliably and to provide

the basis of the fact table’s primary key, which is the date, account, and

sequence number. We prefer using the sequence number rather than a time-ofday

stamp because differences between the sequence numbers are a valid measure

of account activity.

Heterogeneous Product Schemas

The keys of the custom product dimensions are the same keys used in the core

product dimension, which contains all possible product keys. For example, if

the bank offers a $500 minimum balance with no per-check charge checking

product, the product would have the same surrogate key in both the core and

custom checking product dimensions. As we discussed in Chapter 3, establishing

conformed dimensions is essential to an extensible data warehouse

architecture. Each custom product dimension is a subset of rows from the core

product dimension table. Each custom product dimension contains attributes

specific to a particular product type.

This heterogeneous product technique obviously applies to any business that

offers widely varied products through multiple lines of business. If we worked

for a technology company that sells hardware, software, and services, we can

imagine building core sales fact and product dimension tables to deliver the

global customer perspective. The core tables would include all facts and

dimension attributes that are common across lines of business. The core tables

would then be supplemented with schemas that do a deep dive into custom

facts and attributes that vary by business. Again, a specific product would be

assigned the same surrogate product key in both the core and custom product

dimensions.

A family of core and custom fact tables is needed when a business has heterogeneous

products that have naturally different facts and descriptors but a single

customer base that demands an integrated view

Context-dependent dimension outriggers

In the case of account-oriented financial services, when a product is sold to a

customer, a new account is opened. In the case of some banking products, such

as mortgages, more account-specific descriptive information is collected when

the account opening occurs. For example, the bank may offer a 15-year fixedrate

mortgage at a given rate. When the mortgage originates, the bank will

know more about the specific property, including the address, appraised

value, square footage, home type (for example, single-family, townhouse, condominium,

trailer), construction type (for example, wood frame, brick,

stucco), date of construction, and acreage. These attribute values differ by

account, so they don’t belong in the what-the-bank-sells product dimension.

As shown in Figure 9.7, we can envision an account dimension outrigger for

some account types