Unlocking Operational Data

Your sales team has promised 25,000 widgets, but an inventory report that manufacturingneeds to produce those widgets can't be run right away. Does that sound like the kind ofdilemma that has catalyzed business process re-engineering and brought major changes toinformation systems resulting in, at last, a data warehouse project?

If it does to you, you're not alone. In fact, you're in the majority. The DataWarehousing Institute (DWI; Washington, D.C.) reports that already more than 3,000companies have built data warehouses, guided by more than 1,000 consulting firms. Mostindustry analysts expect the data warehouse market to continue growing from more than $2billion in 1998 to nearly $7 billion in 1999, with 15,000 data warehouse projectslaunching in 1999, each with $3 million budgets, according to The Gartner Group (Stamford,Conn.).

Data warehousing originally allowed companies to unlock the data accumulated in a fewoperational systems and begin leveraging it, using select tools, with data from externalsources primarily for improved decision making by a few executives and other specialists.More recently, the call has been for finding additional ways to cut costs, identify newmarket opportunities, measure the effectiveness of campaigns and monitor a business'sprogress. In addition, the pace of business (as it goes about globalization, acquisitions,downsizing, business process re-engineering and so on) calls for accessing enterprise databy more people, faster and with greater flexibility.

With such big market numbers at stake, industry players have given data warehousetechnology a lot of attention. And while data warehouses planned today are capable ofsupporting global enterprises, they had humbler beginnings. The data warehouse began as ahistorical, read-only, isolated and function-based database. But with advancingtechnologies, next-generations of data warehouses support a read/write basis and affordsophisticated new views of an operational, enterprise-based database.

Further, the process of re-implementing data warehouses involves developing a businessmodel that overlies the warehouse. It also requires decisions on more pragmatic, butessential, details. These details include specifications of methods for assimilatingoperational data, enacting user policies governing the access and usage of a datawarehouse and providing methods and tools to access and administer the data warehouse: allof which must be done more cost-effectively than was previously possible.

House Full Of Troubles

What has proved so troublesome for data warehouse architects and proponents is thatplanning, configuration and deployment of data warehouses encompass volatile and rapidlyevolving sets of company-specific processes, product technologies and lifecycles, customerneeds and other variables such as government regulation and deregulation. Furthermore, forbetter or for worse, no data warehouse will ever be complete. New markets will bediscovered, advances in technology create products and services and IT managers attuned tocorporate user groups will always be adding to their to-do lists.

These needs are reflected in a growing emphasis on data warehouses for OnlineAnalytical Processing (OLAP) as opposed to Online Transaction Processing (OLTP). The keydifference and advantage is that OLAP prescribes an approach of live, ad hoc data accessand analysis. In contrast, OLTP deals with the currency and integrity of day-to-dayprocesses.

Unfortunately, transactional databases are not suitable for analytic purposes because:

  • Transactional databases contain only raw data, and thus, the processing speed will be considerably slower
  • Transactional databases do not store the historic data necessary for data analyses
  • Transactional databases are not designed for querying, reporting and analysis and limit performance on those tasks
  • Transactional databases are inconsistent in the way they represent information. For example, different databases may use different units of measurement for the same attributes.


Honors for coining the term "data warehouse" in 1990 go to William Inmon, CEO of Pine Cone Systems (Englewood, Colo.) and author of several books on building, using and managing data warehouses. Defining it as a managed database, he described the data itself by four characteristics. These are important to keep in mind as you remedy the shortcomings of existing data warehouses and for guiding upgrade plans.

Subject-oriented: There is a shift from application-oriented data (i.e., data designed to support application processing) to decision-support data. If designed well, subject-oriented data provides a stable image of business processes, independent of legacy systems. In other words, it captures the basic nature of the business environment.

Integrated: The database consolidates application data from different legacy systems (usually old-style mainframe databases) that use different encoding, measurement units and so on and it eliminates inconsistencies in the data.

Time-variant: Informational data has a time dimension. Each data point is associated with a point in time and data points can be compared along a time axis unlike operational data which is valid only at the moment of access.

Nonvolatile: New data is always appended rather than replaced. The database continually absorbs new data, integrating it with the previous data.

Skeletons In The Warehouse

Just as the backbone of OLTP is the operational database, the backbone of OLAP is thedata warehouse database. Hence, the successful data warehouse is the linchpin tofulfilling the next generation of needs across the whole of the business, the enterprise.Even better news for companies is that data warehouse solutions are being verticallyoriented, tuned to the specific requirements of, for example, financial services,telecommunications and manufacturing industries.

Companies build data warehouses for a variety of reasons. Most data warehouses attemptto solve fundamentally similar issues: analysis, trending, "What-if?" scenariocreation, reporting and multidimensional analysis. Some companies are gaining control overERP and extended supply chains while others want more effective marketing and salesresearch and analysis and still others may need to integrate back office processes toextend them to the enterprise.

"Companies across a wide spectrum of industries are making themselves prominentfor their business efficiencies that even customers notice in terms of quality of service.Among these industries and companies are Bell Atlantic, GE, Capital One and First USA, UPSand HP's own groups such as the InkJet Products Group," says Rick Millem, DataWarehouse Marketing at HP. These organizations base their prominence and success to alarge degree on having integrated, migrated and upgraded data warehouses.

