Monday, March 23, 2009

Business Intelligence: A Case Study

Presentation: Business Intelligence: A Case Study
Presenters: Lynne Hamre and Bill Reichet, The College of St. Scholastica

-----------------------------

Very excited! Found the wireless! Not in the meeting rooms, but I found the wireless!
Also found the free coffee.
And met some people I work with who I have never actually seen before.
Because the only way I was going to meet them was at a conference out of town.
Figures.

-----------
Big education takeaway:

They have a monthly get together of all of the functional data administrators in a computer lab.

15 minute introduction of a particular feature of function of Banner.
Rest of time spent working on own stuff in Open Lab. Trainer and analyst available for questions.
People talking to each other and sharing during the Open Lab session.

Their take – this approach has been successful beyond their wildest dreams! Much less “duplicate development” of particular reports.
------------------------------

College of St. Scholastica – Duluth MN.
Liberal arts, private. Low endowment, tuition dependent.
Trying to create entrepreneurial culture in college.

1995 Banner Implementation
- Baseline Banner shop (no mods)
1998 – Started developing homegrown MS Access DataMarts
2003 – Banner HR Implementation
2004 – ODS/EDW Cognos

Katz-Tower and the Cloud
‘Institutions Performance Management System may become one of the defining elements of the enterprise”

Why Business Intelligence?
- Enrollment growth (3500 students)
- Increased Operational Complexity

How to link planning, budgeting, assessment (steps)
- Mission
- Vision
- Strategic Priorities
- Institutional Goals
- Departmental Action Plans -- >Budget
- Key performance indicators

BI system provides transactional data and aggregate data + strategic indicators.

Principles for success
1) Select the right tools
+ Had dozens of homegrown datamarts
+ Could not report historical trends
+Could not move beyond departmental reporting
+ Had to manipulate Banner data by managers
+ IT Maintenance overload
+ Inconsistent information
+ Time lag between request and receipt getting longer and no time for new development

- Buy vs.Build
+ IT dept 5 people (incl DBA /Banner admin)
+ Better to purchase system fully implemented with Banner and will keep up to date as the system upgraded.
+ ODS / EDW / Cognos

- ROI / VOI
+ was outside of budget cycle.
+ Needed to do major sell job on admin

- Align to Strategic Plan

2) Create right governance structure
- Historically – had Banner Implementation Group (representatives from each dept. Front line folks). Determined data needs based on individual departments.
- As org grew – folks not understand enterprise-wide implications.
- 5 campuses. 42 campus codes (because now a particular program rather than campus now). Multiple term structure. Department decisions not match reporting and strategic needs for org.
- Created new committee
+ VPAA / CFO / VPEM / CIO / IR. Enterprise wide decision makers. Strategic thinkers
+ Strategic focus
+ Establish data definitions to meet broadest set of organizational needs
+ Establish college wide reporting standards

3) Come to common understanding of critical data elements
+ Campus – Where is the physical location for this activity?
+ School
+ Program / Major
+ Student / Attribute / Level / Classification / traditional – non-traditional
+ Codes VERY important
+ All use common data elements

4) Transition from existing reporting structure
+ Involve current data stewards (the functional areas)
+ Each area has own data steward responsible for quality of information and security of system.
+ Functional areas responsible for own routine queries
+ Create training opportunities (BI Open Labs)
-- Once a month – 15 minute overview
-- Let them loose on the computers.
-- Folks could them talk to each other
-- This worked GREAT. They would create one that all would use. Rather than each creating own stuff
+ Divide labor
+ Set realistic deadlines and stick to them. This didn’t work so well. KILL the old data warehouse (tried fluffier language, but that didn’t work). Hard to separate functional user from old data warehouses. Current goal – end of ’09.
-- Generally more have confidence in the new system
-- Barrier now really logistical (not political)

BI Challenges
- Substantial upfront investment
- Staff training and time
- Departmental user / buy-in
+ End-users could see value in tools pretty immediately
- Transition creep
+ Of course longer than an 18 month project
+ Didn’t realize the scope or that it was bleeding edge at the time

BI Benefits
- Enhanced efficiency and productivity - by 2005
- Reliable and consistent data
- Integrated planning, budgeting and assessment – yay!

Setting Realistic Timelines
- We did not do a good job of identifying what we really needed to shut down and the implications of the shut-down.
- Learning as we go. (Good to see them admit this)

ODS – the transactional data moved over to a new data instance.
- One source of data. Everyone hits the same source.
- Department-level reporting and decision making.

Server Sizing – the tech stuff
- Production
+ 35 GB of data – On Banner since 1994, using all modules except Advancement, ~2000 FTE
+ 1.3 TB of disk space, 330 GB in use
+ 16 GB RAM
+ Red Hat Enterprise Linux ES v4 64-bbit
+ Oracle 10.2.0.3
+ Dell Poweredge 2950 w/ dual quad core Xeon processor
- Test box is VM Ware, 8GB RAM, 600 GB of disk space, otherwise same as Production

Server Performance (using ODS ver.8)
- Full load – 7 hrs (ODS ver 2. took 24 hours+) (after install and as needed if data problem develops)
- Refresh all – 7 minutes (ODS ver.3 took 45 minutes) (1am every day. Using night-job info.)
- Rebuild custom tables between 4am and 6am.
+ Leveraged heavily in custom tables
+ Built specifically for college’s needs.
+ Still a lot of custom tables. Thinking of starting a project to reduce the number
+ Built, honestly, for early buy in. Now turning into crutches.

Create custom tables
+ When data element not available in ODS,
+ Consolidate elements from multiple tables for performance institutional goals.
+ 5 important ones (out of 100+ custom tables)
-- Student Master - 1 stop for anything you want about students. Tons of fields, runs quick. 90% of what need. (Other masters pretty much the same concept)
-- Recruit Master
-- Applicant Master
-- Budget Master
-- Operating Statement

Security
- Novell LDAP for authentication. Cognos for groups and roles
- Pros
+ Easy setup and maintenance
+ Uses existing credentials
+ Poised for Identity Management
- Con – Does not accommodate other uses of ODS

Metadata table helps both tech and functional communicate.
- What each field does.

(I tuned out here since I have absolutely no control over the back-end or configuration).

No comments: