Hi all,
I have a location dimension that is sourced from our GIS system
I have a number of fact tables that are sourced from four different systems that I want to join to my location dimension
Only one of the fact table systems references the natural key of the GIS system so no trouble there.
The problem is the remaining 3 fact sources. I plan on using a mapping table to map the GIS natural key to the various disparate and potentially duplicate location references from the 3 fact sources. But I can't think of a way to handle inferred members for entries that don't exist in the mapping table.
What are some of the design patterns to use in this situation?
The simplest one I can think of would be to have either add one global placeholder '-2|Not In Mapping Table|unkown|unkown|unkown' dim record or one for each source .. But that would mean complexity down the line to fix the fact records once I added the mapping record. I'd need to either reload all my facts or do some other complex 'data fix' query to gloss over the fact that i'm not materialising inferred dimensions directly in the dim table. Something I'd rather avoid
Another is creating a new 'natural' key based on the fact source system + its location pair, and use that in the dim and mapping tables. But this'll create a similar problem to above when I add the entry to the mapping table for an inferred member and it points to an existing GIS sourced location. I'd have "logical" duplicates.
Are there any other approaches? any pitfalls of the above that I need to be aware of?
Thanks
Adelaide, Australia
.
social.technet.microsoft.com/Forums
