In-Depth

Dashboards come into view

• By Peter Bochner
• May 1, 2003

Forward-thinking IT development managers have worked for years to fashion business intelligence (BI) apps that can contribute to corporate profitability. Oftentimes, in past years, apps appeared destined to be shelfware, as the job of cleaning, transforming and staging data consumed the attention of IT development teams. The long-sought promise of the executive dashboard seemed unfulfilled. But that is changing, according to development managers in the middle of the fray.

For example, when it comes to ease of use, the latest BI tools are a different story. They offer intuitive GUIs that can be dashboard-driven. Their ad hoc query and analysis capabilities are Web-based, so users can often get fast turnarounds on reports. They include SDKs that allow users to customize their analytic solutions and extend them via Web services, or XML or Java APIs. Some tools are so easy to use that even executives with limited data-analysis skills can create the majority of the reports themselves.

Today, the benefits derived from BI systems seem limited only by the creativity of a company’s IT staff. This article discusses the benefits five companies received from their BI systems and how they represent major advances over what used to be in place.

Todd Wilkes, director of application development at Premier Inc., said his firm "needed the ability to update [data] from a central location."

In the medical community, management engineers first tried to realize the benefits of BI through creative ways of sharing data. In the late 1980s, they created Lotus 1-2-3 spreadsheets and mailed them to each other. As the amount of information grew too large to put in cubes, patient data was put on CDs and sent to hospitals. But even that solution was soon inadequate.

“Shipping the data was no longer feasible,” said Todd Wilkes, director of application development at Premier Inc. “We needed to have the ability to update it from a central location.”

Headquartered in San Diego, Premier represents more than 1,500 not-for-profit health-care organizations, including multi-hospital systems, academic medical centers, urban acute-care facilities and rural community hospitals. More than 600 of its clients use the MicroStrategy Web component of the MicroStrategy 7i BI software to make decisions on issues of clinical care process, financial and market strategy, resource utilization, patient safety and staffing.

Premier Inc. first turned to MicroStrategy in 1996 for Version 3.0 of its BI software and is now using Version 7.2. Overseeing the sharing of data is Wilkes, who works in Premier’s Charlotte, N.C., office. There, Premier has six Compaq Web servers running Windows 2000 and six application servers broken across two clusters. These application servers run Microsoft SQL Server 2000, which contains the database for MicroStrategy meta data. The data mart server is a 68000-based Sun server that runs Red Brick under Solaris.

Premier’s business intelligence apps consist of two data warehouses. The Perspective warehouse contains clinical and financial data, while the Operations Outlook warehouse contains operational data. Perspective allows users to look at the clinical outcomes for, say, heart failure patients, so they can improve their clinical care processes by benchmarking themselves against national norms or within their own hospital. Digging into financial data helps executives to compare what procedures cost vs. what the hospital charges, or to gauge mortality rates by cost, length of stay or physician.

As MicroStrategy’s BI software evolves, Premier is able to offer its clients richer insight into their health-care practices. For instance, Version 7.1 expanded Premier’s ability to perform Web-based health-care analyses. Because of the Web subscription capability in that version, users can receive personalized reports on a scheduled basis, rather than perform a particular analysis every day. This feature, notes Wilkes, substantially reduces the time it takes Premier’s users to run reports, particularly time-sensitive analyses. Lastly, the package’s improved prompting enables users to build on-the-fly reports more easily than ever.

The 4,365 people who currently use Premier’s BI system include department managers within hospitals, CEOs, CMOs, COOs and management engineers. They all use the same interface, but the seven BI developers whom Wilkes oversees now use MicroStrategy tools to customize the user interface. According to Wilkes, there will be three versions of the user interface: one for the data analyst power user, a point-and-click version for CEOs, and a third version for those users who want the best of both worlds. Wilkes is also giving some thought to changing the style of the user interface from its current Windows Explorer folder look to an Amazon.com-type user interface with tabs across the screen.

“The big thing is to make sure our user interface is still meeting the needs of our user community,” said Wilkes. “We need to show ROI back to the hospital. If the CEO and CMO are daily users and can’t get their job done without your data, that makes showing your value proposition to the hospital a lot easier.”

Unlocking the mysteries of brand loyalty

