Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Unknown dimension values or Expand / Collapse
Author
Message
Posted Saturday, November 3, 2012 5:44 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 2:47 PM
Points: 1,170, Visits: 1,211
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
Posted Monday, November 5, 2012 10:50 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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
Posted Tuesday, November 6, 2012 7:10 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 2:41 PM
Points: 815, Visits: 2,020
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
Posted Thursday, November 8, 2012 8:21 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Posted Wednesday, November 21, 2012 11:54 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 958, Visits: 1,031
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 »

Add to briefcase

Permissions Expand / Collapse