Data Warehouse Model: Diffuse Relationships

Data Warehouse Model: Diffuse Relationships

Data Warehouse Model: Diffuse Relationships

A diffuse relationship is one of a group of similar relationships, which broadly apply to entities in a model. We can show them explicitly, but such an approach can become verbose and obscure the deeper content of a model. We coined the term “diffuse relationship” as we haven’t seen this phenomenon named by others.

Consider the following data warehouse model. The relationships to Source_System, Data_Warehouse_User, and Batch_Load_Run are diffuse relationships. Many of the tables have a source_system_key, created_by_key, updated_by_key, and batch_load_key. You can see the repetition, even in this small example.

The model is a proper model in that all relationships are defined. We can readily generate DDL code including foreign key constraints.

However there’s an aesthetic problem. The diffuse relationships cloud the underlying deeper meaning. Furthermore, if we consider the full warehouse with several hundred tables, we have severe clutter in the model. One purpose of a model is to generate DDL and the example model does that well. But another purpose is to foster deep understanding of a problem and communicate to others – the clutter of diffuse relationships undermines this latter purpose.

Read Also:
We’ve given artificial intelligence too much power far too quickly

Here’s a restated model where we omit the diffuse relationships.

Clearly the simplified model is better at showing meaningful content. End users that are writing analytical queries would much prefer the simplified model to the verbose one.

If we want to have foreign key constraints, we have two choices.



Sentiment Analysis Symposium

27
Jun
2017
Sentiment Analysis Symposium

15% off with code 7WDATA

Read Also:
Data-Driven, Analytics-Driven Decisioning

Data Analytics and Behavioural Science Applied to Retail and Consumer Markets

28
Jun
2017
Data Analytics and Behavioural Science Applied to Retail and Consumer Markets

15% off with code 7WDATA

Read Also:
We’ve given artificial intelligence too much power far too quickly

AI, Machine Learning and Sentiment Analysis Applied to Finance

28
Jun
2017
AI, Machine Learning and Sentiment Analysis Applied to Finance

15% off with code 7WDATA

Read Also:
Why Machine Learning Is Hard to Apply to Networking

Real Business Intelligence

11
Jul
2017
Real Business Intelligence

25% off with code RBIYM01

Read Also:
Why Machine Learning Is Hard to Apply to Networking
Read Also:
Why Machine Learning Is Hard to Apply to Networking

Advanced Analytics Forum

20
Sep
2017
Advanced Analytics Forum

15% off with code Discount15

Read Also:
MIT makes breakthrough in morality-proofing artificial intelligence

Leave a Reply

Your email address will not be published. Required fields are marked *