The data mart project, which was already started, was bolstered in the fall of 2003 by a major facilities initiative called the Research Campus of the Future, which necessitates vacating 700,000 sf of R&D and office space inventory in older facilities by 2009, and replicating the capability in approximately 500,000 sf elsewhere.

"We were tasked with providing some readily available, accurate information—combined with data found in a lot of diverse systems—that can be sliced and diced to provide decision-support tools for this major initiative," says Erik Anderson, PNNL's manager of integrated information for facilities and operations. "This challenge didn't really drive the data mart project, but it put a lot more attention on it."

PNNL, operated by Battelle, is a U.S. Department of Energy Office of Science laboratory that solves complex problems in energy, national security, the environment, and life sciences by advancing the understanding of physics, chemistry, biology, and computation. Because PNNL's work and facilities are highly regulated, reassigning 700,000 sf is a complex and precise task. Anderson needed to create a way to pull space, occupancy, and chemical and property inventory data from the existing data warehouse, and combine it instantly into dynamic reports that were both timely and accurate. He also wanted to make this information readily available on the company's intranet.

Building on a Solid Foundation

Before the data mart was completed, gathering facilities information was a hit-or-miss proposition. Two reports on a single topic could take days to produce and provide two different sets of data. The reason was that facilities data was stored in various systems and database formats. Definitions as basic as square footage varied depending on the source of the data: Some people assumed it meant gross square footage, while others used the term to mean net occupiable square footage. And some information could be current to the day, while other data downloaded simultaneously from another source could be months old. This discrepancy created confusion when the information was combined into a single report and it made it difficult to accurately track trends.

"We had a lot of work to do in shortening the time span of people running around gathering information from one or more of our information systems, from their homegrown Excel spreadsheet, talking to three or four people, getting information out of their head, putting stuff together, and responding to those questions," says Anderson. "There was no one-stop shop. It was not intuitive how to even get the right information."

PNNL had a head start creating a data mart because it already had a data warehouse system that consolidates data from the computerized maintenance management system, computer-aided facilities management system, and chemical management, as well as from systems owned by the finance department, human resources, and others. Using programs designed by the in-house IT staff, the data warehouse converts all the data—which arrives in Microsoft SQL Server, Oracle, Excel, Access, and Lotus Domino formats—into a standardized SQL format, arranges it in tables, and renames it in a user-friendly way.

The facility data mart takes that data and uses it in two ways: It selects a subset of information that the owners are comfortable sharing with the facilities department, and it makes that information available to be manipulated into specialized reports. These reports, currently generated with an Active Server Pages (ASP) program called "Report Catalog" designed by the in-house IT staff, have limited capabilities because all the data is presented in a tabular format. Departments soon will be able to choose between Business Objects and Microsoft Reporting Services for their reports. The reports are displayed on the intranet using ASP pages and Excel pivot tables.

"The difference between a data warehouse and a data mart is that the primary focus of a data warehouse is integrating and sharing data, system to system," says Anderson. "The real purpose of the data mart is reporting.

"There are over a dozen systems just in facilities," he explains. "The person pushing the buttons on the Web page to get that report doesn't need to know where that data comes from."

Business Council

The data warehouse was not the only head start PNNL had. It also had a Business Information Systems Council in place, which established a good working relationship between the IT department and the users of their technology. This is critical, because many departments are squeamish about allowing access to their data.

"I have heard a lot of my peers say, 'HR won't give us their data,'" says Anderson. "HR doesn't want your hand in their cookie jar. There is social security information, Privacy Act-protected information, and salaries, and they don't want your hand in there to get the data out. By the same token, we don't want HR to start playing around with facility location data. So we want the subject matter experts to manage the data that they own."

The Business Information Systems Council is made up of people who can represent data stewardship from the major organizations in the Laboratory on the support side. Anderson represents facilities and operations; other members represent human resources; finance and contracts information; environment, safety, health, and quality; scientific and technology information; strategic planning; and each of the four research directorates: national security, fundamental science, energy science and technology, and environmental technology. Four senior IT managers serve on the Council but they do not have a vote.

"We sit down with the top management from our in-house IT organization and work on their plans," explains Anderson. "We influence their budget submittals. We talk about better ways to share data back and forth and move the whole organization farther ahead from the integration standpoint.

"We have a pretty progressive IT group," he says. "It speaks really highly of them."

As a result of the Council's work, each source owns the data and accepts responsibility for it, and no one else tries to replicate it. That means that each source can protect the integrity of their data, but also must work to make that data available to others who are doing the same. They also must own data that falls in their purview, even if it is data requested by someone else. For example, Anderson owns location data on all buildings on all campuses at PNNL. If the Laboratory is doing research on the north slope of Alaska, on property PNNL does not own, Anderson previously would not have been concerned with it. He now must take ownership of that location, as well.

"I have to accommodate the rest of the Laboratory, not just what the facilities organization needs," says Anderson. "By doing this, there is a little quid pro quo. I think this is important, and key to our success. If I need some additional information out of the HR system that they are not currently tracking, I can ask them for that, and they will do it. This Council sits there and mediates that."

The Data Mart Takes Shape

The facilities and IT organizations conducted the first phase of the data mart project in September and October 2003 by mapping the original sources of data throughout the Laboratory. They then built the facility data mart and added feeds from the source systems so the data could be updated nightly. The final step was to design and build four sample reports. Because the central IT group had already made substantial investments in the data warehouse and data mart building blocks, that initial phase cost the facilities organization only $19,000 to complete.

The second phase, which cost just $5,000, expanded on that model by soliciting requests for additional reports, adding the required tables, and building the report and Web links. The plan is to refine the system and add more reports each quarter, says Anderson.

The feedback from users has been overwhelmingly positive.

"One of the side benefits of the data mart is that errors show up," says Stacy Austin, CAFM systems administrator. "Since we get a report showing gross square foot per staff and office square foot per staff, anomalies jump out of the page. It's been a good tool to help us go back in and fix some data quality problems."

"The data mart will put my staff out of the business of generating about six different weekly reports for each of the Work Centers," adds Ed Dunbar, manager of Facilities Project Management Support. "That's a good thing, because then we can spend our time analyzing the information instead of gathering the information."

Anderson expects to see a return on the modest $24,000 investment in just over a year. He estimates that the data mart will save $20,000 a year in acquired efficiency.

"The bottom line is that we are providing timely, accurate, easy-to-understand decision support information to facilities and laboratory management," says Anderson.

By Lisa Wesel

We welcome your Questions and Comments

Copyright 2008 Tradeline Inc.
All Rights Reserved
ISSN: 1096-4894