CODEWITHSUNDEEP
relationshipdata-warehousefact
Maxence Leurquin
Maxence Leurquin

Reputation: 1

DWH modelization - Relationship between 2 fact tables

Hy,

I realize a DWH and i have a probleme with the relationship between my tables.

My context :

"Job offer" is ambigous table, it be considered as a fact table (contain externals Id, have a Date property). can also consideres as dim table, have a many properties and id use another fact. job offer have a lot of row (several million)

I identified 3 facts : JobOffer, PublicationOffer, Candidaty. The fact table is a transactions and have only count measure (considered as factless)

I have difficulties to create relathionship because i have relationship between fact table (it's forbiden)

I think create two tables job offers, one dim table, the other fact table. Or considered job offer as a dim table. What the good choice?

Models representation: model 1 or model 2

My envisaged solution solution 1 or solution 2

THX

Upvotes: 0

Views: 126

Answers (1)

NickW
NickW

Reputation: 9778

designing a DWH should follow these broad steps

  1. Identify your reporting requirements
  2. Identify the measures that will deliver these reporting requirements
  3. Identify the dimensions that will allow you to aggregate and filter your measures in accordance with your requirements
  4. Define the grain of your fact table(s) which then drives which of your measures can be in the same fact table and which need their own fact table

If you follow this approach you should be able to answer your own question

Upvotes: 1

Related Questions