It’s been awhile since we talked about factless fact tables in Design Tip #50. You may recall that a factless fact table is “a fact table that has no facts but captures the many-to-many relationship between dimension keys.”
We’ve previously discussed factless fact tables to represent events or coverage information. An event-based factless fact table is student attendance information; the grain of the fact table is one row per student each day. A typical coverage factless fact table in retail includes one row for every item being promoted during the time it is promoted; it is used to help answer the “what didn’t happen?” question identifying items being promoted but not sold.
Factless fact tables can simplify the overall design. Think of a property and casualty insurance company providing automobile coverages. It’s reasonable to create a transaction fact table that captures the written premiums resulting from a new sale or change to an existing policy. Likewise, it makes perfect sense to implement a monthly snapshot fact table to capture the earned premium associated with every policy by customer, by named insured, by household, by vehicle, by driver and so on.
But real life complications jump up and the design seems to get horribly complex in an effort to capture the relationships between dimensions and the changes in these relationships over time. For example, a single driver can relate to multiple vehicles, policies and households. Of course, in a similar manner there can be multiple drivers on a single vehicle, single policy or single household.
This complexity can cause the design team significant headaches. The next thing they know the design is littered with bridge tables. Even then, many of the design challenges are not well suited for a bridge table solution as they require three, four or more dimensions. In the end, the designs get too complex, too hard to understand, and provide poor query performance.
The key to avoiding this situation is recognizing there are multiple business processes at play and designing a solution to include additional fact tables. The problem is many design teams fail to understand there are multiple business processes involved as they cannot visualize the “facts” that would result, forgetting about the factless fact table. Tracking drivers associated to vehicles is not the same business process as recognizing the earned premium related to a policy each month. Recognizing multiple business processes will result in a simplified design for the core transaction and snapshot fact tables surrounded by several factless fact tables that help track the relationship between other dimension tables.
To finish our example, a property and casualty design might include factless fact tables to support:
- Household involved parties – One row per household and insured party with begin and end effective dates.
- Policy involved parties – One row per policy, household, insured item, and insured party with begin and end effective dates.
- Vehicle involved parties – One row per policy, driver and vehicle with begin and end effective dates.
On the claims side of the business, complex claims may result in several to dozens of claim handlers involved in the resolution of a claim. Another factless fact table can capture the relationship of each claim handler, their role, the coverage and other details of the claim.
Other industry examples that can benefit from utilizing this design pattern include financial services where it is common to have multiple accounts and multiple individuals moving in and out of households over time. Similarly in long running, complex sales situations, a factless fact table may be helpful to identify all the sales support resources involved with various clients and products.
It’s important to remember that the use of factless fact tables does not make the complexity of our examples go away. The complexity is real! But the factless fact tables allow us to package the complexity in clean, understandable ways. BI users will find these factless fact tables natural and intuitive.