InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 10:23:21 GMT20http://www.sqlservercentral.com/Forums/Topic1380754-363-1.aspxI 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.Wed, 21 Nov 2012 11:54:01 GMTBruce W Cassidyhttp://www.sqlservercentral.com/Forums/Topic1380754-363-1.aspxI 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. Thu, 08 Nov 2012 08:21:57 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1380754-363-1.aspxI [i]always[/i] 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.Tue, 06 Nov 2012 07:10:57 GMTRonKylehttp://www.sqlservercentral.com/Forums/Topic1380754-363-1.aspxUnknown, Not Applicable, ...I think these allow you more control over the design and data loading.Mon, 05 Nov 2012 10:50:34 GMTPMwarhttp://www.sqlservercentral.com/Forums/Topic1380754-363-1.aspxIn your DW do you always create an "-1 Unknown" record for each dimension? For example in DimEmployee:[code="plain"]EmployeeKey -1EmployeeName "Unknown"ect.[/code]Or do you just rely on setting SSAS's UnknownMember and UnknownMemberName properties?[url]http://technet.microsoft.com/en-US/library/ms170707%28v=sql.105%29.aspx[/url]Just curious what other folks do.Thanks,RobSat, 03 Nov 2012 17:44:31 GMTrobert.gerald.taylor