Manage revenue with Impact

Amdahl adopts a killer data mart application for high tech companies … without learning a star from a snowflake
by Kevin Strehlo

Wigh tech companies don’t have it easy. Stock price pressures force managers to hit aggressive goals. Do they need to favor margins or growth rate, and what indicators will tell them how to maximize either or trade off between the two?

You wouldn’t expect that a company like Amdahl would even think about improving its analysis of such issues in the midst of solving its Y2K problem. But there Amdahl was, waist-deep in Y2K and trying simultaneously to maximize revenues. In some sense, that course was set when Amdahl decided to handle the millenium problem by replacement rather than remediation. Bringing up Oracle Financials and Clarify packages had the IS group’s hands full, making resources scarce for a second major project precipitated by the first: who was going to replace all the “reporting” tools built around the legacy apps? And without them, how would Amdahl keep its financial house in order?

Mukta Bahl, who drove the financial analysis project for Amdahl, wanted a better foundation for revenue management. The move to new operational systems created both an opportunity and an imperative to get the job done. Amdahl had to redo its homegrown financial analysis applications anyway … and in short order, since they were tied closely to the legacy systems that were soon to shut down.

“I call it ‘reporting’ because I don’t like to talk about data marts with my business people,” she says. Calling it “reporting” kept the eyes of her financial troops from crossing the way they did when the subject was data marts. Moreover, her word choice stressed attaining real business insights rather than focusing, as too many data warehousing projects do, on building an infrastructure to gather and rationalize information with the presumption that good results will follow automatically.

Mukta was thus primed for the problem-solving orientation of the pitch from former coworker and Brio cofounder Arun Shah about Brio’s forthcoming Impact application for revenue management.

“Most tools are oriented at the source of the data, rather than focused on the consumption,” Mukta observes. “The Brio application’s high tech business perspective is pretty unique in the data warehousing market.”

Much as airlines have long since codified applications that maximize yield when booking flights, Impact helps high tech manufacturers who want to aggressively manage their margins and market share. That sounded good to Mukta, as did Brio’s promise to give Amdahl what it needed within a demanding timeline.

Her initial goals were to get clearer financial and operational views of the Amdahl sales lifecycle. On the operational side, she was concerned with tracking the length of the sales cycle, how many configuration changes they were making per order, sales volumes, and so on. On the financial side, she wanted a better handle on the cost, discounting, and margins for every sales opportunity, the ones lost as well as won. She was unconcerned with the architecture, except as it affected how hard it would be to implement Amdahl’s sales lifecycle application.

“It’s not that we didn’t discuss architecture,” says Mukta. “One of the first points was whether Impact’s default data model would fit our business model.” For example, she wanted to know how much customization would be needed to recognize revenue in Amdahl’s conservative manner, which isn’t to recognize on shipment but considerably later: upon final acceptance at the end of a money-back trial period.

	Figure 1. Star and snowflake schemas. Click here.

She was impressed that Brio’s consulting team didn’t try to hide how technically complex it was going to be to align data coming from so many sources, yet they discussed it clearly from the businessperson’s point of view. Mukta was content to leave the development of appropriate star and snowflake schemas to them. (See Figure 1 to understand more about how the natural dimensions of a business—products, sales organization, time—and the facts about them are modeled in many data warehouses and data marts.)

She was more interested in Brio’s depiction of Amdahl’s current system as an “information black market.” As with most organizations, Amdahl’s financial analysis depended on an informal network of information exchange and analytical rules and practices, applied most effectively by the company’s top performers. Mukta wanted to spread the wealth, and Brio offered a blueprint to do it with Impact.

Figure 2. Impact architecture: Best practices, institutionalized. Click here.

Architecting a better black market
Figure 2 shows Impact’s architecture. Most of this project’s challenges rested in the particulars of supplying the Amdahl data mart in the back-end data tier.

As usual, the main application logic runs in the middle tier on a Java application server. In most organizations, this logic is scattered throughout in what Brio describes as an “information black market.” By “black market,” Brio means the informal processes and relationships that exist among business analysts who know where to get information and the managers who know what to do with it.

For example, one of the star performers in an organization might be a product group manager who pays attention to which products are selling in which market segments. The manager knows to ask a particular analyst to segment the market according to certain rules captured in a spreadsheet (say, large, medium and small customers). Another manager might work with an analyst to collect information on margin by product, using it to spot cannibalization of a high-margin product by a low-margin product and institute a marketing program to prevent it.

These kinds of rules and processes, not supported in an ordinary data warehouse or data mart, are embedded in a structured way in the middle tier of Brio’s Impact to guide high tech companies in the best practices of managing revenue (see Figure 3).

Figure 3. Take revenue management off the black market. Click here.

