SQL/Master Data: Information Integration at Attachmate

Back in the earliest days of Attachmate's history there were three employees and the one manufacturing/sales/software development/support department. As the company began to grow new departments were formed to handle specialized tasks such as Sales, Marketing, Shipping, Software Development, Testing, and Customer Support. As each department grew they separately designed a uniquely structured Advanced Revelations (AREV) relational database to handle information relevant to their specific mission in the company. For example, Sales kept data about transactions and the customer systems involved. At the same time, but on a different database, Customer Support logged data about customer systems, problems, and missing features that, if implemented into Attachmate's product would provide for compelling sales opportunities.

And as this growth was occurring Marketing was sending advertising mailers to customers for products Sales had recently sold to them and customers were frequently asked the same questions each time they communicated with a different department.  That is, information in every department's database was valuable for numerous reasons if it could be made available to the other divisions. I was given the opportunity to devise a way to organize and manage the different departments' databases so accurate, consistent, and up-to-date information would be available to everyone.

Going in, I expected that my goal was to decide the best technical design for an end-product data warehouse. However, it turns out that establishing nth level normalization is not the biggest challenge in this type of an endeavor. I realized early on that I was put in charge of this project because several months of weekly meetings attended by the nine department heads involved had failed to pound out a well-normalized corporate database. Each of the nine departments with an AREV database had a lot of stored data in their database, but each was constructed in a dialect that reflected the needs and understanding of the types of people who worked in the department. That is, while much of the data was exactly the same from one system to the next, there was a lack of what are known as "unique identifiers" shared by all of the databases to identify a field type. For example, a field in one department's database might cover multiple fields in another department's. Another common problem was where a field in one department's database might concern the same data, but be named differently, and some departments had developed a serious religion about their choice of field names.

The steps I chose to reach an agreement involved producing a candidate list of the fields that emerged from running historical queries against all of the databases using the each department's terminology. To avoid the head butting that had occurred before my entry to the project I submitted in series -- specifically not in parallel -- the candidate table and field list to each of the department managers involved. (If any of you managers involved are reading this, my strategy was to gain managers' acceptance in order from the least agreeable of you to the most. ;-) ). Each time I ran into need for a change when discussing the list with one manager I would polish the changes in and start over at the first manager, successively gaining approval until the job was done. Yes, at the start it looked like an O(n^2) endeavor, but happily the managers turned out to be agreeable and it was finished in O(n) time.

From that effort, together with the purchase of a newer web-based database engine, the goal to create one master corporate database was completed. It provided a lot of strategic information, made clear what tactics worked well from one project or product lifetime to the next, and allowed us to optimally manage our efforts in ways that certainly played a role in Attachmate's later success.