February 25, 2009 at 9:27 am
This is probably easy but it's new to me, and there are no good design guides out there.
I am bringing records into my cubes that can have null dates (open records with an empty close date). I have set up a date dimension and the first entry contains null values with a key of '1'.
The store procedure that populates the fact table with the date key never finds this record as it joins on the date.
This means the foreign key on the fact table is set to null, and this is currently causing the deploy to fail.
What is the easiest way to put a 1 in the foreign key of the fact table. Do I just do a second parse of table, check the column and if it is null, set it to 1? Is there a slick piece of code out there, that does things a little less clumsily?
Are there other options that I just don't know about?
I've spent most of the day looking for answers but when I get close, I find the article is riddled with terminology that I then have to look up. If anyone has a link to a jargon-free site where I can get some design tips that would be really helpful too.
TIA
February 25, 2009 at 10:15 am
depends a little on how you're populating your fact, but assuming for the moment you're using straight TSQL to move data from some staging area to the fact, then you could use ISNULL( , 1) where this replaces any nulls with 1. An alternate is the coalesce function, same outcome but allows cascading choices for value.
Just out of interest, if using AS2K5 you could make use of the built in 'Unknown' member for a dimension. Turn this on at the dimension level, and then modify your processing instructions to allow for missing key errors.
Lastly, get on to your proj sponsors, get them to get ont he back of whoever is doing data entry - show them what info they miss out on by not getting all of the data (ie using either your approach or the unknown, all of those entries are bucketed together, basically removing any analysis capability for that part of the data).
HTH,
steve.
Steve.
February 27, 2009 at 4:18 am
Thanks Steve. I went for option 1, using ISNULL and it worked a treat.
I'll give some thought to 'Unknown'. As regards non completion of fields by the business, in this case it's valid for the close date to be null, but that's not always the case with other data, and I like the idea of being able to flag this up easily.
Thanks again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply