Archive

Archive for December, 2009

Centralize Reporting

December 31, 2009 Leave a comment

For many years there was no central place to request reports at CSU, Chico. The campus has over 3000 staff members. When requests for data are made to random decentralized sources, miscommunication, chaos and disorganization reigned.

We were tasked to centralize reporting, which after investigating, realized meant a change in the culture of the organization in regards to gathering data.

The first task was to identify an enterprise reporting tool. The second task was to get all the developers on board. Without a mandate to use one tool, getting 75 developers to stop delivering data the way they are used to and use some new tool was not going to be easy. After using my best efforts to sell the concept and tool to the group, a small minority remained who refused to leave their homegrown systems and would not succumb to cheer leading.

So the responsibility of centralizing reporting without the authority to enforce on developers rested on our shoulders. After a few months of not being able to have the outlying developers to convert their ways we decided to take a different angle. The Solution: A One Stop Shop for data which would eliminate the need for all reports to live in one tool which pulls data from a report library. Developers would submit the reports they created, description of the report, and the system it is created and delivered through and the One Stop Shop would be the “portal” for the different systems.

The One Stop Data Shop was successfully created and deployed September 2008 and can be found at http://data.csuchico.edu

Programming Language: C#, ASP.NET, PL/SQL
Database: Oracle
Read more…

Categories: Projects

Finance General Ledger Mart

December 31, 2009 Leave a comment

Finance reporting data was initially created by the CSU Chancellor Office (CO) and was a flavor of Oracle’s Enterprise Performance Management (EPM) data warehouse developed for PeopleSoft 8.4. Chico State was the pilot campus for EPM, and since implementation the CO has abandoned the product and discontinued development.

In March 2009 Chico State was scheduled to upgrade to PeopleSoft 9.0. Due to table changes and change in processes for 9.0, the data mart would have to be updated. To complicate the situation the stand-alone version of the reporting tool the Finance department used was also being discontinued.

Due to both discontinuation of support for EPM and discontinuation of our reporting tool we decided to replace our entire finance reporting system with a home grown data mart and the campus’ enterprise reporting tool.

I was appointed lead on the data mart which was successfully implemented before the deadline and has since then been adopted by 4 of the 23 California State Universities supported by the CO. The data mart mostly follows Ralph Kimball’s model with conformed dimensions that are used in multiple marts.

Tools: SSRS for Reporting
Language: PL/SQL, SQL for table development
Database: Oracle

Read more…

Categories: Projects

Costing a Class

December 31, 2009 Leave a comment

Costing Classes

An age old question at universities is “How much does it cost to teach a class?” During these hard times of the economy, the answer to this question is becoming more important and should be taken into consideration when making the class schedule. I initiated a project to create a report to answer this question and took the lead. As simple as the question may seem, the rules revolving around classes, faculty, salaries, and how benefits are paid presented a challenge.

Challenges

  • Need to combine data from the three different PeopleSoft modules (Human Resources, Student Administration, and Finance) which does not necessarily relate fields as they are independent systems.
  • Work with both functional and technical staff from the different modules which entails different lingo, ideas and definitions of terms.

Tools: Microsft SQL Server Reporting Services, Cognos Decision Stream
Languages: SQL, VB script

Read more…

Categories: Projects

Using Oracle to Place a Google Voice Call

December 31, 2009 Leave a comment

Google Voice PL/SQL Package:

Project done in partnership with Steve Bealer.

Purpose: We needed a way to make a phone call from our Oracle database whenever there was a failure. We did not have access to a PBX to place the call. However, Google voice offers a service to create free phone calls through VOIP. After some investigation to figure out the process used to make a Google voice call we built a PL/SQL package to utilize this feature.

Programming Language: PL/SQL
Database: Oracle

Read more…