Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Unknown dimension values or


Unknown dimension values or

Author
Message
robert.gerald.taylor
robert.gerald.taylor
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1332 Visits: 1399
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 Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

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

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

PMWar
RonKyle
RonKyle
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1290 Visits: 3344
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
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
Right there with Babe
Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)

Group: General Forum Members
Points: 787 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