System Interconnection

System Interconnection

There are three additional areas where the DW/BI system interconnects with other systems in interesting ways. The first is with what we call downstream systems: other systems, either transactional or analytical, that pull some of their source data from the data warehouse. The second is a source system that manages what we call master data. Master data management is a system designed to be the single source for certain core business objects, like Customer or Product. Its the transaction version of conformed dimensions. The third is BI web services , where the DW/BI system offers some of its unique capabilities to other systems via web services. Lets look at downstream systems first.

Downstream Systems

If the DW/BI team does its job well, people will learn to trust the numbers that come out of the data warehouse and it will gradually become the analytical system of record. As a result, other systems will want to use data from the data warehouse rather than try to recreate it themselves . For example, the sales force automation system might want to pull detailed customer data from the warehouse for distribution to remote salespeople. Another example weve seen is the customer care system querying the warehouse to pull customer order history when a service rep needs to respond to a question about an item the customer ordered two years ago. The DW/BI team also needs to make sure it can supply any value-added elements that get created in the warehouse to any other systems that need them. If you calculate a customer lifetime value score, you might want to make sure the customer service people see this on every screen when they answer a call.

Potential interested parties extend beyond the boundaries of the organization. You may need to pull shipment data from your suppliers or provide order history to your customers. You also may need to share data with industry organizations or government agencies. In some companies, an independent subsidiary may need to supply data back up to the parent company to feed their data warehouse or other reporting systems.

The point is, you need to provide tools designed to support system access just like you provide query tools to support business analyst access. These tools could take the form of bulk extracts executed as part of the ETL system, direct queries against the warehouse (either single event or bulk), or even a full-blown metadata-driven data distribution system. Make sure you look around for other systems that could take advantage of the data in the warehouse. And make sure you also recognize the impact this may have on your service level agreements.

Master Data

We realize this may come as a shock , but there are some organizations out there with systems that issue multiple identifiers for the same customer. This usually comes about as a combination of business urgency and lack of communication. One information services provider we worked with just had to get its business on the Internet right away; it built all new customer order systems rather than trying to integrate with its existing systems. While it achieved the goal of getting the company online quickly, it created a nightmare when management tried to understand how many of their existing customers were using the Internet. It was hard to say because there was no way to join the customer data from the two systems together. It also created additional work for the customers because they basically had to re-register as customers if they wanted Internet access. Most often, we see these problems as a result of incomplete and ineffective system integration efforts resulting from acquisitions. One Kimball Group client had 23 customer- facing systems that allowed for 7 or 8 different customer numbers for the same customer as a result of acquisition activities.

Ideally, the transaction systems should all be able to call on a shared source for shared data like customer information, rather than have each system collect and maintain its own version. The goal is to keep the data in one place so when it gets updated, all systems have access to the most current version. We call this shared data master data (like the customer master, or the product master). As companies move toward adopting large-scale ERP systems and Customer Relationship Management (CRM) systems, the possibility of true master data is coming closer to reality. Its interesting that many companies still have several major transaction systems rather than having everything in a single ERP system. The big split is between the ERP and CRM vendors, with the biggest battle taking place over who owns the customer master. Vendors on both sides have built APIs and web services to allow for the easy exchange and updating of information based in their systems. The message is, We have the customer master, but you are welcome to use it. Third-party companies have also sprung up to help solve this problem.

The creation of master data is often at least a five-year process. Typically it begins with the DW/BI team creating a complex ETL process to integrate similar data being created by multiple source systems independently. The DW/BI team often has to do it because the data warehouse needs a conformed customer dimension or product dimension or person dimension, and there is no other way to get it.

At one university we worked with, the DW/BI team had to create a person master table because there were at least five source systems that were collecting person name and address information completely independently of one another. A student would enroll and get an ID number and an entry in the student system. The same student might take a position on a grant project, receiving another ID and another name and address entry in the grants and contracts system. This integration work had to be revisited every day as new people showed up in the source systems, or people changed their addresses in one place, but not another, and so on.

Building a conformed dimension that integrates multiple transaction systems is a big task, and technically not one the data warehouse would be doing if the transaction systems had done the job right in the first place. If you take on this task, you need to aggressively educate the business folks and the transaction systems folks about the cost of fixing a problem that should be handled in the source systems. Do the work you need to in order to meet the business requirements, but continually push to migrate the responsibility for this data integration task back to transaction systems. Ideally, the best way to handle customer attributes is through a centralized master data facility. All changes/updates are handled centrally , real-time as part of the transaction system. Then, all we need to do is extract from the master data.

BI Web Services

Recently weve been seeing DW/BI teams going into the data services business. One BI system director we spoke with had to build a massive customer matching system to integrate and de-duplicate customer data from several source systems. His team took their name and address standardization routines and turned them into a web service, which the transaction system team then decided to use to do in-line lookups in the data capture modules. In this case, the DW/BI team ended up providing the source system developers with the tools they needed to fix the data capture problem.

Customer scoring and data mining present similar opportunities for offering web services developed from the BI system. In fact, many of the queries that come to the data warehouse in support of downstream systems described earlier in this section could easily be supported by web services. Again, like supporting downstream system queries, you have to be careful about the performance and service level implications of offering DW/BI system based web services.