Fact Table that is mostly dates
3 posters
Page 1 of 1
Fact Table that is mostly dates
I have a reporting requirement to report status on a hierarchy that includes Orders, Items within those orders, and locations for each item. A one to many hierarchy all the way down. So far, there are no numerically additive facts I can find. The reports are all about the status of an order and it's parts. At each level of the hierarchy, there are 20 or so dates we track, and some other codes and descriptive information. I've moved the codes to dimensions, but that leaves me with a fact table with dimension keys only, including 20 or so role typed date dimension keys. I have two design issues:
1. Does it sound right to have a fact table with the only "facts" just dates?
2. To make this more manageable (about 15 of the 20 dates are repeated at each level of the order-item-location hierarchy), I've collapsed the fact so that a row could be at any one of the three levels, based on a level indicator. So there is an Actual_Completion_Date, Scheduled_Completion_Date, Equipment_Arrived_Date, etc. in the table, but these could refer to the Order, the Item, or the Location, depending on the indicator. And they are very well different values at each level. What do you think?
Steve
1. Does it sound right to have a fact table with the only "facts" just dates?
2. To make this more manageable (about 15 of the 20 dates are repeated at each level of the order-item-location hierarchy), I've collapsed the fact so that a row could be at any one of the three levels, based on a level indicator. So there is an Actual_Completion_Date, Scheduled_Completion_Date, Equipment_Arrived_Date, etc. in the table, but these could refer to the Order, the Item, or the Location, depending on the indicator. And they are very well different values at each level. What do you think?
Steve
spthomas- Posts : 3
Join date : 2011-11-16
Re: Fact Table that is mostly dates
Facts tables without facts/measures aren't a bad thing if used properly - they're referred to as factless facts.
You might need to implement an accumulating snapshot fact table.
From Wikipedia:
This type of fact table is used to show the activity of a process that has a well-defined beginning and end, e.g., the processing of an order. An order moves through specific steps until it is fully processed. As steps towards fulfilling the order are completed, the associated row in the fact table is updated. An accumulating snapshot table often has multiple date columns, each representing a milestone in the process. Therefore, it's important to have an entry in the associated date dimension that represents an unknown date, as many of the milestone dates are unknown at the time of the creation of the row.
You might need to implement an accumulating snapshot fact table.
From Wikipedia:
This type of fact table is used to show the activity of a process that has a well-defined beginning and end, e.g., the processing of an order. An order moves through specific steps until it is fully processed. As steps towards fulfilling the order are completed, the associated row in the fact table is updated. An accumulating snapshot table often has multiple date columns, each representing a milestone in the process. Therefore, it's important to have an entry in the associated date dimension that represents an unknown date, as many of the milestone dates are unknown at the time of the creation of the row.
min.emerg- Posts : 39
Join date : 2011-02-25
Re: Fact Table that is mostly dates
It sounds like you might be mixing grains. When each row reflects a different level of a hierarchy it means I need special knowledge to know which how to count (your default metric) and drill into your fact table. Typically, you will store all rows at the lowest grain and then aggregate through your dimension hierarchy. As previously mentioned factless facts are fine. Although you said you wanted to track statuses. So again, I think your default metric is just a row count column. You will sum that field when answering how many orders are in a complete status, etc. Flattening out your three rows to one row will increase the number of dates you need to carry, but you can always create views for the different levels of the hierarchy to help users select only dates for the particular level they are summing.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
![-](https://2img.net/i/empty.gif)
» Dates as NULLS in Fact Table
» Accumulating Fact Table Dates as Role Play Dimension and Descriptions
» Combination of SCD2, partial snowflake, Effective and end dates in fact table
» Fact with eff / exp dates referencing dimension with eff / exp dates
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» Accumulating Fact Table Dates as Role Play Dimension and Descriptions
» Combination of SCD2, partial snowflake, Effective and end dates in fact table
» Fact with eff / exp dates referencing dimension with eff / exp dates
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum