Fact table with multiple date ?

View previous topic View next topic Go down

Fact table with multiple date ?

Post  Okidoo on Fri May 14, 2010 11:24 am

Hi,

I am modling a fact table for prisoners inscription in order to track historical changes. An admission is the hole stay comprise of one or many inscriptions usually separated by transferts between institutions.

In the transactionnal system, it is all saved in the same table : Inscriptions.

Prisoner:
prisoner_no, name, city, education_level, ...

Inscription:
inscription_no, prisoner_no, institution_code, insc_start_date, insc_start_reason, insc_end_date, insc_end_reason, ... (there are not any important measure in this table)

Possible value for insc_start_reason include reason for an initial admission or transfer from other institutions
Possible value for insc_end_reason include transfer to other institutions or end of sentence.

The ETL is done every month. We want to be able to keep historical data to be able to go back to a month in particular. I want to keep track of history of Prisoner.City and prisoner.education_level too.

As it is, I don't know how I can model Admission or inscription to keep historical data because there are two dates. It seams to me that there are too many facts in the same fact table. If I do a SCD type 2 in the prisonner table, I will not be able to bind the right prisonner surrogate key with a fact because for some reports I would need to consider the start of inscription date and for some other, I would need the end of the inscription date.

Any Idea of how to model this things in one or many fact tables ?

Okidoo

Posts : 3
Join date : 2010-03-30

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum