Good answers! Now back to your problem with the isValidNumber thing. My great idea is to create a junk dimension for it. You'll see what I mean below but the main reason for that recommendation, and my suggestions on the whole DB design, is this: cardinality. Your external number dimension will get kind of big. A junk dimension will hold various common flags and strings which will help trim it down. And your fact table will get really big. At 100K records per day that's 20M to 36.5M records per year. That's not unmanageable but it's up there. At that size, I think you will need to have partitions (totally handled by the database server) but that can wait for 6 months or more. The other thing you can do to help with the size factor is keep the fact table narrow. For that reason, among others, I completely withdraw my suggestion of a degenerate dimension. Knowing what I know now about your application you certainly should not do that.
And here are my thoughts...in the attached PDF...because they are kind of long. Sorry about that.
Last edited by darrell.nelson; 12-07-2013 at 03:54 PM.
Windows 7 (64 bit)