Fact Table that is mostly dates

View previous topic View next topic Go down

Fact Table that is mostly dates

Post  spthomas on Thu Sep 27, 2012 9:25 am

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


spthomas

Posts : 3
Join date : 2011-11-16

View user profile

Back to top Go down

Re: Fact Table that is mostly dates

Post  min.emerg on Thu Sep 27, 2012 9:47 am

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.

min.emerg

Posts : 39
Join date : 2011-02-25

View user profile

Back to top Go down

Re: Fact Table that is mostly dates

Post  BoxesAndLines on Thu Sep 27, 2012 9:15 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Fact Table that is mostly dates

Post  Sponsored content


Sponsored content


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