DDB, the largest ad agency in the U.S., uses business intelligence to help its researchers unlock the mysteries of brand loyalty. SAS software allows the agency to store and analyze data from its Brand Capital and Lifestyle consumer research projects, giving its account executives insight into customer connections to brands and allowing agency staff to better advise clients on their advertising strategies. DDB began conducting its Lifestyles survey of adults in 1974 to try to determine why consumers choose one product over another. In 1999, it introduced the Lifestyle Web site, and the Brand Capital site went live in March 2000. DDB polls thousands of users about brand information, attitudes, interests and hobbies, then collects the data and organizes it. By 2002, research was conducted in 23 countries.

DDB first deployed SAS software in an OpenVMS environment in 1996. When it moved to Windows NT 2000 in 1999, it deployed SAS/STAT Version 7 for Windows to perform statistical analysis on its data. But as the type of data used for BI became predominantly profiles based on lifestyle information, the agency moved to the SAS/IntrNet collection of tools for data analysis and decision analysis.

Today, several hundred agency employees, ranging from strategic planners to account executives, can access the SAS/IntrNet BI applications, according to Janice Riggs, Brand Capital projects director. With the data Web-enabled by SAS/IntrNet, users simply go to the agency’s intranet and order the reports they want through pull-down menus.

“We’ve taken something very difficult to request and made it very straightforward,” said Riggs. “We’ve taken the fear out of numbers for our users,” who, she noted, “are not very quantitatively proficient.” All a user needs to do is type in their e-mail address and click on a menu -- all the behind-the-screen commands are taken care of by Java code. The list of valid e-mail addresses is controlled by DDB for an added level of security.

Forty-four DDB offices worldwide have access to the Brand Capital Web site. Although the server for the Brand Capital and Lifestyle Web sites -- a Dell dual-processor server with 4Gb of RAM and 0.5 TB of disk space -- is in the Chicago office, if someone in DDB’s office in Greece wanted information, they would not have to go through the Chicago office because all of the firm’s business intelligence is distributed across the Web.

As a result, “we can run requests 24 hours a day, and they get the information back in a few hours,” said Denny Merritt, DDB’s Brand Capital system developer.

Added Riggs, “Half of our users do not speak English, but they still find the Web site navigable.”

Times have changed, noted Ned Anschuetz, group strategic planning director at DDB. In the past, when an ad agency client requested market data, it would take a week or two for the agency to be sent a tab book of market information that it would go through by hand to create a report for the client.

“It took forever and was totally archaic,” said Anschuetz. “If all our offices had to request tab books and get information, each of those requests would cost $1,000. Last year we had nearly 6,000 requests for runs. Multiply that by $1,000, and that’s nearly $6 million saved by getting the information accessible through SAS.”

An example of the type of intelligence gleaned from DDB’s consumer research reports might be how many people in Greece are familiar with the Toyota brand and how their familiarity with the brand compares with that of consumers in Poland. For DDB’s executives, having this business intelligence available helps them to better advise their clients on advertising strategies. “Our people are getting accustomed to having this convenience on their desktops,” said Anschuetz.

Sharing the gold

TruServ, headquartered in Chicago, is a member-owned retail cooperative and the parent company of retailers such as True Value Hardware, Grand Rental Station and Taylor Rental. The Fortune 1,000 company has thousands of customers, products and employees.

Just two years ago, to access sales data, TruServ managers had to first choose from a multitude of databases, download the data, then go into a spreadsheet and do the metrics for their division. But the problem was that because of the profusion of databases, department A’s daily sales numbers did not always match those of department B.

That year, as part of a data warehouse initiative, TruServ deployed Business Objects’ Application Foundation, an open framework for building and deploying dashboards and custom analytic applications. The first thing TruServ did with the package was to use it to consolidate all of its databases into one common database, creating a data warehouse.

“Now we scrub all the data before it gets to [the] warehouse so everyone is looking at the same thing,” said Neil Hastie, TruServ CIO.

With data integrity no longer an issue, TruServ is able to balance its daily sales in the data warehouse. So accurate is the information in the warehouse, said Hastie, that “we use our data warehouse to feed our general ledger. Our sales journal entry comes directly from the warehouse.”

TruServ initially used Application Foundation to track daily sales, or, as Hastie calls it, “your ho-hum, down-and-dirty BI.” But, he said, “the more we played with the software, [the more] we realized we had a goldmine; so we decided to share the gold with our vendors and member retailers.”

