How to Model mapping between products with varying number of descriptive attributes?

[Bergtroll]

Hello again,

I have another problem to solve. It's about unifying product mapping from external systems to an internal reference system. The reference system uniquely identifies a product using three attributes, e.g. manufactoring site, quality and delivery type. The external systems are using different attribute sets, to identify their products, e.g. supplier and distributor. Since there are a lot of different systems, it is unpredictable how many attributes are necessary to map a certain external systems product to one or more of the internal system in advance. Nevertheless in the end all the systems describe the very same set of products and therefore I want to have a single mapping fact table. But how do I model an external_product dimension when I do not know, how many columns to use? Using a bridge? How?

Kind regards,

Marius

[Mike Honey]

Hi Marius,

I'm imagining an external_product dimension with a concatenated key e.g. nvarchar(900) populated in the format: <Source System>|<Source Key Name 1>=<Source Key Value 1>|<Source Key Name 2>=<Source Key Value 2>...

I'd start with a <Source System>| value to ensure you don't get clashes across systems. So following your example you might have a key value like: "SystemA|Supplier=ACME|Distributor=XYZ"

An alternative method is to build the key as an XML structure, stored as a string. This is more verbose but also more intuitive/transparent.

The attributes of this dimension would be your internal reference key, the Source System, and then the union of all the attributes needed from each external system. E.g. if several external systems provide a "Supplier", I'd load them all into a single Supplier attribute, with a default value (e.g. Unspecified) for the external systems that dont provide "Supplier". In this way, the list of attributes can expand (as new systems are added) without affecting your existing keys.

Good luck!
Mike

[ykud]

I've always used a rather simple approach of implemeting a mapping table like
'primary refernce key'|'source_system'|'source_system_key'|'source_system_key_attribute1'|....|''|
and then just loaded the existing fields for new systems + added the neccessary columns for mapping just this system. At the end of a day, even 20 systems won't generate that much “new” attributes.

And then you point some mapping tool for users to adjust table values (pick correct reference products), add notifications for unmapped products and you're all set.

Take a look at this post, it describes mapping tables approach in detail
http://blog.kejser.org/2011/08/04/transforming-source-keys-to-real-keys-part-1-introducing-map-tables/

[Bergtroll]

Hi ykud,

http://blog.kejser.org/2011/08/04/transforming-source-keys-to-real-keys-part-1-introducing-map-tables/

thank you for pointing me to the blog. Since the mapping approach taken there is quite the same than my first shot on that, I feel more confident that I am on the right path. I evaluated the use of additional columns for each system to map, but that will result into many columns quite fast. I also evaluated to model it using a bridge table as shown in bridge table example http://www.kimballgroup.com/html/10dt/DT124AlternativesMulti-valuedDimensions.pdf. I am quite sure by now, that this approach would work from a technical point of view. On the other hand the business users want to see the mappings used for a certain system nevertheless. And since the construct is quite hard to query I finally decided to use a dedicated scheme mapping in which every source system has its own mapping facts table to the conformed product dimension.

Kind regards,
Bergtroll

[ykud]

It's nice that you've found your solution.
Cheers.

[Sponsored content]