Technology can simplify your institution’s financial transactions—if you can overcome the eventual glitches that are sure to occur.
The Illinois Institute of Technology (IIT), founded in 1890, is a Ph.D.-granting university with more than 7,700 students in engineering, sciences, architecture, psychology, design, humanities, business, and law. The Chicago-based university’s interprofessional, technology-focused curriculum is designed to advance learning through research and scholarship.
To provide business intelligence to our schools and departments, IIT tries to take advantage of new software and electronic devices that will help solve business-reporting issues and allow university users to quickly analyze information. In April 2006, we decided to implement a new enterprise resource planning (ERP) system for financial and grants administration, procurement administration, human resource management, and student administration.
At the same time, we updated our financial general ledger and accounts payable system from our legacy financial reporting system to correct a long-overdue weakness: Users within our schools and departments could not extract information from the financial reporting system without the assistance of our information technology group. The result: Users with special data needs joined a queue, with waiting times varying depending on IT’s current priorities. Even after extracting the data, the process was not complete because users still had to convert the data into business information using Microsoft Excel.
With the newer ERP system, we hoped to eliminate duplication of effort by integrating our general ledger and accounts payable system with our student system, and to get business information to our departments so that they could make better and more timely decisions. We wanted to speed up this lengthy process and give our controller’s office, president’s office, and fiscal offices in the schools the ability to self-serve.
No Building Required
We selected the Banner ERP system for financial and grants administration, procurement administration, human resource management, and student administration modules, as well as the Banner portal and self-service modules. Already integrated with the system—so no building required—the portal and self-service modules allowed students to go online and retrieve their records, pay the university for tuition, and register for classes. This helped eliminate the line of students who each semester waited to pay for tuition at the bursar’s office.
Other benefits:
- Faculty could use these modules to track spending and class enrollments.
- Staff could enter and approve time cards and examine financial information.
- Both faculty and staff could see their pay stubs, direct deposits, vacation balance, tax forms, and W-2s.
- We could direct people to university Web sites for different policies and procedures—for example, financial or human resources.
- Students could access both libraries and research.
Making a Start
The financial and grants management implementation began in September 2006 and was completed in May 2007 in preparation for FY08. The human resources and student administration were completed later in FY08. In the finance division, which includes the controller’s office, budget office, and auxiliary services, we decided to use the software’s reporting functionality for our team in the controller’s office and its self-service queries for academic faculty and staff.
The grants accounting module allowed us to track projects in academic budgets. From a grants perspective, we needed the ability for the principal investigators, faculty, and staff to see what their project-to-date balances and actuals were so they knew how they were doing. In addition, it gave us the ability to enter additional reporting attributes that made it easier for us to report. For example, we were able to distinguish between federal and state funding, identify different cost rates, and recognize different principal investigators. In the past, that type of reporting would have required us to integrate two systems.
Unfortunately, during the development and testing phases, we discovered that the reporting functionality did not easily allow the finance staff to create ad hoc reports as needed or offer the ability to download data into Excel. To fix these glitches, in the spring of 2007, the university’s information technology and finance division teams reviewed several options and elected to implement Oracle Discoverer and Banner Operational Data Store (ODS) for the controller’s office and student administration departments. The Discoverer tool, which connected directly to ODS, went into production at the same time as Banner ERP, giving those of us in the finance department the flexibility to generate special and standard reports when, for example, deans and faculty requested them.
In the controller’s office, we found Discoverer to be a good business intelligence tool for developing standard and ad hoc financial reports and to export information into Excel, but we uncovered a significant drawback. Although we could generate reports, we could not deliver them through e-mail. Instead, we had to manually deliver reports. Meanwhile, the faculty and staff were struggling to obtain summary financial information for their respective operational and grants accounts. Although we considered using a report-distribution tool connected with the prior ERP system, we decided the tool was difficult to navigate. In addition, it could not be exported to Excel.
A Focus on RCM
In June 2008, we expanded the scope of our efforts. We realized that if we ever wanted adequate business analytics, we needed a data warehouse infrastructure that integrated all of the information from all of our systems in a logical fashion. The finance division and information technology department selected and purchased IBM Cognos Enterprise and Banner Enterprise Data Warehouse (EDW) to form the university’s comprehensive business intelligence system. With the marriage of these two products, we hoped to generate and e-mail financial reports, conduct intricate complex reporting with the advanced report writer, and set up dashboards so people could see summary information at a glance.
Our controller’s office and IT began implementing the Cognos query and report studios in September 2008 and completed implementation in January 2009. The primary users were the controller’s office, budget office, human resources, registrar’s office, financial aid, and academic affairs. The tool was connected to ODS views as the basis for developing financial, human resource, and financial aid reports.
During this implementation phase, we created a financial reporting team that included members of the controller’s office and academic budget managers. Our team developed summary and detail financial reports that would satisfy the business requirements of academic areas. We developed eight reports covering operations, gifts and endowments, and grants and contracts, that helped academic departments monitor their progress and transactions.
The financial reporting team also directed the development of the new responsibility center management (RCM) reports, which are similar to income/expense statements for each of our eight colleges. The implementation of Cognos enabled the controller’s office to complete RCM reporting in May 2009 and disburse the RCM reports to our colleges in June 2009.
There was another benefit. Research—which is IIT’s second highest revenue generator—is a key area at our institution. Out of IIT’s $250 million annual budget, $50 million is devoted to research and about $30 million to employee salaries. To manage and expand our research, we require good business reporting while being in compliance with federal regulations, which the tools allowed.
Complexity and Needs Collide
However, we soon discovered that our chosen suite of tools required an enterprise data warehouse to take us to the next level. The university could not take full advantage of Cognos because the controller’s office and IT lacked the financial resources for hiring and training additional staff, for programming the necessary dashboards and reports, and for building the EDW.
Fully implementing the Cognos functionalities and the enterprise data warehouse would have given us the ability to obtain two critical functions:
- Online analytical processing (OLAP) cubes, which allow users to quickly access multiple dimensions and measures of detailed information with the click of a button. At IIT, we think of dimension as a way of looking at nonquantitative information. For example, a month is a dimension of time, which is important when users want to see historical and future trending. Another dimension might be the type of research grant or type of cost within the research grant. On the other hand, a measure, such as a budget or the cost of salaries, is quantitative.
OLAP cubes are a type of infrastructure that permit authorized users to drill down from summarized dashboards, reports, or scorecards to detailed reports by date, funding, account, and so forth. This drill-through feature greatly enhances the ability to quickly understand and analyze financial or student data. It was a function that we wanted to use.
- Row-level security within the business intelligence tool. For example, we could have stipulated that the budget manager in the arts and sciences school could view only the data dimensions in his or her college and not in engineering. Although we attempted to establish filtered financial data access within the ODS views based on the end user’s Banner ERP access, we abandoned the project. Access was just too complex.
Assessing the Situation
In February 2010, we began assessing the efficiency of our business intelligence tools. We identified six problem areas with our current systems:
1. Limited resources. Without additional funds, the controller’s office and information technology departments could not implement the EDW and OLAP cubes, which would give the faculty and staff access to the Cognos dashboards and allow them to quickly access detailed information online—without using two different systems. In addition, the controller’s office did not have the dedicated resources or training to quickly develop and deploy new reports.
In January 2010, the budget of the controller’s office was cut, which meant we could no longer afford the staff person who handled Cognos reporting, including programming and development. Although the controller’s office attempted to fill the position by reassigning staff on a part-time basis and providing additional staff training, the controller and deputy controller quickly realized that report development was a full-time responsibility.
2. Lack of account hierarchies. The controller’s office couldn’t use different charts of account hierarchies for internal and external reporting. The Banner financial module provided the controller’s office with the basic charts of accounts format, which was easily duplicated with the ODS financial views for external reporting. However, any variation on the charts of accounts hierarchy required the controller’s office to use attributes or reporting fields within Banner. The attributes worked on a limited basis, but they did not allow the controller’s office to implement multiple charts of accounts hierarchies.
The multiple charts of accounts hierarchies were especially vital for internal RCM reporting and internal grants reporting. Using different hierarchies for RCM reporting, versus grants reporting, would allow the controller’s office to easily tailor reports to meet our internal clients’ requirements.
3. Insufficient grants reporting. The faculty required quick access to individual grants, including the ability to drill down into the transactional information. Academic staff required summary grants reports that listed indirect cost recovery, labor distribution, grants by principal investigator, grants by departments, spend rate on each grant, and the available balances for each grant.
4. Inability to implement the OLAP cubes. Not having this functionality limited the controller’s office and academic area’s ability to create new reports, dashboards, or scorecards. This was especially evident with grants reporting to faculty and RCM reporting to academic areas. The lack of timely grants reporting became a leading cause of the faculty’s overspending or underspending on grants. Financial grant reports, which were only sent on a monthly basis, did not allow the faculty to drill down for analysis. As a result, salary and nonsalary expenses were missing or allocated to the incorrect grants.
5. Lack of row-level security. The controller’s office could not grant the academic administration users access without compromising internal system security.
6. No drill-through functionality on RCM reports. Without this function, the academic administration could not quickly diagnose a financial problem. Academic administrators were limited to receiving reports on a monthly basis in a PDF format. If they had any questions, they had to retrieve additional information from a different system, which was time-consuming and inefficient.
A Six-Part Solution
Based on this assessment, the controller’s office decided to review different business intelligence alternatives. We wanted the solution to meet six requirements:
- Low cost of implementation.
- Outsourced implementation and ongoing maintenance.
- Access to OLAP cubes.
- Easy-to-use system.
- Data exportable to Excel.
- Row-level security.
Keeping these requirements in mind, the controller’s office in March 2010 began discussions with AnalyticsWare, a vendor I had used in a prior position. The company offered—at no cost—to build a proof of concept OLAP cube, basic reporting, and row-level security system using the university’s financial information and software. The proof of concept would incorporate reports, dashboards, and Excel functionality.
In April 2010, the controller’s office and vendor teams presented the proof of concept to the vice president of finance and administration. In May 2010, the controller’s office and vendor teams presented again to the university’s provost, chief information officer, and the dean of the graduate college and research. Each presentation covered the drill-through functionality, ease of use, and row-level security.
We believed that this outsourcing solution could solve our problems.
In addition to the business intelligence tools, the finance division was seeking a fixed-price implementation to ensure that the system was implemented using the most cost-efficient processes. This included no travel costs for consultants; system implementation and development to be completed using telecommunication tools such as e-mail, web conferencing, and a virtual private network; and use of existing university servers and software licenses.
Who Does What?
In July 2010, the controller’s office and the vendor began the implementation of general ledger and grants OLAP cubes and row-level security. We divided assignments:
- The controller’s office provided the end-user reporting, security requirements, and testing.
- IT set up the development and production applications and database servers.
- The vendor loaded the software, set up the OLAP cubes by converting the proof of concept into a development database, established the row-level security, and handled the migration from development to the production servers. The university elected to extract data from the Banner ODS tables into the AnalyticsWare database to minimize daily system usage disruptions. For example, we didn’t want to diminish students’ ability to access information.
In September 2010, the general ledger and grants OLAP cubes went live and were used by the controller’s office as a pilot program. During the pilot program, those of us in the controller’s office could review the general ledger and grant cubes, enhance the reporting and dashboards, and implement the Excel Analysis Services, which enabled an end user, such as a dean, to connect directly to the OLAP cube using Excel. This allowed the controller’s office to grab data points and place them into Excel spreadsheets to develop any type of ad hoc reporting analysis. When users needed to update the financial information, they simply refreshed the data.
In November 2010, we completed our pilot program, and the row-level security was up and running for faculty and staff, giving them the ability to review their summary and detail reports online, drill down for more data, and download into Excel. In addition, staff was granted access to the Excel Analysis Services functionality. In January 2011, the payroll OLAP cubes went live and provided end users with payroll and labor distribution information.
Leveraging With the iPad
The controller’s office began exploring other ways to use the AnalyticsWare software with new technologies. Since the university had issued iPads to freshmen as part of our iPad initiative in 2010 (see sidebar, "Putting iPads to Work"), the controller’s office wanted to build on that synergy. Because the majority of faculty and academic staff were also issued iPads, an application would greatly enhance their ability to analyze data outside their offices.
We asked the vendor to build an iPad application with the following business requirements that would:
- Take advantage of existing reports and dashboards.
- Use existing row-level security.
- Offer access from anywhere in the world.
- Keep drill-through functionality.
- Provide a lost-cost implementation.
- Outsource implementation and ongoing maintenance.
In April 2011, the vendor began the development and by September 2011 completed the project. The iPad application enables the end users to access financial information through a dashboard, run basic reports, and drill through to detailed information. In addition, the controller’s office team can use the row-level security to limit access to confidential areas. The iPad application lets us combine two independent technology initiatives into a comprehensive business solution.
What’s Next?
Over the past year, the controller’s office has continued to train end users on the new tools and improve the reports and dashboards. Our vendor maintains the system, including implementing enhancements and troubleshooting system issues. In addition, the controller’s office has purchased a block of consulting time at a fixed price to implement new features into the system.
Our budget office used the consulting time to develop a data-extraction process from the database for the annual budgeting workbooks. The controller’s office used the consulting time to implement new grant dimensions in the grants OLAP cube for reporting. In addition, we are working with the vendor team to change the extraction from the Banner ODS tables to the ERP tables. This will greatly reduce the amount of time required for data extraction.
To ensure that we continue to meet the needs of departments and schools, our office conducts regular technology meetings with users to gather their new business requirements, which we then compile into an internal technology project list—a process that’s working for us. For example, when a grants and contracts user recently requested an additional report, we gave the user requirements to the vendor and within a week, the report was complete.
The controller’s office continues to evaluate the reporting requirements for the faculty and staff, as well as new technologies offered by Cognos and AnalyticsWare. Using both technologies will enable us to meet the reporting needs of the university now and in the future as new business tools become available.
BRIAN LAFFEY is associate vice president of finance and controller, Illinois Institute of Technology, Chicago.