How to deal with multiple facts in dimensional modelling

Question

I have a scenario where the technical team is requesting for single dimensional model combining different subject areas like finance, sales,etc.

eg: Fact table 1 -> Finance area gives P&L heads at month level

Month	P&L Head	Amount
2023-04-01	Net Sales	100 Mn
2023-04-01	Throughput	80 Mn
2023-04-01	EBITDA	20 Mn
2023-04-01	PBT	5 Mn

Fact table 2 -> Sales area gives monthly sales by product, customer

Month	Product	Customer	Sales Amount
2023-04-01	ABC	C1	2 Mn
2023-04-01	ABC	C2	3 Mn
2023-04-01	XYZ	C3	1 Mn
2023-04-01	XYZ	C1	2 Mn

3 Dimensions noted : Time, Product, Customer

2 Fact Measures noted : Sales Amount, P&L Head Amount

My query is Is it advisable to create single data model or keep them separate as per data modelling best practices

1. Keeping only Time dimension (Conformed dimension) in the common fact and all measures aggregated

Month	Net Sales Amount	Throughput Amount	EBITDA Amount	PBT Amount	Sales Amount
2023-04-01	100	80	20	5	8

2. Keeping all dimensions but the rows where dimension is not available to leave it blank

Month	Product	Customer	P&L Head	P&L Amount	Sales Amount
2023-04-01	ABC	C1	x	x	2
2023-04-01	ABC	C2	x	x	3
2023-04-01	XYZ	C3	x	x	1
2023-04-01	XYZ	C1	x	x	2
2023-04-01	x	Net Sales	100	x	x
2023-04-01	x	Throughput	80	x	x
2023-04-01	x	EBITDA	20	x	x
2023-04-01	x	PBT	5	x	x

3. Keep both data models separate

Star schema 1 -> for P&L data Star schema 2 -> for Sales data

Which of the above 3 are correct... Thankyou in advance.

Regards, Kiran

Answer 1

A fact table needs to have its grain defined and then all measures must adhere to that grain. So if you wanted a single fact table then only your first option (having date as the only dimension) is a possibility.

I'm guessing that you have other reporting requirements that will involve these measures so you're going to need separate fact tables anyway. Whether you also need to build this single fact table is a cost-benefit decision only you can make: is the cost of creating and maintaining this additional fact table outweighed by the benefit of being able to query a single table v. querying multiple tables and combining the results