In Amdahl’s case, Mukta was determined to improve upon the company’s existing black market. “There were key information providers giving key performers direction in terms of what to do,” she says. But there were also known problems, such as a revenue reporting system that was a collection of Excel spreadsheets floating haphazardly around the organization. Something had to change.

In the initial application, Mukta directed Brio to focus on the sales cycle. Were managers getting gross margin, and if not, why not? Why was Amdahl losing those deals they lost? “We didn’t go into cost of goods, inventory management, or that kind of thing,” says Brio project lead Robert Smith. “The dimensions are there, but before you begin to use Impact to analyze them they’ll have to go back and populate the facts.” The strategy was to focus on revenue optimization.

Amdahl users working with the Brio implementation team to map their data sources into the Impact data and metrics model (see “Revenue management metrics“) quickly discovered an important gap in their analytical capabilities. While they were tracking margin on a contract level, they couldn’t break margin down by individual products, making it impossible to spot patterns of excessive discounting or products that weren’t contributing sufficiently to the bottom line. The thorough, comprehensive nature of Impact was already paying dividends.

Given the strengths of the application package, it might seem the Brio team would have an easy time getting Mukta’s initial application running. Not so: gathering and aligning the scattered data sources from an organization’s black market is always a challenge.

Users help solve data issues
The biggest single activity on the Gantt chart of the project was data analysis. “It was tricky to map the facts and make them conform,” says Brio’s Smith. “And the model in some cases presented the illusion that facts conformed, when the model actually had multiple instances of a particular fact table, presenting the appropriate table depending on the user’s information needs. But that complexity was hidden.”

The Brio team used Amdahl’s corporate data modeling tool, Oracle’s Designer/2000, but did much of the initial analysis on white boards and sticky notes with colored pens. “We would attack one source system at a time,” Smith recalls. “We knew ultimately what the requirements were: a customer, product, and sales region dimension, and we defined facts second. We then took stabs at what the hierarchy of those facts should be. Finally, we tried to access the data from the source systems. The less-than-ideal results sent us back to modify our initial assumptions.”

Looking back, Smith wishes they had kept end users involved through these iterations. Discrepancies between what they thought they had found in the data sources and what users actually stored there demanded further reworking of the model when users were brought in to validate what was supposed to be the final data model.

“We learned it wasn’t enough to track sales forecasts by returning just the latest one,” says Smith. It turned out that regional sales managers do multiple forecasts for the same month, and they need to see the whole series as well as the underlying assumptions. Such fixes, while not impossible at that late date, would have been easier if the problem were understood earlier.

Some of the challenges Brio faced were typical of data warehousing. For example, the problem of aligning the various flavors of time found in source systems when they were combined in the data mart. Some numbers were fiscal, others were calendar; orders were sometimes at the day level and sometimes at the month; and forecasts were at the quarter. “It’s pretty standard stuff,” says Brio’s Dave Babicz, who took on the physical data model as well as the extraction, transformation and loading (ETL) of the data into the system. “We had to roll up the lower level numbers to be able compare them at the same time granularity, that kind of thing.”

It would have been easier for Babicz if he could have used an ETL tool to do this kind of work, but Brio went along with Amdahl’s request to stick with Oracle’s PL/SQL. While it was easy enough for Babicz to cope with a procedural, code-intensive approach, he worries that Amdahl will be forced to apply scarce technical resources to future maintenance, when a high-level ETL tool would have allowed a business analyst to handle the chore.

Figure 4. Conforming dimensions. Click here.

The Sales organization dimension presented another problem that was tough no matter what tool Babicz used. Two hierarchies were in use, one geographic and the other organized by management structure (account executive, regional director, area general manager, and then VP). A surprising number of sources had a value in one hierarchy but not the other. Bridging that gap wasn’t always difficult. For example, the opportunities tracking data source included account executive and region, and region was the business key in the organization table, which provided a direct match (see Figure 4). In other cases, a handcrafted mapping table allowed Babicz to derive the organizational values by looking up account executive and regional director. While such a mapping works, it presents maintenance issues in terms of updating the mapping table each time an employee is hired, gets promoted, leaves the organization, or moves to a new region.

There were also the usual cleansing issues: consistency of names, for example. Is “Don” the same as “Donald”? And don’t forget to delete the middle initial from the one data source that includes them or nothing will match.

A particularly tricky problem came in dealing with the product dimension. “We could not conform products,” says Babicz. “We ended up using two product dimensions, one for opportunities and one for all else. The dimensions are consistent, but not down to the lowest part level.” The opportunity data source was limited to category and group designation only, while the Clarify system used for customer relationship management went down all the way to model, line, and parts.

Missing values are a problem without a perfect solution. “You get the choice of putting out an exception and dropping it,” says Babicz, “or you can manufacture an unknown dimension entry.” The advantage of using unknowns, or course, is that reports and rollups give the right totals that way. The problem, in turn, is that when you drill down at some level of detail you’re going to come to a “value unknown” place holder just when you need a number.

