Best Practices Awards 2007
TDWI’s Best Practices Awards recognize organizations for developing and implementing world-class business intelligence and data warehousing solutions. Here are summaries of the winning solutions for 2007.
BI/DW on a Limited Budget
Solution Sponsor: Business Objects
San Francisco–based StubHub is a unique open marketplace that enables customers to buy and sell tickets at fair market value to a vast selection of sporting, concert, theater, and other live entertainment events. Since its founding in 2000, StubHub experienced triple-digit year-over-year growth, straining its existing information management infrastructure.
StubHub’s limited visibility into current data, customers, and business drivers threatened its long-term survival, and StubHub did not have the resources for an expensive overhaul. Instead, StubHub’s BI/DW initiative emphasized investing in specific technologies to empower end users and free up existing staff resources—reusing existing technology investments and employing open-source technologies where possible.
StubHub created a scalable and extensible IT infrastructure to enable dynamic reporting, provide access to rich current and historical data, and reduce data latency. The solution provides actionable customer insights (including definition of key customer segments); quantifies customer lifetime value; and creates customer behavioral profiles. It provides tools to maximize site conversion rates, marketing effectiveness, and customer loyalty. Finally, the solution identifies key business drivers and defines key company metrics.
StubHub successfully replaced its failing database with a stable, scalable, and extensible data warehouse environment. The new database and reporting environment was developed and deployed in eight months (six months to code completion, with an additional two months of parallel testing). StubHub now maintains a rolling two-year road map to build on the platform and continue driving business value through actionable intelligence.
StubHub leveraged industry best practices that can be adopted easily by similar organizations. For example, the initiative was designed by a cross-functional team to ensure full organizational buy-in. The company developed a detailed project plan that included a phased approach and clear milestones. The team used a traditional star-schema data model, a software development lifecycle (SDLC) methodology, and data-quality techniques. Finally, the development team delivered “quick wins” with key reports that demonstrated and reinforced value to the organization.
The StubHub team needed to find innovative ways to achieve an enterprise-class implementation on a limited budget. These included using open-source, on-demand, and free or bundled software; the extensive use of “sandboxes,” which minimized BI headcount and enabled the BI team to focus on delivering value-added projects; repurposing hardware to further reduce capital expenditures; and collaboration with partner groups to create new reports and functionality.
To read more about StubHub’s innovative solution, see the case study.
Solution Sponsor: Teradata
WINNER: Verizon Data Services
Verizon Communications Inc. delivers communication innovations to mass market, business, government, and wholesale customers. Verizon Wireless serves 59 million customers nationwide; Verizon Business operates one of the most expansive wholly owned global IP networks; and Verizon Telecom deploys the nation’s most advanced fiber-optic network.
In 2003, Verizon Telecom developed Performance Assurance Reporting Suite (PARS), a relational online analytical processing (ROLAP) application that serves as a scalable common point of access for metrics and analytics. PARS operates on top of the company’s active enterprise data warehouse environment from Teradata on the same high-speed server.
PARS has changed the way Verizon does business. With PARS, Verizon now has a single 360-degree view of the customer across all acquired brands and regions, and it operates with a single, common version of the truth for reporting and analysis.
Verizon Communications Inc., formed in 2000 by the merger of Bell Atlantic Corporation and GTE Corporation, has continued to grow through acquisitions. Verizon had difficulty unifying the diverse markets and product lines of the former companies and reconciling data across markets and regions. The company recognized the need for universal access to a central data repository to expedite data queries and provide valuable customer insight.
Verizon’s initiative began with an active enterprise data warehouse (aEDW) that served to aggregate metrics across hierarchies. Verizon sought insight through customer and product data to successfully promote its offerings.
PARS is an example of an application designed specifically to fit the needs of its users. It has proved its worth to both Verizon employees and executives. Metrics generated from PARS are now used across the organization for fast insights into business performance, and the application has become the official source for internal and external metrics.
PARS is a unique application, designed by Verizon and tailored to fit the needs of its users. Developed in-house, PARS combines data on individual customers from several billing systems as well as data sources on demographics, operations, and competitive gains and losses. All this information is now available on a timely basis with quick and easy access.
By building its own application, Verizon has saved approximately $2 million in resources and $1 million in licensing fees and maintenance over a four-year period, and also reduced preparation time by 90 percent.
Solution Sponsor: Harte-Hanks Trillium Software
WINNER: UMB Bank
UMB Financial Corporation is a financial holding company that offers complete banking and related financial services to both individual and business customers. Its banking subsidiaries own and operate 139 banking centers throughout Missouri, Kansas, Illinois, Colorado, Oklahoma, Nebraska, and Arizona. Subsidiaries of the holding company and the lead bank, UMB Bank, n.a., include an investment services group based in Milwaukee, Wisconsin, a trust management company in South Dakota, and single-purpose companies that deal with brokerage services, consulting services, and insurance.
Years ago, UMB Bank associated its customers with certain product sets (e.g., asset management services, commercial loans) and managed its technology in the same way; however, many customers crossed over and spanned both lines of business. UMB had excellent individual lines of business systems, but immature customer enterprise information management systems.
During a growth phase, UMB aligned their tools and technologies to fit their business model, which enabled UMB associates to become customer-centric. The transition required breaking down some of the organizational silos that functionally segregated customer data. In addition, UMB needed a mechanism by which all information could be aggregated.
Three key aims drove this initiative: 1) to increase front-line efficiencies by consolidating customer data, thus reducing the research time necessary to quantify the customer relationship; 2) to improve cross-sale opportunities by consolidating current product offerings and identifying complementary products; and 3) to empower associates to fulfill UMB’s mission of knowing its customers. To achieve these objectives, UMB would need an enterprise CRM solution with a focus on data quality best practices.
While the UMB Bank team engineered the business process transformation, a transition manager was appointed from the business side. He first presented the new idea and then introduced the new technology that would enable it. UMB evaluated available technologies that would help aggregate existing host systems to create a foundation for the overall CRM effort. The finished design offered flexibility and logical building blocks for the future. The first phase was launched in May 2006.
The project yielded CRM success, some of which UMB links directly to Trillium Software and Oracle UCM. UMB’s information aggregation model is highly streamlined and has gained accolades from peer group customers and fellow commercial entities.
Elements of UMB’s program are contemporary and novel, such as the concept of making Web service calls directly to UMB’s mainframe environment. UMB also uses a sophisticated series of data-quality and information matching routines to ensure the highest possible level of information consistency.
Enterprise Business Intelligence (EBI)
WINNER: DaimlerChrysler AG
DaimlerChrysler (DCX) is a global automotive manufacturer formed by the merger of Daimler-Benz and Chrysler Corporation. Procurement at DCX is part of an organization called Global Procurement and Supply (GP&S), and is extremely important to DCX because approximately $100 billion is spent annually on parts and services. Cutting these costs by even a small percentage generates large benefits.
After the merger, operational systems for creating purchase orders and paying suppliers could not be standardized quickly, so it was decided to build a data warehouse for combining the procurement summary data. The result is the Global Procurement & Supply Information System (GPSIS), which has grown far beyond the original vision.
GPSIS aids in global negotiation, facilitating achievement of lower costs and higher quality of purchased parts and services. The annual corporate goal for reducing purchased-part costs exceeds $1 billion, and GPSIS is key to meeting that goal, as it is already credited with helping to create savings of millions of dollars.
GPSIS began as a globally available DW with structured, Web-based reporting and has evolved into a fully functional BI platform with strategic planning applications.
The original goal was merely to understand how much business was done with each supplier. This provided leverage for negotiating lower prices based on the full global value of business (VOB) across commodity groups. DaimlerChryler then needed a way to consider the other aspects of TCO (total cost of ownership), since choosing a supplier means more than finding the lowest price. The company decided to use the GPSIS DW as a foundation for balanced scorecards to track supplier performance globally. The next step for the system was to support both Daimler and Chrysler corporations as they operated separately. Management at both companies has expressed strong desire to retain GPSIS functionality for their respective global purchasing operations.
CRM applications are all about providing a basis for managing a company’s relationship to its customers. In a similar fashion, GPSIS is an SRM—supplier relationship management—system that provides a basis for managing the company’s relationship to its suppliers.
Enterprise Data Warehousing
Solution Sponsor: IBM Corporation
Ingenix, a wholly owned subsidiary of UnitedHealth Group (UHG), is a global health-care information company founded in 1996 to develop, acquire, and integrate best-in-class health-care information capabilities.
At the heart of all BI activity at Ingenix/UHG is the Galaxy enterprise data warehouse (DW). Galaxy is an atomic DW with transformations that integrate subject areas across many UGH platforms. Before Ingenix implemented Galaxy, data silos and inconsistencies across these platforms hindered processes and decisions. Ingenix focused on data availability, performance, and information quality to develop Galaxy. The result: Galaxy is now the company’s single source of truth for many applications. Technologies and techniques developed for UHG are also sold to other health-care companies, making Ingenix both an in-house analytics and IT group and a commercial solutions provider.
A centralized program-management office coordinated the transition to Galaxy from two legacy DWs and legacy systems. Requirements-gathering began in 1998, and Galaxy went into production in 2001. Its database was 2.5 TB, growing to 18 TB today. About 50 business analysts, data modelers, DBAs, and software engineers worked mostly in-house, with the exception of IBM data modeling assistance.
The consolidation saved the company several million dollars and has produced ongoing cost savings, eliminated redundancies, and dramatically increased the efficiency of integrating new data sources. Information quality is the foremost objective for Galaxy. Ingenix has three information-quality goals:
- Ensure the company meets business-defined quality standards and goals. Galaxy “must always be valid” to a Six Sigma level of verifiable quality.
- Monitor information quality continuously to save operational maintenance costs.
- Continuously improve Galaxy’s overall quality to ensure UHG’s overall health.
Feeding Galaxy are 350 source input files from more than 25 distinct internal and external sources. Galaxy’s industry-differentiating characteristic is its value-added transformations, which derive information from multiple sources—the heart and soul of the information quality management program. These value-added transformations enable difficult processes such as finding patients to invite to drug trials. Galaxy’s value to this process is in its completeness and breadth, its quantity of information, and its trustworthiness.
Galaxy’s success is due to Ingenix and UHG’s comprehensive and continuous focus on information quality, their organizational commitment to Galaxy across all aspects of the business, and their built-in, industry differentiating, complex, value-added transformations.
Government and Nonprofit
Solution Sponsor: Information Builders
WINNER: Richmond Police Department
The Richmond, VA, police department needed an analytic system that would help determine the probability of particular types of crime occurring in specific areas at specific times. Combining technology expertise from SPSS and Information Builders, the Richmond Police Department (RPD) is now using predictive analysis and business intelligence technology to apply informationbased policy.
The RPD system’s predictive analytics capabilities help determine where a crime may occur and empower officers at all levels to take immediate actions. Overall, the system delivers accurate insight into where crimes might occur, increases public safety, and reduces the number of calls for assistance. From 2006 to 2007, major crime was down 19 percent; from 2005 to 2006, it was down 21 percent.
In 2002, RPD procured an enterprise data-mining workbench from SPSS to predict patterns of behavior that would help them use police resources more effectively. Recognizing the value in this statistical analysis system, in 2005 and 2006 RPD enhanced the system by deploying Information Builders’ WebFOCUS BI tools in conjunction with GIS mapping software from ESRI. The benefits of the system have surpassed original expectations.
The three key elements contributing to the success of the project are real-time information, usability, and portability. The agility and responsiveness of the RPD system gives officers fresh information at every eight-hour shift change, and some data is updated continuously.
Information Builders tied components of WebFOCUS into mapping software from ESRI to create an easy-tounderstand, intuitive presentation of the law enforcement application. Dashboards serve as the key interface. The system offers point-and-click drill-down capability and visual representation of current analytic data.
The new application’s portability is crucial to successful deployment. With data terminals in their cars, officers receive alerts, conduct planning, and develop strategies, and supervisors are able to review performance outcomes in each area.
The predictive analysis system uses a database of arrests, past calls to police, and crime incidents—some going back 15 years. Advanced analytics from SPSS are used to examine how current crime reports relate to data on past, present, and anticipated actions. Advanced analytics include statistical, mathematical, and other algorithmic techniques. From this advanced analysis, RPD’s system allows crime analysts to examine the interaction among data and more efficiently deploy police resources to deter crime.
Government and Nonprofit
WINNER: Memorial Sloan-Kettering Cancer Center
Memorial Sloan-Kettering Cancer Center (MSKCC) is one of the premier cancer centers in the world, committed for over a century to exceptional patient care, leading-edge research, and superb educational programs. The Center has nearly 9,000 employees. In 2006, more than 21,000 patients were admitted to Memorial Hospital, and MSKCC accommodated over 430,000 outpatient visits.
The MSKCC’s data warehouse, the Institutional Data- Base (IDB), was launched in 1988 when the information systems senior management team formed a steering committee from hospital administration, quality assurance, information systems, and the clinical community. This committee defined a methodology to create, maintain, and administer a data warehouse that would ultimately unite clinical, operational, and financial data. The IDB has evolved well beyond its original objective to make institutional data readily available for research, decision support, and executive information systems.
Treatment of patients: IDB data provides clinicians with a patient’s history of chemotherapy; it also helps clinicians study and compare the effectiveness of treatment regimens.
Research: Information Systems’ data delivery group DataLine has provided thousands of reports to support cancer research initiatives, leading to active protocols and published papers. The group has also provided key information in the generation of tens of millions of dollars in institutional grants.
- The IDB is the primary data source for “visit processing,” which supports the coding of ~$1 billion in annual ambulatory revenue.
- IDB reports have been created to help support accreditation from the Joint Commission on Accreditation of Healthcare Organizations (JCAHO).
- Census information from the IDB supports budgetary decisions by MSKCC management.
In 1989, the IDB consisted of two subject areas. Today, 16 disparate applications with various DBMSes feed the IDB to produce more than 1,000 daily reports. In place since IDB’s inception are “morning reports” for each subject area, which are monitored daily in order to identify data anomalies, growth, and exception conditions.
The data delivery team, DataLine, was created in the early 1990s. Starting with one data expert, the group has grown to six, and develops more than 500 ad hoc and scheduled reports per year. Power users also provide data delivery services and run reports out of the IDB for 10 departments, accounting for thousands of reports per year.
WINNER: Airlines Reporting Corporation
Airlines Reporting Corporation (ARC) is an airline owned company offering financial settlement solutions and data and analytical services to airlines, travel agencies, airports, and travel industry analysts. ARC’s cross-functional Data & Analytical Services team manages ARC COMPASS, a data warehouse that captures and analyzes information related to air travel ticketing and holds 39 months of historical data.
ARC has centralized and simplified industry reporting in virtually every internal decision-making and workflow function. The result is significant tangible and intangible cost reductions.
ARC’s company’s first BI application, ARC Document Retrieval Service, helped the company realize $12.5 million in core cost reduction related to shipping fewer paper ticket coupons as the industry became fully electronic. In addition, ARC mines data on behalf of external customers. The results help travel industry customers create targeted marketing plans, assess incentive programs, and optimize promotions. ARC also offers insight for route and network planning, sales trends, customer behavior, demand forecasts, statistical profiles, point of sale analysis, and targeted marketing campaigns.
While most data warehouses are internally focused on sales, customer behavior, and supply chain optimization, ARC’s vision was to provide a travel industry solution to customers and give them the resources of an industry data warehouse.
The Data & Analytical Products business line has experienced better than 100 percent revenue growth in each of the past two fiscal years. ARC offers more than 60 revenue-generating reporting products, and launches five new revenue-generating products each year.
ARC has implemented a comprehensive best-practice data governance framework through the assignment of data custodians and data stewards. The data provisioning team provides data integrity through modeling of the data across business, operational, and technical levels. This team also supports ongoing infrastructure initiatives. ARC develops many of its products and services through an iterative prototyping methodology. Prototypes of reports and even analytical front-end applications are developed very quickly and are easily modified.
ARC was the first organization in the industry to comply with the payment card industry data security standard, and also complies with the European Union Safe Harbor program. With its entry into BI/DW solutions, ARC has expanded existing market segments and developed new segments. The company has significantly expanded its marketing list services with ARC COMPASS. ARC can now provide highly customized marketing lists for destination marketing organizations, airlines, and various service providers.
Solution Sponsor: MicroStrategy
WINNER: Corporate Express US Inc.
Corporate Express, part of the Holland-based Corporate Express NV (NYSE: CXP), is one of the world’s largest B2B suppliers of essential office and computer products and services, with 2006 sales of approximately $4 billion in North America, including $3.6 billion in the U.S.
Corporate Express’ distribution infrastructure is among the industry’s most advanced. It delivers an average of $13 million in products every business day in the United States, $6.31 million of which is ordered through the Internet. To increase sales on all combined orders, Corporate Express built a market basket application to recommend related products to customers during their purchase.
The market basket application is based on reference products and correlated products. When a customer chooses a reference product, the application presents nine mathematically correlated products to pair with the reference product. Optimal pairing is determined by three metrics:
- Support: The probability that the reference and correlated products will be purchased together.
- Confidence: The probability that the correlated product will be purchased once the reference product is added to a shopping cart.
- Lift: The improvement in the probability that the correlated product will be purchased once the reference product is added to a shopping cart, divided by the original probability that the correlated product will be purchased independently.
With the application in place, analysis now shows and often predicts what items might occur together in a checkout session. The BI application is having enormous impact. The company can track all online baskets and identify what SKUs are most profitable or poor performers when purchased in combinations. According to Corporate Express, the market basket application has generated the following ROI results:
- Average order size for basket purchases increased more than 2 percent
- Average order size for orders with a basket pairing is more than twice that of orders without a basket pairing
Many companies present complementary products as part of the shopping experience, often based on the professional judgment of marketing or merchandising organizations, and complements are often intuitive (e.g., pairing staplers with staple removers). Leveraging predictive analytics allowed Corporate Express to innovate past intuition and judgment to base pairing decisions on solid math.
WINNER: Lawrence Livermore National Laboratory
Lawrence Livermore National Laboratory (LLNL), a premier applied science laboratory, is part of the National Nuclear Security Administration within the Department of Energy, and is responsible for ensuring that the nation’s nuclear weapons remain safe, secure, and reliable through the application of advances in science and technology.
First developed in 1985, LLNL’s data warehouse is now a portal-based Java and Oracle RDBMS application considered highly successful in delivering accurate and timely information. While data warehouses are now commonplace, four of LLNL’s approaches and innovations combine to give its users uncommon capability and control in addressing their information needs.
A modular architecture has been employed and continually leveraged for flexibility, control, and self-sufficiency. The Enterprise Reporting Workbench (ERW) interface allows users to create formats and filters and link them to create reports. The modularity has expanded to support interaction with commercial packages, which produced the “workbench” reference in the name—a place where tools can coexist. The ERW home page is a comprehensive workbench of enterprise reporting tools.
Dimension data is separate from fact data, which allows ERW users and application a great deal of flexibility. If fact data contains the base attribute (such as account or employee number), then all relevant dimension data is dynamically available, even if absent from the reporting view. Users can easily create, maintain, and use their own virtual dimensions (URAs) or use a wealth of institutionally maintained dimensions (IRAs). This dimensioning architecture also allows for rapid deployment of whole new dimension sets without programming or creating new view structures.
Over the years, common desktop skills and capabilities have become integral in today’s retrieval and analytical processes. When LLNL found some users were re-keying data from paper reports into other desktop processes, they preserved and staged their report files for FTP download. Today, all output is electronic and available in full-featured .xls, .pdf, .tsv, and HTML. Users can also integrate post-retrieval analysis with report processing within the ERW prior to distribution. The Desktop Data Integrator (DDI) allows users to create a workbook once and upload it to ERW, where its data sheets, pivots, and summary sheets will be refreshed automatically.
LLNL’s philosophy is to encourage, recognize, and support alternative approaches. When it became apparent that users wanted to include report data in further processing activities, they added .tsv and .csv output formats and created a Web repository to stage all reports. When some users and organizations began running global ERW reports for download into local systems, LLNL created the Enterprise Data Depot (EDD), a set of Oracle views cloned from the DW reporting data sources with access controls.
Back to Table of Contents