Today, TruServ’s key vendors, manufacturers’ reps and hundreds of Tru-Serv employees use the BI software to share the latest business data on sales, marketing, logistics and inventory. “Every step we touch in the supply chain uses it,” Hastie noted.

Perhaps TruServ’s most important use of Application Foundation was to build an executive dashboard for its top managers, who use the digital cockpit to track and analyze business metrics. The dashboards show time-series analysis, enabling managers to pinpoint trends and changes over time, and obtain alerts to help monitor, interpret and act on changing business data.

The executive dashboard gives managers valuable data on operations, which helps them to manage inventory. TruServ defines red-zone inventory as inventory sitting on a shelf for 180 days or more. If a manager discovers that a particular SKU is in a warehouse in Manchester, N.H., and it is not moving, TruServ has to liquidate that inventory or move it to another warehouse.

“If our BI software can show us that the velocity for that SKU in Atlanta is x units per day, we’d want to make an automatic transfer,” said Hastie. “So the next deadhead [return trip without a load] from Manchester will take that SKU to Atlanta.”

By rebalancing its network of warehouses every week, TruServ pares down its inventory-carrying costs. Hastie estimates that since deploying Business Objects’ Application Foundation, TruServ has reduced its inventory by $50 million.

“It’s not worth our time to calculate an actual ROI because we know we’ve gotten such value from the system. There’s no reason to figure out the ROI except to make yourself feel good. We don’t have time to do it,” he said.

TruServ also uses its BI system as a sales-force automation system. Some 150 members of the sales force can send ad hoc queries to the warehouse to get reports for sales calls.

“The average sales call costs between $150 and $500,” said Hastie. “When your salesperson goes to a store, instead of bringing coffee and donuts, they should have a report from the system that tells them where the store is weak and what they should talk to the storeowner about.

The system also provides a predictive model that alerts managers to the possibility that a store may be leaving the TruServ network. For example, a store whose orders for plumbing supplies have dropped may be getting ready to try a new wholesale distributor. As soon as the system flags that situation, an alert goes off and TruServ can then send a salesman to talk to the storeowner.

Asked what TruServ will do next with Application Foundation in the area of business intelligence, Hastie said, “Call me tomorrow. We don’t really know. We have a hard time keeping up with our own ideas for the system.”

Seating maker floored by discovery

Traditionally, the furniture industry has not been at the forefront of IT. But Brayton International, a manufacturer of seating environments for the contract furniture industry, may be an exception. The High Point, N.C., company uses BI software from Cognos to improve the speed and accuracy of its reporting.

For years, Brayton ran a locally written RPG application that several companies in the area used as a manufacturing, accounting and inventory system. “On a good year, it was about 60% accurate,” said Markus Hill, director of technology and information services.

In July 1999, the company deployed the SyteLine ERP application from Frontstep Inc. (acquired by Mapics in February 2003). SyteLine ran under the Progress Software DBMS and 4GL. Brayton had deployed SyteLine to centralize data from its different business units, but when it went to run reports and analysis, Hill discovered that it was too slow, unstable and had limited visibility into how the performance of one division affected another. In March 2000, acting on Frontstep’s recommendation, Brayton began an implementation of Cognos’ Impromptu software and data analysis tools. The Cognos software runs off Microsoft SQL Server and links into a 300Mb data warehouse with 40Mb cubes. The system went into production in August 2000.

When the project went live, Brayton had a big surprise. “The internal reports we’d been using for the past year were wrong,” said Hill. “The way we were doing reports under the 4GL was a lot more complicated, and we discovered that, in terms of numbers, what was in our data warehouse and what we were reporting internally didn’t match. Basically, the 4GL wasn’t looking as closely at the data as Cognos was.”

With Impromptu, users could author virtually any report using Impromptu’s frame-based reporting interface, and report data could come from any source. “The way we used Cognos to build the cubes and normalize the data gave us numbers much more tidily than what we got from our ERP/4GL system,” said Hill.

As far as BI systems go, Brayton’s may be small -- only seven executives access the system for BI, a number that Hill expects to grow to 10 this summer -- but it is effective.

“We ran $40 million of inventory through the system but only had an $11,000 variance,” said Hill. “We’re running 99% accurate. A big part of that is that now that we’ve deployed Cognos, we’re measuring all our numbers.”