While the table below shows that industries have many requirements in common, datawarehouse design is anything but cookie-cutter in its packaging and configuration. Nearlya decade of data warehouse deployment has taught that each one is specific to thatcompany. HP has helped customers implement more than 400 warehouses worldwide, accordingto E-business consultants at the Patricia Seybold Group (Boston, Mass.). Many of them arelarger than 100GB meaning that real-world experience can be a tangible factor insuccessful deployments from this time forward.

Data warehouses and their smaller cousins, the data marts, are the foundation of"the agile enterprise." These are the basis for knowledge-based organizationsand wider circles of company users who are made ever more nimble with their use. And ableto respond quickly and gracefully with decisions regarding the design and introduction ofnew products and services tuned to market opportunities, manufacturing management,inventory, distribution, resource allocation, financials, HR, sales automation andcompetitive strategies.

Just as data warehouse design becomes more sophisticated about how it serves up data toend users, front-end applications, too, are savvier in the way they access the data fromthe desktop. For example, smart plug-ins and add-ins provide links between commonapplications, making them powerful data analysis tools. A good example of how thiscontributes to users' acceptance of data warehouses is Crystal Reports, a popular reportgeneration tool integrated with most databases that lets end users create consistentreports, regardless of what data source they're accessing on the back end.

Progress along this line goes back to vendor collaboration. The resulting"pacts," as the Baan Company calls them, can dramatically reduce the complexityof purchasing, implementing and maintaining business software. Baan's pacts with HP andMicrosoft have led to the optimization of the Baan IV BackOffice enterprise businessapplication software for Microsoft SQL Server and HP's NetServer LX. Users have alsobenefited from related developments such as porting MC/Service Guard to NT, which meetsdata warehousing requirements for maximum uptime.

Vendor applications are much smarter now in the way they streamline the integration oflegacy database information systems rather than replacing them, e.g., data warehousesbeing used to supplement ERP systems. The benefit is that it accommodates or facilitatesthe rapidly changing nature of business that results from acquisitions, productinnovation/lifecycle, government regulation, etc.

Application Areas Within the Industry

Manufacturing Inventory management Sales order analysis Product and customer profitability Promotional effectiveness Yield management
Telecommunications Product profitability Call Rated Detail (CRD) tracking Performance forecasting Help Desk reporting Accounting and financial systems
Banking Customer retention management Risk management Customer acquisition Channel management Performance measurement
Health Care Provider profiling Customer segment trending Risk management Human Resources Performance Measurement
Insurance Product affinity Accounting and financial systems Human Resource management Performance measurement Customer Relationship management
Retail Distribution Market basket analysis Inventory management Seasonal sales forecasting Vendor tracking E-tailing and online catalog analysis

Warehousing While You Wait

Design and construction, implementation and debugging were processes that once had totake place at the customer's site. But with years of experience behind them, many vendorsare working together to solve basic problems ahead of time, before they arrive at theclient site, often before they start working on the client's nickel.

It's become easier for IT managers to look good. New, proactive management tools suchas HP OpenView MeasureWare, in combination with HP OpenView PerfView, provide triggers andalarms that sound when preset performance thresholds are crossed. The MeasureWare Agentconstantly monitors measurement data to detect exception conditions, based upon individualor a combination of metrics that can be defined using both thresholds and time duration.

For example, an exception condition might be defined as occurring when a datawarehouse's response time exceeds a pre-defined threshold. Whenever the agent detects anexception condition, it produces an alarm message. The PerfView management consolereceives and maintains a list of MeasureWare alarms that occur anywhere on the network.These products greatly simplify management and monitoring, leaving IT personnel more timeto focus on how to use the knowledge contained in the database.

Application performance management, a prominent subset of data warehouse and networkmanagement tools, affords a view into mission critical applications at a level closest tousers answering the question of how well an individual application is performing. Used inconjunction with network performance monitoring tools, IT groups can optimize overallperformance and allocation of server resources and meet service level agreements.Furthermore, based on tracking and analysis of performance as transactions traverse thenetworks, administrators will get a far better picture of where they should spend more oftheir time.

The addition of application performance management, as a discipline now supported withsoftware tools, fills in one of the remaining gaps in end-to-end solutions. Some ITmanagers have taken this one step further with HP's OpenView SMART Plug-In productsthat provides them with centralized, pre-packaged management of the distributedenvironments for many specific data warehouse solutions.

Pre-sales planning and testing processes have gotten considerably better, as measuredby the speed of many deployments. Several recent large data warehouse implementations fortelecommunication companies, manufacturers and universities illustrate how to successfullyroll out data warehouses within four to nine months.

Master Of The House

With the experience vendors have gained at developing proposals for data warehouses andby following well-practiced business process studies, the design recommendations andconfigurations show far fewer configuration errors and order resubmissions. Moreover,today's Requests For Proposals (RFPs) encompass people issues and user needs while stillintegrating business goals, management support and training activities, so that littledevelopment occurs now on the customer site.

Because applications of data warehouses are as varied as the industry types they serve,next-generation data warehousing success stories don't require a paradigm change.Evolution, integration, and extension may be more in order and, though the improvements totechnology may be subtle or even transparent to a corporation's end-users, measures ofimproved user proficiency/agility, ROI and cost-of-ownership will be clearly significantto all.

Must Read Articles