dimension table 1

[lrnr]

I have to create a single dimension table from three different sources.The master "Vendor" list exists independently in all 3 sources with different key values and attribute set. The Vendor names although similar may not match exactly.

What is the most elegant/low maintenance way of consolidating data into single dimension table.

Thanks

 

[avjoshi]

I don't know about other ETL tools but Oracle OWB has name and address extension, which I am guessing you can use for that purpose.

Anand

 

[lrnr]

Thanks Anand for your response.
But we are not using any ETL tool, it has to be coded thru PL/SQL scripts.

All three systems are live and independent, although the VENDOR might be same its attributes(keys and even names) are different in all 3 systems.SO I am looking for a way to align these in a single dimension table.

 

[johnherman]

Might want to consider using replication (or PL/SQL scripts) to create a giant "union" table noting that not all columns would be populated depending onthe source. The union table should have a surrogate key. Whether you want to try to cleanse (unify same vendor, for example) the data in the "union" table is up to you.

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[SarangPatil123]

Well u will have to Build some thing called lookup and pass each record through lookup. Some thing similar to Lookup transformation. Lookup table might look like ( Actial-key TABA-Key TABB-Key TABC-Key ) Look for matching Names rest u will have to clence.

The building of lookup table is reduce future maintaince. If an atribute change lookup table will be able to guide u to correct record in the Dimension table. Use a new feature MERGE in 9i it will make your life little easier.

 

[johnherman]

By the Way, what you are asking for is a customer (in this case, vendor) unification software routine, something that companies like Dunn & Bradstreet and Group One make BIG bucks selling to companies like yours, especially those who have merged several times over the past few years and have never gotten around to scrubbing their customer lists of duplicates.

Some things you may want to consider that the big guys use....first of all, establish a point value for matches and an safety threshhold you can tolerate for mismatches. A vendor who matches on phone number might be an immediate acceptance. Or one whose name matches pretty closely and has the same zip code. Or street address and city match. Or Federal Tax ID number. Anyway, you get the idea.

Those companies I mentioned above have artificial intelligence routines that look for close matches and award points for the degree of closeness. You, as their customer, decide how much risk you are willing to take as far as improperly combining vendors who are actually different. Some companies simply print all the scores for those which match closely and let someone from customer service (or purchasing in your case) make the actual decision.

Best of Luck.

Sometimes the grass is greener on the other side because there is more manure there - original.