Aggregating data across multiple companies - Ideas Wanted

[workdan]

Hi all,

I've got an interesting project going on, and I'm looking for some suggestions. Here's the dilly:

Our data warehouse contains sales data from a number of client companies. They all sell the same items, but they may name them differently. For example, take an item called Widget A1. One client may refer to this item in their database as "Widget A1". Another client may refer to it as "Wid A", and a third might call it "WA1".

So the result is sales transactions of what is logically the same item, but with different names. My goal, of course, is to aggregate this sales data across all clients, so if I had a dataset like this:

Item Name    Units Sold
Widget A1          100
Wid A                  80
WA1                   125

My result is:

Item Name    Units Sold
Widget A1          305

Tricky, eh? There's no unique key across those different clients that we can use to link these items - we have to do it manually. We've come up with two possibilities:

1. Use report filters to catch all items named like your target. Problem with this is, it's not terribly accurate.

2. We do have the facility to create meta-groups of items that will be represented in the model. So we can manually add all distinct item names that we know to be instances of the target item to a group, and report on the group as a whole. Problem with this is, someone needs to maintain those groups. When items are removed or added, the group needs to be updated.

It may seem unreasonable for me to ask for solutions to such a highly-specialized problem. Really, all I'm looking for are suggestions, things we may not have thought of. Has anyone had to tackle something like this before? I'm sure we can't be the first! :)


Thanks for reading - any and all suggestions are appreciated.

- Dan

[COGNOiSe administrator]

I think option 2 is your best bet. Yes, it is extra maintenance, but it is also the only predictable way of generating accurate reports.

Create a web interface to your DM, whic would allow different people to maintain their own lists, and generate exception reports for records that do not match.

[goose]

Yup option 2 is the way to go, you can use fuzzy string matching to automate creating your meta-groups, this way you will have minimal admin except for exceptions.

http://en.wikipedia.org/wiki/Fuzzy_string_searching