SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Unknown dimension values or


Unknown dimension values or

Author
Message
Rob Taylor
Rob Taylor
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3708 Visits: 1616
In your DW do you always create an "-1 Unknown" record for each dimension? For example in DimEmployee:

EmployeeKey -1
EmployeeName "Unknown"
ect.

Or do you just rely on setting SSAS's UnknownMember and UnknownMemberName properties?
http://technet.microsoft.com/en-US/library/ms170707%28v=sql.105%29.aspx

Just curious what other folks do.

Thanks,
Rob
PMwar
PMwar
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 228
Unknown, Not Applicable, ...

I think these allow you more control over the design and data loading.

PMWar
RonKyle
RonKyle
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6724 Visits: 3619
I always have an unknown in a dimension, and it's key is 0. Then the sql statements can be COALESCE(value, 0). Most of the time using 0 is not an issue, but occassionally it makes it problematic. Not such a problem that I regret doing it that way.

I often has an N/A dimension, and it's key is -1. There is only an N/A when it makes sense. So Creation Date does not have an N/A. An open order has N/A for complete date. A completed order that for some reaons doesn't have a date would be classed as Unknown.

I'm not sure how setting the unknown value will work, so I can't compare the two. It maybe that I'm using old fashioned methods, or it may be that my desire for the greater control is a good thing. It would be interesting to hear from someone who has done both.



Jason-299789
Jason-299789
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4755 Visits: 3232
I tend to use -1 with Unknown as the description, and occasionally also use -2 with Undefined at Source.

The two are very different especially when loading data from 2 or more systems and conslidating them, where System one might have an value where system two doesnt recognise that particular field and theres no mapping.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Bruce W Cassidy
Bruce W Cassidy
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2989 Visits: 1033
I like the idea of unknown versus undefined at source. I stay away from using zero (although I have done so in the past), as zero is often a valid value. If you're using a true surrogate key, then that's not an issue, but often we muddy the waters by using a natural key (such as using time for a time dimension, and zero may well be a valid time).

I model in the relational DW, and then project that into SSAS, rather than model in SSAS. I guess if you model directly in SSAS without a relational DW, then you may do that there and not worry so much about it.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search