Datatype of numeric dimension data

#1

I understand that it is probably not optimal design, but our source data has
some "holes" in it. That is, certain dimensions are not populated in every
record. For text fields I can update null fields to a value such as 'N/A'
during the load into the fact table. For numeric fields, I can't use 'N/A'
unless I change the datatype to text. Are there any consequences if I do this?

#2

In article <113DA521-771E-4E99-9873-570EC3EA1B62 (AT) microsoft (DOT) com>,
KitenutDave (AT) discussions (DOT) microsoft.com says...

Quote:

I understand that it is probably not optimal design, but our source data has
some "holes" in it. That is, certain dimensions are not populated in every
record. For text fields I can update null fields to a value such as 'N/A'
during the load into the fact table. For numeric fields, I can't use 'N/A'
unless I change the datatype to text. Are there any consequences if I do this?

The usual pattern for dealing with this sort of issue is to add a record

to your dimension table to catch these 'unknown' records. I often use an
ID of 0 or -1 across all my dimensions. When inserting records into the
fact table I then COALESEC() null values to the 'unknown' ID.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Datatype of numeric dimension data

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

Datatype of numeric dimension data - 04-17-2006 , 04:38 PM

Re: Datatype of numeric dimension data - 04-18-2006 , 07:52 AM