Author: Roger Deng

Overview

Recently some of my colleagues asked me about what kind of business functions should data warehouse support, analytical or operational?

Before I answer that question, let’s review some issues business currently is facing regarding to information requirements. Most companies have many distributed information systems and the business data are scattered everywhere. It is very hard for people to see all the transaction data about his clients at one time, one screen. Data warehouse seems is the only place where could have such detailed, cleansed, integrated and completed information. So how to leverage the existing data warehouse investment to help business to make smart, objective decisions and improve operations efficiency from both analytical and operational needs is another challenge to IT people.

Along with the growth in the use of Business Intelligence and Data Warehousing, the BI industry is also going through a period of significant change. BI is no longer used just for doing strategic and tactical reporting and analysis, but also for driving and optimizing daily business processes and workflows. BI is no longer nice to have but essential to business success.

Analytical Space

Traditional data warehouse usually support analytic functions, which answer following business questions: These are the typical analytical business questions usually requested by top management and business analysts. They tend to look at the business trend from high level and then drill down if needed. The BI tool in this space would support those people who manage their business in overall view and help to pinpoint certain areas for improvement. The requirement for data is more aggregated and with several years of history. The physical implementation for this need is more lean to dimensional modeling or star-join modeling.

Operational Space

There is a recent trend to use data warehouse and business intelligence technology to help improve operation efficiency for normal people like salesperson or customer service representative or even customers themselves. The requirement for data is more timely and integrated. Users of this space are looking for the integrated detailed information about certain person like a called customer or a requested client etc. They are trying to answer following type of questions. They are not interested in high level but detail and recent or most recently history transactions. People would like to use the data warehouse, which could provide such complete and integrated information to help them to make some tactic decisions. Customer services could leverage this information and suggest, recommend next steps for clients and customers. These extra services add to the service excellence and improve overall customer satisfaction rate. Compared to the analytic need, the data needed here is detailed, integrated and timely.

Usually data warehouse access design is based on the data usage and targeting to certain group of business people and their business functions. It is very hard to design a single database to accommodate both of these types of users. So is there a better way to accommodate these two different types of users without big change in your data warehouse architecture?

The answer is yes and absolutely.

Architecture Solution

The data to support both analytical and operational needs are all coming from the same source systems. It is critical for them to provide the same truth from both sides across enterprise reports. In order to reach this goal, we need to provide a solution from architecture so that the data flow to all analytic and operation databases are consistent and could match each other. One of growing trend I have seen is to build a centralized enterprise data warehouse and then feed to different data marts for different purpose. All business rules and derivations are done in data warehouse area. The data marts are sole optimized for user access. Figure 1 shows this architecture in high level.

The advantage of this architecture is that the data are extracted once from transaction systems, cleansed once, applied business rules once and integrated in one place. This “done once and used anywhere” strategy helps to remove duplicate efforts and thus save companies time and resources. This also helps to reach the objective of providing a single version of the truth for enterprise use. The data quality could also be controlled and addressed from enterprise level.

Figure 1 – Architecture Solution

Conclusion

Business Intelligence and data warehouse have been evolved and matured enough to support both business analytical and operational needs. How to leverage existing investment is the challenge to any data warehouse architect and IT departments. This paper provides one solution and definitely not the only one. There are many variations of this solution based on each company’s actual data warehouse architecture. An enterprise solution architect should have an open mind and apply the technologies smartly to meet any business challenges.

About the Author

Roger Deng has been working in the data warehouse area since 1994. He is currently working as a Enterprise Data Warehouse Architect for a healthcare company. You can reach him by email at. rogerdeng@yahoo.com