| September 1999
Manage revenue with Impact
Amdahl adopts a killer data mart application for high tech companies … without learning a star from a snowflake
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.
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.
Architecting a better black market
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).
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 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.
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.
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 [email protected].
|Conforming dimensions. Brio had to source the product table from two different product tables. One table supplied three of the five product hierarchy levels, another the other two levels. A manually created mapping table then related the two.
For Organization, the dimension table was sourced from a single source system, but the fact tables did not always contain the business key values (AE Name and RD Name) required to find the matching organization record. By using a site_id lookup into a staging table that mapped site_id to a close proximity of the business key, most instances resolved to a single organization record. In some cases, multiple Str_org rows matched these two values and special rules had to be developed to determine the appropriate one to use.
|Impact architecture: Best practices, institutionalized. Brio Impact can be viewed as an analytical server that acts as a framework for institutionalizing an organization’s best practices and unique metrics for managing the sales lifecycle. In a multi-tier architecture, it offers metrics libraries and a complex and customizable data and business model to let users analyze everything from summary indicators to details: orders, configurations, average sales prices, parts, and even quality.|
|Take revenue management off the black market. Amdahl improved on its informal “information black market” approach to financial analysis by applying a customized version of the process, practices and metrics embodied in Brio’s Impact solution.|
|Star and snowflake schemas. A star schema features a single fact table, with detail and summary data. The primary key has only one key column per dimension. Each key is generated, and each dimension is a single table.
The benefit of such a schema is that it offers hierarchies that are easy to understand and easy to define. As a result, you find a reduced number of physical joins, simple metadata, and a low-maintenance data mart. On the other hand, the summary data in the fact table yields poorer performance for summary levels. And huge dimension tables can present a problem.
The snowflake schema offers no level in dimension tables. The tables are normalized by decomposing at the attribute level. Each dimension table has one key for each level of the dimension’s hierarchy. The lowest level key joins the dimension table to both the fact table and the lower level attribute table.
So how does it work? The best way is for the query to be built by understanding which summary levels exist, finding the proper snowflaked attribute tables, constraining keys, then selecting from the fact table.
|Revenue management metrics|
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:
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.
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