Often a transaction system can be confused with data integration and information delivery. This is partially (more…) because operational data can be retrieved from these systems. This video clarifies the differences between a data warehouse and a transaction system by sharing a recent example with salesforce.com
Text from the Video:
Recently I was confronted with a strange situation. We were helping a customer plan out the implementation of their Data Warehouse. And in the midst of this, one of the executives returned from a salesforce.com conference claiming that the Data Warehouse wasn’t going to be needed, because salesforce.com was going to solve their reporting problem. A little perplexed, I realized that the differences between a transaction system and a data warehouse needed further explanation and understanding. And this is where I want to take this video.
The first distinction I want to point out is one of purpose. A transaction system like salesforce.com or Microsoft Dynamics is designed to write data. In other words it provides an effective interface to enable you to create information about a customer or a product. A data warehouse on the other hand, is purpose built for reading data. This means that it is designed to help you see what is going on with your organization by turning your raw data into information. This is where the VP stopped me and said “but Jared at the salesforce.com event they were showing me customer records in their interface.”
And he was absolutely correct, which took us to our second distinction between a transaction system and a data warehouse, which is how the data’s history is stored. If you open up a customer screen in a transaction system like salesforce.com or SAP you will notice that it shows the current state of your customer. But, imagine that the customer you are looking at changed their home address and marriage status only a day ago. To you it will appear that this customer has been married their whole life and that their address has been the same all along. So systems like salesforce.com, SAP, and Oracle Applications do a very good job of storing the transactions, but they don’t keep track of infrequent, but still very important, changes in your data. Let me share with you some common real world examples of where this can cause problems.
Recently we were working with a VP of Sales. They had undergone a big reorganization of their territory sales reps. However this reorganization made it appear as if some reps were much stronger compared to other reps, because they had come from higher producing territories. Hence, there was no snapshot of history to maintain an accurate comparison of what had actually occurred during the year.
In another example, a large provider of lawn care services found that their sales reps were calling customers to sell services which the customers had recently requested be canceled. Since the reps could only see the current services being used, they could not tell there was a change.
When designing a data warehouse, we build in the capacity to capture historical snapshots when data changes. This way, when you are doing analysis and evaluating performance, you maintain the assumption that the world is changing around you. This means, the reports and analytics that come from the Data Warehouse are a far more accurate portrayal of what’s happening in real life.
The third distinction that separates a data warehouse from a transaction system is the scope of information. A system like salesforce.com is designed to capture your relationship with your customer. But, your billing system is a completely different system altogether. So what happens when you need to analyze your sales forecast vs actual billings? Or for that matter, anything that ties the customer relationship with any other event in the organization? A data warehouse is designed to pull data from systems like salesforce.com and others so an integrated view of your business can be realized.
The fourth distinction that differentiates a data warehouse from a transaction system is the structure of the data in the database. This structure in a transaction system is optimized for writing 1 row of data at a time. So when you need to read hundreds of thousands if not millions of rows at a time, it will not only be very slow at pulling that data, but it will also cause the application itself to be very slow for its other users. In a data warehouse the design of the database is optimized for reading millions of rows on demand. Since this database is separate from your transaction system and usually updated nightly, there is virtually no impact on your applications performance.
As you can see in this video a transaction system is not a replacement for a data warehouse or vice versa. Rather they are both critical components to a successful data gathering and information delivery process.
Intricity specializes in designing and developing data integration strategies which can gracefully grow with an organization’s needs. We can help you get started on this path by conducting a short Data Integration Strategy engagement. To discuss the details of this strategy I recommend you reach out to Intricity and talk with one of our specialists. We can outline a road ahead that will engage both your business and technical sponsors and define a best practice path to success.
