On March 17, 2009 our new dimensionally-modeled Financials Warehouse tables went into production. These tables are modeled after the Reporting Solutions tables whose development was spearheaded by Cal Poly, San Luis Obispo. The Chancellor’s Office is now supporting a basic set of flat reporting tables that build nightly in the PeopleSoft production instance via an SQR. Campuses can pull those tables into a warehouse environment if desired and/or use them through PS Query or nVision.
Caly Poly, SLO went on to develop dimensionally modeled tables on top of the Reporting Solutions tables. At Chico we decided to build dimensionally modeled tables directly from PeopleSoft tables to support analytics and to make available a set of flattened reporting tables built on top of our dimensionally modeled tables. Our Warehouse Architect, Russell Lobban, crafted our new dimensionally modeled tables basing them on the SLO model.
Our Chico Database Administrators (DBAs) have had experience with a variety of ETL (extract-transform-load) tools including Oracle Warehouse Builder, Ascential, and Cognos and for this project also took a look at the Microsoft ETL tool, SSIS. We decided to go with PL/SQL--an inexpensive tool (many of these ETL tools cost over $100,000) that is widely used among developers and DBAs and as a result does not require any additional tool training to use for ETL. Less expensive, less complicated, less likely to give us the kind of issues we’ve had in the past with other ETL tools. We like the idea of spending less energy supporting the backend of warehouse and more energy building reports and products that get data into the hands of our users.
We believe that keeping the warehouse process straightforward, manageable and cost-effective is the way to go. We employed that same strategy with a reporting solution designed to meet 80+ percent of our basic Financials (and other) reporting needs. We chose the Microsoft Reporting Solutions tools which we renamed INSIGHT. The tool was quick to deploy, a developer can be up and running after an hour-long training, and users can run reports with very minimal or no training. We do provide training, guides, and a training video but generally 15 minutes of hands-on is all it takes.
Our next step in Financials Reporting is an ad hoc tool running against a cube for our Financials power users. This is in development at the moment and we’ll have more news on its progress and use in the near future.