Showing posts with label inconsistent/reused dim reference keys. Show all posts
Showing posts with label inconsistent/reused dim reference keys. Show all posts

Tuesday, April 16, 2013

[SQL Server Data Warehousing] Design Pattern Question: Conformed dim used by facts sourced from different systems - inconsistent/reused dim reference keys


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

Monday, April 15, 2013

[SQL Server Data Warehousing] Design Pattern Question: Conformed dim used by facts sourced from different systems - inconsistent/reused dim reference keys


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

Friday, April 12, 2013

[SQL Server Data Warehousing] Design Pattern Question: Conformed dim used by facts sourced from different systems - inconsistent/reused dim reference keys


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

Wednesday, April 10, 2013

[SQL Server Data Warehousing] Design Pattern Question: Conformed dim used by facts sourced from different systems - inconsistent/reused dim reference keys


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

Search This Blog