By deploying Cognos’ reporting, analysis and ETL solutions, Brayton achieved a 576% ROI, according to Nucleus Research, a Wellesley, Mass.-based ROI consulting firm. Nucleus arrived at those numbers by calculating the personnel costs of achieving what Brayton did through other methods, such as hiring a full-time programmer, paying for someone on the outside to write the reports in a 4GL, or going through Microsoft’s ODBC API to resolve the problem.

“But at the time, Progress didn’t go well with ODBC,” said Hill. “We were killing the DBMS three times a week. But once we got a separate reporting system in place, we were able to run with it.”

Attaining any additional BI from the system, said Hill, only requires “a couple of days with your target users to figure out what pie in the sky they’re looking for, then going back and changing some of the cube.”

Hole in their pocket

No matter how hard Motorola’s Global Telecom business tried, it always seemed to get a convoluted picture of how much it was spending. The problem was simply that no tool was able to get accurate information out of Motorola’s global supply-management data warehouse, said IT director Chet Phillips in Arlington Heights, Ill.

When Motorola’s Procurement Leadership Team got together in 2001 to share best practices, they saw that Phillips’ team was able to pull data from numerous sources quickly using Informatica PowerAnalyzer, which was part of the Informatica Corp. PowerCenter suite of analytical applications.

“So they asked us to try to pull together some 30 sources of spending data and put it into one place so they could do analysis on pricing, vendor quality and 100 commodity groups such as semiconductors and cabinets,” said Phillips. “The goal was to get a full picture of our spending situation, so we could have meaningful conversations with our vendors.”

There were two caveats: Do not spend a lot of money, and do not launch a major project that would take years to accomplish. Since Phillips’ group was already using PowerAnalyzer, the project fell easily within budget and time constraints.

Phillips’ team began the project in January 2002. Most of the sources the data had to be pulled from were Oracle DBMSs, but there was also an Ariba Spend Management data store for the purchase of non-production goods and services, and an SAP R/3 data store.

“Using Informatica’s analytics mappings allowed us to get 80% of the data just by pointing PowerAnalyzer at the source,” said Phillips. The only source left is the R/3 system, which Phillips said he saved for last “because most of our expertise [is] on Oracle. We’ll use Informatica’s SAP connector for the task.

Phillips gives PowerAnalyzer high marks for its ease of use. “I don’t let any other tool access my data warehouse. Frankly, it’s set up so the user can’t join the tables wrong. It takes away the risk of a user doing an inappropriate query.” That type of ease of use is critical, since the number of users who currently have access to the data warehouse -- 200 -- is expected to grow to 1,000 by the end of the year, as more points of the supply chain put their data in the warehouse.

While the data warehouse is growing, its eventual size will be manageable. “We don’t want it to be the Swiss Army knife of data stores,” said Phillips. “We only need a rolling two years’ [worth] of data. Purchasing information older than two years has marginal analytic value.” But the key, he said, was getting Motorola’s users to realize that they did not need access to every piece of procurement history.

“At first, it was hard to get the user community to articulate their requirements,” said Phillips. “They said, ‘Hook the warehouse up to PowerAnalyzer and give us everything and we’ll tell you what we want.’ I told them that the data warehouse is not a place where you edit the data.”

Today, the warehouse contains bills of materials, sales, orders, shipment and inventory, but “we didn’t turn on all the canned mappings, then ask the user what was relevant. Loading the warehouse with stuff people aren’t going to use will only bog it down,” explained Phillips. “The purpose was not to re-create all the reports they once had. That enabled us to keep the data warehouse as a reasonably performing piece of infrastructure.”

In the end, said Phillips, PowerAnalyzer “allowed us to analyze tens of millions of lines of information at various levels to help us drive our costs down.” The key, he added, was that the people looking at the data from a 50,000-foot level were looking at the same data as those at the 500-foot level.

“You could roll it up or roll it down, but everyone was looking at one version of the truth, and the cost measurements based on that one version of the truth,” said Phillips. “That was our BI.”

The combination of PowerAnalyzer and the data warehouse “helped us get everyone on the same page,” said Phillips.

“Now the purchase processes are quite the same across Motorola,” he noted. “Our data warehouse was the great normalizer of all the information -- it allowed us to compare apples to apples and oranges to oranges.”