Corporate performance reporting

Leveraging industry data models for financial management excellence

by Tim Duning

Corporations operating in today's regulatory and stakeholder environment need access to their detailed financial data to support decision-making. However, today's standard financial operational systems have little to no flexibility in their architectural structure to respond to ever-changing analytical needs.

For businesses with disparate global operational systems the situation is even worse. They find it difficult to manage multiple sets of data and inconsistent data definitions, a situation that hinders the organization's ability to obtain a clear, over-arching view of the business.

The answer is an integrated enterprise data warehouse—a common, open repository where input from global applications can be analyzed at any time during the accounting period.

From any perspective, particularly that of a finance professional, the task of building an enterprise data warehouse can be daunting. Traditionally, construction of a data warehouse is seen as the full responsibility of the information technology (IT) department. However, executives often criticize IT for building complex systems that do not address the needs of the business.

The finance department plays a critical role in ensuring that the enterprise data warehouse is relevant; however, few finance professionals are willing to invest the time necessary to support and work through the complexity of financial data design. It's a tedious task to figure out what all the questions are from a financial management perspective and how data elements relate to those questions.

These struggles have mired many enterprise data warehouse projects from the very beginning. In a study of data warehouse projects written by Barbara Bashein and Lynne Markus for the Financial Executives Research Foundation, researchers found that:

"People complained that the process cost too much, took too long and produced highly abstract results. Technical specialists had difficulty securing participation of knowledgeable businesspeople; when participation was good, it was often difficult to obtain consensus on the key data elements and their definitions."

Fortunately, an easy-to-follow blueprint for designing an enterprise data warehouse that reflects business priorities exists today in the form of a Teradata Logical Data Model (LDM).

Explaining the LDM
The Teradata LDM specifically defines which individual information elements are required and how they relate to one another to provide a data model for the entire enterprise. Core components within the Teradata LDM include Customer, Internal Organization, Finance, Location, Event and Advertisement. Establishing the relationships between these elements is important because without them, you could not obtain answers to analytical questions and reports asking, for example, what the impact of an Event is on Customer Spending.

Ultimately, the financial segment of the Teradata LDM defines which financial questions can be answered from the enterprise data warehouse, thereby helping to determine the business value of the entire information architecture. Financial management data is the catalyst that adds relevance to all other data sets. Add financial data to customer data, and you get customer profitability and channel profitability. Combine financial and operations data, and you enable product activity cost analysis. As you can see, finance is a common denominator to all industry-specific LDMs (ILDMs).

Looking through an industry spyglass
When taking on a data warehousing project, it's helpful to start with a specific ILDM. The Teradata Retail LDM, for example, is written in the language of retailers. Because it is industry-focused, the LDM can answer questions that are important to retailers. Therefore, a pre-built ILDM provides company executives with an objective template for not only answering those business questions that are important to them, but also those business questions that should be important to them as a member of that particular industry.

ILDMs also identify and extend the data model into subject areas that are unique to a specific industry. For example, within the Teradata Financial LDM, there are relationships designed and information triggers set for events such as customer deposits, customer withdrawals, credit and debit charges, or brokerage buy and sell orders. In contrast, these items would have little or no relevance to manufacturers and therefore are not included in the Teradata Manufacturing LDM.

Although the ILDMs share the common core area of financial management, the relationships established between the core financial management segments and other attributes within the ILDM often differ depending upon the industry. For example, consider the value drivers that determine how select revenue flows into these different types of businesses:

1. In the financial industry, interest income is a key revenue component driving the business. So the Teradata Financial LDM has relationships defined around multiple contractual rules that determine what interest is charged to customers and when the revenue can be recognized.
2. Within the communications industry, network analysis enables the business to collect the revenue it is due. Consequently, the Teradata Communications LDM contains attributes about the network so the company knows when a customer utilizes a particular product or service. It also gathers information about the physical equipment used to provide those services.
3. For the retail industry, market basket analysis is key to understanding revenue potential. Therefore, the Teradata Retail LDM contains relationships designed to understand issues such as customer purchases of complementary goods and how product location within stores influences purchasing.

All three industries share revenue as a common subject area, but the way they analyze revenue is different. That is why every industry requires a separate Teradata ILDM.

Leading the design
To build a solid enterprise data warehouse foundation, the Teradata LDM is laid down before the design of the physical database commences, enabling a company to better understand its own structure within its industry. The Teradata ILDM can be easily customized to incorporate differences that the company brings to the table. This is one way that the physical database design can differ from the standard Teradata Logical Data Model. How the physical database is designed and realized will also differ in a number of ways from the Teradata Logical Data Model.

Given the differences between the logical data model and the physical database design, does it make sense to build a logical data model in the first place? Absolutely! Building a Teradata LDM is a critical first step before businesses invest time in the physical database design.

