Post reply

Unknown dimension values or

  • November 3, 2012 at 5:44 pm

    #284350

    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

  • November 5, 2012 at 10:50 am

    #1556158

    Unknown, Not Applicable, ...

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

    PMWar

  • November 6, 2012 at 7:10 am

    #1556448

    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.

  • November 8, 2012 at 8:21 am

    #1557208

    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

  • November 21, 2012 at 11:54 am

    #1561359

    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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply