Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lazerath
Constraint Violating Yak Guru
343 Posts |
 Posted - 2010-05-11 : 17:11:56
|
| I inherited a Data Warehouse with a FACT table representing an event: a load of laundry. There is a metric called "Turn around time" that is the duration between wash loads. In order to calculate this metric, the ETL has to compare each record to the next chronological record and determine the difference. As such, I do not believe this metric is part of a single wash load and belongs to this FACT. I am still learning of DW design and would appreciate input on the correct way to handle this. I was thinking of creating a new FACT to store these turn around events. Is that appropriate? |
|
|
dportas
Yak Posting Veteran
53 Posts |
Posted - 2010-05-11 : 17:43:10
|
| Duration sounds like an attribute of the load to me. You are better placed to understand your data than I am, but just suppose that the data source already included the duration so that you didn't need to compare each row to the next in order to calculate it. Would you still say in that case that it had to go in another table? My point is that how it's calculated is not necessarily important because the ETL process is just a means to an end.Ask yourself whether each attribute is a fact about the key(s) of this table (ie. does it satisfy Boyce Codd Normal Form). If it is then I don't think it needs to change. |
 |
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2010-05-11 : 18:25:10
|
| Think about "Turn around time" as the span of time there is no laundry in the washer. Run duration, which seems to be what you are referencing, is the amount of time a load of laundry is in the washer and it is most certainly a valid metric. Technically, turn time is the opposite because the only way to calculate it is by the absence of a wash load. |
|
|
|
|
|
|
Data Warehouse Design - Time between Events