DWH modelization - Relationship between 2 fact tables

Question

Hy,

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

My context :

• "job offer" : contains the list of all job offer. A job offer is created by a company, have a validation date and can be publish to a jobboard (like a likedIn)
• "candidaties" : a candidate applies to a job offer published to a joboard

"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

Answer 1

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