The success criteria for a physical database design are often driven by a desire to keep hardware, disk space and software costs down. But the technical design may not provide a means to answer critical business questions. Since business experts build the Teradata LDM, the first criterion is to construct a design that meets business needs. The beauty of the Teradata LDM is that it is technologically independent, so that optimization of the physical database design can be implemented upon a foundation that ensures that business questions get answered.

For example, suppose a retail executive needs an answer to the following question: "What stores need to have an additional stock of knit sweaters if I run a one-week special at 50% off for Gold Card customers?" The answer requires that data be related from many data elements and tables:

• Customer table (Who are the Gold Card customers?)
• Product table (knit sweaters)
• Revenue table (What's the historical average price demanded by customers for sweaters sold?)
• Accounting period table (What is the right time of year to sell sweaters?)
• Store location table (The promotion's success might differ in Florida and Idaho.)
• On-hand inventory table (How much inventory do stores currently have?)

The Teradata LDM ensures that data throughout the enterprise gets mapped consistently into the same dimensions and tables. That way, the same stock requirement question can be asked and answered in Florida and in Idaho. The Teradata LDM creates a common business language that is critical to decision-making.

Leveraging the investment
For financial executives who are closely monitoring expenditures, the thought of building a complete enterprise data warehouse to support detailed financial management may seem resource prohibitive. But remember, a data warehouse is rarely built all at once. It is typically built over time, beginning with one related set of business questions. Thus, the company only needs to scope and implement a portion of the logical data model into a physical data model. If the company is new to data warehousing, financial data is an excellent place to start because it constitutes one of the easiest data sets, yet yields some of the most immediate returns. (2)

Alternatively, the company may already have a data warehouse for other information. If the company had the foresight, it would have implemented the first segment of the enterprise data warehouse with reference to a proven ILDM. Having done so, some of the data elements necessary to answer the next set of business questions for the next functional area will already reside within the enterprise data warehouse. The relationships are already mapped out in the ILDM, so answering new questions of a financial nature often is simply a matter of adding a few missing data elements.

The ILDM allows companies to incrementally extend their analytical environment as opposed to building from scratch another data mart when the next urgent business question or issue raises its head. Consequently, the ILDM facilitates rapid implementation and an efficient investment strategy. We can better illustrate this point by taking an example from the communications industry using a hypothetical company called Global Voice. Consider the following matrix of critical data elements (rows) needed to support three key business programs (columns): Customer Loyalty, Network Optimization and Revenue Forecasting:

Customer Loyalty:
Competitive pressures within the telecommunications industry have made retention of existing customers and market share paramount. The VP of Customer Loyalty builds a business case to implement an enterprise data warehouse. Her analysis requires many data elements. Call detail, for example, provides insight into customer behavior, enabling the development of highly targeted retention programs with tailored offers. Combining this with analysis of customer bills, including payments and credits, enables the identification of bundles and packages that would be most relevant to retaining and growing wallet share of the most profitable customers. Network Performance data provides critical insight into a key cause of dissatisfaction and churn that can be used to launch proactive intervention programs.

Network Optimization:
The Director of Network Engineering Operations is chartered with optimizing the existing network infrastructure. Are there network lines with excess capacity? Are additional capital expenditures needed to build capacity for overburdened lines that are providing unreliable service? Companies are looking at the revenue production and profitability of network components in addition to pure capacity, and they are looking to reduce capital outlays by moving capacity from unprofitable to profitable areas rather than blindly investing in additional capacity.

To access the information needed to take action, the Director must ask questions that require call detail records, network inventory, network performance data and service order data. Much, if not all, of this information has already been gathered to support the Customer Loyalty program. Since the data warehouse was implemented with an ILDM, the relationships have already been mapped out so that unprofitable or unproductive network assets can be identified and redeployed to profitable and growing traffic centers, avoiding significant new capital outlay and optimizing network ROI.

Revenue Forecasting:
The Director of Planning is asked to improve the Revenue Forecasting process. Once again, with a data warehouse based upon an ILDM already in place, the task is much less harrowing and costly than what might initially be assumed. The detailed data on call detail records, customer bills and customer history already exists. Only a level of financial history needs to be added to empower a robust revenue forecasting process. Additionally, current period call detail can be used as an early warning indicator of revenue production, enabling immediate response.

The name of the game is leveraging existing resources efficiently. Load once and use the same data many times over.

Summary
In today's environment, decision-making is more critical than ever. Managers need access to data in order to make the right decisions. Technology is available to enable data access and analysis, but the implementation can often go astray. By beginning with a Teradata Logical Data Model and using proven processes and techniques, a business can feel confident that the physical database design will meet their decision-support needs.