Remaining challenges
While Amdahl is pleased with what Brio has helped them achieve thus far with Impact, their plate remains full for the foreseeable future.

For example, one of Amdahl’s ambitions is to eliminate the need for extracts and instead populate Oracle, Clarify and the data mart as transactions occur, eliminating overhead and making information timelier. They’re currently working on the necessary architecture using Software Technology Corp.’s (STC) application integration solution. STC provides middleware and consulting to help integrate supply chain, sales force automation, and customer relationship management applications into enterprise resource planning systems.

In addition, over time Amdahl will start to implement more of Impact’s modules and get into some of the more sophisticated revenue management that helped Cisco, another pre-release customer for Impact, achieve payback on a seven-figure investment within three months. For example: by looking at configurations sold in a European country, a Cisco product manager identified similar sales teams in the United States that could be selling higher margin applications. After he created specific programs to change the sales mix, Impact allowed him to monitor the results.

By trending the number of customers buying a new product, and by monitoring the units sold to each customer, another Cisco product manager was able to determine the penetration rate, identify pilot sales vs. deployments, and monitor the success rate and evaluation time for the pilots.

In yet another early use, Impact’s configuration analysis module helped disprove an incorrect hypothesis that a particular board was driving the spectacular ramp of a new Cisco product. Just goes to show what you can achieve when an application takes an informal, unfocused set of practices, sharpens them, and fleshes them out to a full set of best practices presented in a fashion that fits with users’ natural understanding of the business.

You’ll have a real Impact, or so Brio hopes.

Kevin Strehlo can be reached at kevinstrehlo@ibm.net.

© 1999 FAWCETTE TECHNICAL PUBLICATIONS, all rights reserved.

Brio’s metrics library helps users analyze complex business issues such as margins and market share. A sample of the subjects a company can choose from in an application installation is:

The key to Impact’s flexibility is that its design simplifies some common issues in data warehousing scenarios, including:

• Separating the physical elements (e.g. awareness of what numbers are aggregations of lower levels of detail, star navigation, etc.) from the logical elements (such as formula and time period), which allows someone in IT to manage the physical elements and the business administrator to manage the logical.
• Stitching together multiple fact tables using conforming dimensions to create complex metrics such as Book/Bill.
• Handling different levels of detail and different related dimensions to readily calculate metrics like percent of Forecast or Actual vs. Forecast comparisons.
• Automatic compensation for business days, holidays, and 4/4/5 or 5/4/4 quarters to simplify such metrics as daily average bookings and quarter-over-quarter performance.
• Flexibility for non-standard roll-ups, for example, allowing fudge factors at each management level of the sales forecast.
• Wizards allow users to modify, clone, and evolve metrics often without programmer involvement.

In Brief

The Job: Implement a financial lifecycle application in order to maximize revenue and replace Amdahl’s non-Y2K-compliant legacy reporting systems. The potential payback from improving revenue yield was so compelling that the project plan called for rolling out the new analysis application against the legacy systems first.

The goals
Bring up sales lifecycle analysis system ASAP.

Achieve clear real-time financial and operational views of the sales lifecycle. Result: a better handle on the sales process and an optimized relationship between cost, discounting, and margins.

Implement application on a data mart architecture that would allow new systems to come online and replace the legacy system without interrupting financial analysis.

Provision of enough scope and flexibility in the data model to support future applications useful to a variety of work groups.

Provide a unified view of data but with custom metrics allowing each business manager to see data from a unique perspective.

The legacy system
Data for the analysis was drawn initially from analysts’ Excel spreadsheets, a homegrown CICS Cobol financial system, and a Lotus Notes sales system, among others. These homegrown systems, which needed to be remediated for Y2K or replaced, were to be replaced during the course of the project by Oracle Financials, Clarify, and a refined Excel cost model.

The solution
Amdahl chose to implement Brio’s Impact revenue management software before its general release. Rather than take on the tricky data modeling and data extraction and cleansing required while simultaneously bringing up new operational systems, Amdahl hired Brio to implement the data mart infrastructure and configure Impact to take advantage of its built-in best practices for determining how to maximize revenue in high-tech firms.

The players
Brio Technology
Janet Dang, Vice President, Applications Marketing
David Babicz, data modeler
Arun Shah, Brio cofounder
Robert Smith, project lead for the Brio team

Amdahl Corporation
Mukta Bahl, project owner
Renu Virdi, project manager
Scott Peterson, application publisher
Holly Day, point person for user community

The tools
Hardware
A wide range of Amdahl servers

Software
Brio Impact
Oracle Financials
Oracle PL/SQL
Microsoft Excel
Clarify eFront Office
Oracle Designer/2000

Databases
Oracle8