Data Warehouse Architectures for Multinational Organizations: Part 1

Consolidating data across regions or international borders requires careful assessment of data warehouse models

Big Data Industry Architect, IBM

This three-part series for the Big Data Architects column was written by Tommy Eunice, software architect on the IBM Big Data Industry team.

Multinational organizations have faced the problem of reporting and analysis across regions and countries for decades. Combining data from different cultures, jurisdictions, time zones, alphabets, and platforms is always highly challenging. Advanced technologies are helping to make these tasks easier than ever before, but even today’s architects often must respond to pressures from the line of business to allow reporting that spans multiple countries. However, there are ways to address these challenges. Many architects facing these problems often ask the following questions:

  • What are the best practices for building data warehouses for multinational organizations?
  • Can we act as one company with a very diverse customer base?
  • How can we create regional hubs with common development and support?
  • Will multinational data warehouses achieve significant improvements in information governance and data quality?
  • How can a single best practice solution be enforced across regional hubs?
  • Is building a single data model and using it in plug-and-play fashion possible?

Before designing a data warehouse for any multinational organization, architects need to ensure there is real business value for creating one in the first place. Reporting across all regions may seem like a good idea, but in reality many decisions are made locally, except for decisions that impact major product development and strategy.

Advantages of analysis shared across regions

For example, one organization has significant operations in 10 countries and has strict, centralized control over billing, marketing, and branding. This company wants to measure the effectiveness of marketing campaigns and product launches across resellers and countries. Its goal is to spot the most successful campaigns and share them across other regions. While this business goal is valid, is the value enough to justify developing a multinational data warehouse? This organization thinks so.

A multinational retailer expanding into new countries, in another example, has a hands-off strategy for each regional business. However, this multinational organization is being affected by disjointed business practices that make comparing regions difficult. The company is also experiencing shrinkage disproportionately in certain regions. It views a detailed, multinational data warehouse as a method to enable tight controls on its regional management.

There is always value in high-level summary analysis shared across regions. This kind of analysis allows management to determine the overall health of the business and make strategic decisions about its growth and investment. Financial regulators require some form of consolidation of data, so consolidation is always necessary. The question is whether detailed analytics across countries is valuable.

Expanded detailed analysis from a highly sophisticated consolidation of accounts and transactions can enhance the operations of each regional business unit. Best practices can be discovered and propagated across units. A product can be hot in one locale and cold in another simply because of clever marketing campaigns or beneficial commission structures. Without detailed analytics that span multiple regions, these potentially profitable situations may not be detected and exploited across those regions. Accurately measuring the value prior to the data warehouse project can be very challenging.

Implications of expansion across country borders

Many multinational organizations grow through acquisition. This kind of growth creates a scenario in which each region can have different operational and analytical systems. The analytical systems in each region tend to have different data models that make consolidation a challenge. For example, revenue per customer might be calculated differently in each region because of different accounting rules among countries or simply because of different preferences by the local management. These complexities are in addition to the obvious problems of merging data from different languages, number and date formats, currencies, time zones, character sets, collating sequences, and so on.

While a consolidated data warehouse across regions has some advantages, a fundamental question should be asked: What value is there in comparing detail data from one region to another? Each region may have different rate plans, product mixes, legal requirements, cultural and regional preferences, and languages. So comparing regions might not have as much business value as it appears on the surface. For example, in the telecommunications industry, some regions may prefer prepay phones while other regions may prefer long-term contracts for smartphones. The ratio can be as much as five prepay phones to one contract phone in one country, and the reverse in other countries, making country-to-country comparisons less valuable.

For example, a major producer of beverages that is interested in a worldwide data warehouse for tracking sales of its hundreds of brands has major operations in Asia, Europe, and the US. In some Asian countries the beer market is completely dominated by two or three brands, while in other markets, such as certain European countries, there are hundreds of local brands in the portfolio. This diversity makes brand comparisons by profitability and market share across countries have less value.

Centralizing and consolidating analytics can be expensive. The cost of centralization may outweigh the benefits. Before beginning expensive, multinational-scale projects, a careful review of the business case is advisable. Training regional managers and investing in enhanced analytics may be more efficient at the local level than at the multinational level.

Part 2 of this article discusses three approaches organizations can take to consolidate data across multiple regions. In the meantime, please share any thoughts or questions in the comments.