Chris Framel, application development manager for Albuquerque's Department of Finance and Administrative Services' Information Systems Division (ISD), remembers when he spent too much time hoofing it from PC to PC installing individual Oracle clients, so department staff could access financial data.
Dissatisfied with this approach, Framel began searching for a new way departments could use and search for data. The ISD settled on Cognos' BI Series 7, a suite of business intelligence (BI) tools to gather pertinent data from a large pool of information collected from various departments over time.
Though initially difficult to define, BI technology is an intelligent approach to heaps of disparate information because it allows targeted extraction of data from a large group of data.
"We started looking at ways to get the data out to users in a more user-friendly way," said Framel. "Everybody was using Excel and Access. People were rekeying data into Access or Excel to get reports."
Because the information resided in a mainframe system, the needed data was difficult to extract. Framel and his staff typically had to use a makeshift approach to get the data agency customers needed.
"Initially it wasn't really BI," he said. "It was just extracting the data out of these mainframe files and storing them in Oracle and using client tools to go get the data. That evolved into more of the business intelligence solutions."
BI technology offers numerous ways to extract and analyze data.
If an employee wants to see all items paid to a particular vendor last year, he or she can go into Cognos Query to extract the data, said Carol Scurlock-Garcia, accounting systems coordinator for the Department of Finance and Administration's Accounting Division. With a Web browser, users query the data for specific information by writing queries, or modifying existing queries written by IT staff, who write some queries up front, knowing the resulting reports will contain information necessary to departments.
"We create a lot of standard reports," said Framel. "Using Cognos Query, they can go out and generate their own queries on the same tables we do -- export it to Excel if they want. The departments take it upon themselves how they want to use it, how they access it."
Another benefit is that once reports are saved, they can be made available through the Cognos Upfront Web-based portal, which displays them in a series of customizable folders all users can access. This is one aspect of the learning curve that, according to Scurlock-Garcia, needs to be overcome to realize the full benefits of BI.
"That is the caveat of using this application," she said. "You have to know the tables and where the data resides to report off of it, and so it's a slow learning curve."
Because there are so many different departmental functions for staff to perform, BI technology helps employees obtain aggregate information. In addition to Cognos Query, another BI product, PowerPlay -- online analytic processing (OLAP) software -- allows users to track business performance and analyze business data by running queries and/or writing reports.
PowerPlay pulls data from relational databases to build PowerCubes, which are data sets that can contain more than 1 billion rows of data and 2 million categories. Business rules and calculations can be built into the PowerCubes. These features can be automatically delivered to Web clients, or to Windows and Excel clients, using the same application server.
"They can take a report or query we've written, or a PowerPlay cube we've put out there, filter it down just for their department or division or whatever it is they're interested in seeing, and save that as their own version of the report," said Scurlock-Garcia. "They save it to their folder or department folder. That's nice because they can take a global cube that has every department, every fund, every dollar bill and say, 'I only want this much of it.'"
Other departments found out how useful BI technology is, and the ability to target specific information is a draw that benefits certain departments and their constituents.
"We extract out of an old legacy system a lot of customer, water usage and solid waste history," said Framel. "[We] pull that data out every night and put it out there and they can go out onto the Web and get their own reports. That data is also put out for the public to get."
Using the Web as a conduit for information allows department staff and citizens to easily access data. Everything from crime stats and utility bills to vendor invoices is available.
"When you come in and run a report, it's starting up a program on a Web server going out to the Oracle database, bringing back the data, and presenting it in either Excel, PDF or HTML format," said Scurlock-Garcia. "Before, we had departments that didn't have anyone who had the skill set to go out there and pull that information down for ourselves, but with us putting it out there, they can. If you can click on a mouse, you can get to this report."
The change for department staff wasn't easy. When the city first implemented BI technology, not all staff members were excited about the change.
"It was difficult to sell to our users," said Scurlock-Garcia. "A lot of people don't like change. They get used to one thing and want to know, 'Why are you changing things on us?'"
Despite the benefits, convincing users BI technology was a benefit took time because not only did staff have to learn to use a new system, they also had to look at data differently.
"It's still hard for some people to grasp analytically reviewing their data for exceptions, instead of reviewing straight transactions like they've always done," said Framel.
The staff used to wade through pages and pages of reports, and suddenly they had to get used to understanding that BI technologies gives them the opportunity to more intelligently pursue needed data.
"You can still get the detailed transactions, but I think sometimes the difficulty arises in, 'How can I use this data differently to make my job easier?'" said Framel. "So instead of going down a list of 1,000 transactions to look for an exception, they can create a query that goes out and gets the data and gives you the exceptions."
To help ease the transition to using the technology, the department offers quarterly training, as well as Monday afternoon workshops that allow interested staff to obtain specific training they desire.
The amount of information collected over the years by an agency like Albuquerque's Department of Finance and Administration practically mandates a dedicated manner to query, browse, organize, and subsequently, use information.
In the end, savings in time, money and efficiency make BI technologies a sure method to wade through governmental madness.