Time Dimension, NULLs and Time datatype

View previous topic View next topic Go down

Time Dimension, NULLs and Time datatype

Post  Paul Tiseo on Fri Jan 10, 2014 1:28 pm

We need to design a Time dimension. Essentially, it would be:

TimeSK int
Time time
...


How do people handle the various types of unknowns. It's easy in other types of dimensions where the SK is set to negative values, and there's a varchar that can carry a descriptive label. Even datetimes can usually be filled with a dummy date that is obviously wrong. But, there's no "wrong" time. What are the common practices to employ here?

Paul Tiseo

Posts : 3
Join date : 2013-12-12

View user profile

Back to top Go down

Re: Time Dimension, NULLs and Time datatype

Post  ngalemmo on Fri Jan 10, 2014 1:40 pm

What do you mean by 'unknown'? áDimensions have natural keys and surrogate keys. áDuring load you use the natural key to locate the surrogate key.

There are unknowns where you have no natural key (i.e. its null) and you have unknowns where you have a natural key but it can't be found in the table.

With nulls it is common practice to assign a specific negative surrogate key (with a corresponding row in the dimension table), although equally effective is to treat it as any other new natural key. áStandard practice with new unknown natural keys is to create an inferred row in the dimension. áThat is, you create a row, assign a surrogate key and load the natural key and whatever other useful information from the source. áFor other dimensions you usually get correct information later from the appropriate source (i.e. a later arriving dimension scenario), for dates you would need to decide if those rows get populated and with what.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Time Dimension, NULLs and Time datatype

Post  Paul Tiseo on Fri Jan 10, 2014 5:55 pm

Specifically, I am talking about what is described in "Handling Null Foreign Keys in Fact Tables" in http://www.kimballgroup.com/2010/10/06/design-tip-128-selecting-default-values-for-nulls/

I assign the usual 0, -1, -2, and -3 to the surrogate key, and the natural key, when a varchar, gets the "Missing Value", etc., values. But, what happens when the natural key is a time? What have people done here? I guess I'm asking for practical ideas to the theoretical concept with which I agree...

Paul Tiseo

Posts : 3
Join date : 2013-12-12

View user profile

Back to top Go down

Re: Time Dimension, NULLs and Time datatype

Post  ngalemmo on Fri Jan 10, 2014 6:21 pm

ngalemmo wrote:
Standard practice with new unknown natural keys is to create an inferred row in the dimension. áThat is, you create a row, assign a surrogate key and load the natural key and whatever other useful information from the source. áFor other dimensions you usually get correct information later from the appropriate source (i.e. a later arriving dimension scenario), for dates you would need to decide if those rows get populated and with what.

I am referring here to unknowns that are not null. Time, customer numberů it doesn't matter.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Time Dimension, NULLs and Time datatype

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