Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Time Dimension, NULLs and Time datatype

2 posters

Go down

Time Dimension, NULLs and Time datatype Empty Time Dimension, NULLs and Time datatype

Post  Paul Tiseo 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

Back to top Go down

Time Dimension, NULLs and Time datatype Empty Re: Time Dimension, NULLs and Time datatype

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Time Dimension, NULLs and Time datatype Empty Re: Time Dimension, NULLs and Time datatype

Post  Paul Tiseo 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

Back to top Go down

Time Dimension, NULLs and Time datatype Empty Re: Time Dimension, NULLs and Time datatype

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Time Dimension, NULLs and Time datatype Empty Re: Time Dimension, NULLs and Time datatype

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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