Siemens Tracks Travel Thru In-House Data Warehouse
<B> Siemens Tracks Travel Thru In-House Data Warehouse</B>
By Mary Ann McNulty
In an innovative use of off-the-shelf technology applications, Siemens Corp. has built a travel data warehouse in Santa Clara, Calif., to replace the reams of paper formerly distributed to its 22 operating units.
The brains behind the effort, Stephan Meyer, manager of IT tools for travel and fleet services at Siemens, recently was cited by Windows NT magazine, a business journal read by more than 300,000 IT executives, as one of 10 NT innovators of 1998. Meyer found the solution to Siemens' data needs in Cognos' PowerPlay Server Web Edition, which offers a tool to transform relational data into a structure optimized for online analytical processing, or OLAP. Cubes of travel data are placed on the company's intranet, running Microsoft Windows NT and Internet Information Server 4.0.
Since February 1998, those responsible for travel management in Siemens' various U.S. divisions have been able to access a dynamic database that details Siemens' $100 million annual air tab and $200 million annual travel and entertainment spend. The database combines booking data from Siemens' two travel agencies with expenditures from two corporate card vendors, two preferred car rental vendors and multiple hotel vendors.
Managers can analyze any elements of the three main "cubes" of data--air, car and hotel--to view spend by vendor, city, class of service or other category. They can view data for their business unit, as well as combined data for all Siemens operating units in the United States, Meyer said. The database can provide virtually any details about spend, though each business unit still must rely on agency reports--sometimes paper reports--for exception reporting.
The users can drill down into operating units, divisions or departments, Meyer said, but are unable to drill down to individual spend, due to both privacy concerns and the drain such details would have on computer performance.
"If we add the layer of individual traveler, it would double the amount of storage space and response time," Meyer said. "We have a good balance now, with just a couple of seconds for most data queries."
To assemble the data, Siemens' travel department receives Zip drives--high capacity storage devices--from its preferred vendors each month. The raw data is loaded into a relational database or warehouse, where the data is difficult to analyze and the process is time consuming.
"To simplify and speed up analysis, we transform relational data into cubes and make them available via the intranet," Meyer said.
It usually takes two days for what Meyer calls "data staging" or reviewing files, ensuring integrity and running scripts that match up proper data fields as the system loads the data into cubes. Then, managers are able to query the database for trends analysis, negotiating or periodic reports to senior management.
Some managers are accessing the data as often as twice a week, looking for nuances and trends; others only access it when they need an answer.
The online querying is a far cry from the paper reports the travel department has been distributing since 1994, Meyer said. The department collected vendor data each month, but could distribute it to the business units only in a paper format. And the reports couldn't always answer management's questions.
In 1996, Meyer began thinking about building a data mart to distribute the data, but he didn't begin developing a blueprint in 1997. By that fall, Siemens management committed the $20,000 or so needed for hardware and software, as well as the resources for Meyer and others within travel to hammer out the concepts and work with vendors to secure the data in the proper format. A precise cost figure is difficult to identify, Meyer said, as the project was included with several others undertaken by the travel department.
After thinking about how to lay out a travel data warehouse for years, Meyer took little time to actually implement the plan. As with any good database design, "you have to really think and rethink, ponder the idea and see if it matures," before building, he noted.
Certain that his concepts were mature enough, Meyer selected Cognos' software, which at the time was one of few products that could be deployed with little programming. Although Oracle now has a similar product, it wasn't available in 1997. The decision to distribute the data over NT servers was made for him, as that was the company standard. Outsourcing to a third party was not an option, he said.
For weeks, Meyer worked with vendors to ensure the integrity of the travel data and add to its richness. These processes have become ongoing efforts, he added, with the most recent emphasis on securing line-item transaction details, a step below folio data, from Bass Hotels & Resorts.
Another ongoing challenge, Meyer has found, is keeping up with the insatiable demand of users for more data and analytical capabilities once they have been given a taste. Meyer and an associate continually upgrade the system to meet the most crucial requests. On an ongoing basis, Meyer estimates the data warehouse costs about $10,000 a year to maintain.
Managers have required little training on the software. "The system is pretty self-explanatory. If you know what a city pair is and the time frame, you can obtain information," Meyer said. He prepared a brief training brochure and offered onsite training whenever he visited major operations. The software also offers online help, and Meyer has fielded questions over the phone.
The exposure in the computer publication did generate a few calls from suppliers and corporations that would like Siemens to duplicate its success for them. However, Meyer said, for now, the benefits are being enjoyed only by Siemens' business units.