Unknown dimension values or

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Strategies and Ideas » Unknown dimension values or

Unknown dimension values or

Rate Topic

Display Mode

Topic Options

Author

Message

robert.gerald.taylor

Posted Saturday, November 03, 2012 5:44 PM

Right there with Babe

Group: General Forum Members
Last Login: Today @ 1:48 PM
Points: 740, Visits: 787

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

Post #1380754

PMwar

Posted Monday, November 05, 2012 10:50 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 2:26 AM
Points: 87, Visits: 228

Unknown, Not Applicable, ...

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

PMWar

Post #1381207

RonKyle

Posted Tuesday, November 06, 2012 7:10 AM

Say Hey Kid

Group: General Forum Members
Last Login: Today @ 9:32 AM
Points: 671, Visits: 1,509

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.

Post #1381556

Jason-299789

Posted Thursday, November 08, 2012 8:21 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 1:30 AM
Points: 803, Visits: 2,124

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

Post #1382527

Bruce W Cassidy

Posted Wednesday, November 21, 2012 11:54 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, March 22, 2013 11:49 AM
Points: 945, Visits: 998

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.

Post #1387560

« Prev Topic | Next Topic »

Permissions