Relationship profiling in data warehouse

by / Sunday, 30 August 2015 / Published in Billet
relationship profiling

Relationship profiling is an exercise in identifying entity keys and relationships as well as counting occurrences for each relationship in the data model. It is necessary to validate existing relational data models or build them when none are available.

Definition of relationship profiling

Relational data model describes high-level logical data structure using standard concepts, such as:

  • Entity is a class of structurally similar persons, things, places, concepts, or events about which the data is recorded. Each representative of an entity is called entity occurrence;
  • Attribute is a most primitive atomic characteristic of an entity;
  • Relationship is an association between occurrences of two entities;
  • Relationship cardinality indicates how many occurrences of each entity can participate in the relationship;
  • Primary key is a nominated set of attributes that uniquely identifies each entity occurrence;
  • Foreign key ties an attribute or a collection of attributes of one entity with the primary key of another entity.

In practice data models are often not kept up-to-date with the actual data. Entity-relationship profiling provides information about actual entity relationships. Several relationship profiling :

Identity profiling

Identity profiling checks primary keys and other unique keys within entities. It provides information about true identify of various entities and identifies any duplicates.

Reference profiling

Reference profiling checks foreign keys. It provides information about foreign key violation in the real data.

Cardinality profiling

Relationship cardinality is rarely represented correctly in relational data models. For example, optionality is frequently built into the entity-relationship diagrams simply because real data is imperfect. Strong entities are routinely allowed to have no corresponding weak entity records simply because database designers expect bad and missing data.

Cardinality profiling is used to understand true relationship cardinality. It is a simple exercise in counting all relationship occurrences. Once counted the results are presented in a cardinality frequency diagram. The diagram will show how many of the parent records have 0, 1, 2 and so on corresponding dependent records.


Others articles

State transition model 








state-transition timeline profiling Analyzing Mining Mining Profiling relationship profiling

Subject profiling Profiling

Get Free Email Updates!

Signup now and receive an email once We publish new content.

We will never give away, trade or sell your email address. You can unsubscribe at any time.

Please follow and like us:
David-Marc Petit

David-Marc Petit

Président at DWBI Expert
David-Marc PETIT est le président de DWBI Expert Inc. Il cumule plus de 20 ans d’expérience dans des entreprises de toutes tailles et tous secteurs, sur trois continents, en tant qu’expert en intelligence d’affaires. Il a fait de sa mission la démocratisation de l’intelligence d’affaires (Business Intelligence) pour optimiser les revenus et la performance de ses clients.
David-Marc Petit
David-Marc Petit

Latest posts by David-Marc Petit (see all)

Leave a Reply


Enjoy this website? Please spread the word :)