Transcript Document

Institutional Reporting and Analysis
Utilizing OLAP Technology and
Analytical Applications
March 17, 2003
Mark Max, Managing Partner
This work is the intellectual property of the author. Permission is granted for this material to be
shared for non-commercial, educational purposes, provided that this copyright statement appears on
the reproduced materials and notice is given that the copying is by permission of the author. To
disseminate otherwise or to republish requires written permission from the author.
All Rights Reserved 2003, iStrategy Consulting
Mark Max Bio
email: [email protected]
• B.S. Accounting & M.S. Business – University of
Maryland
• University of Maryland, Instructor
• 20+ years Consulting, Corporate, Software
Vendor Work Experience
• Founded iStrategy Consulting in 1999
– Consulting and software firm specializing in Business
Intelligence and Data Warehousing
– Principals have been working in BI for 15+ years
– Experience in BI/DW for higher education
– Launched new DW/Analytical Application for Higher
Education in 2003
Shift in Higher Education Towards
“Fact Based” Management
High Focus Areas
• Recruiting Effectiveness
• Enrollment Funnel/Admissions Yield
• Student Demographics
• Retention
• Course Optimization
• Outcomes Management
• Early Intervention
• Key Performance Indicators
• Resource Management
All Rights Reserved 2003, iStrategy Consulting
Higher Education Lifecycle
Fact-based Decisions/Improved Results
Information Chain
Recruiting
Admission
s
Enrollment
Retention
Program
Complet
ion
Institutional Information Users
Recruiting
Office of Planning
Admissions
Alumni/ Fund Raising
Registrar
Provost
Institutional Research
Deans
Financial Aid
Academic Affairs
All Rights Reserved 2003, iStrategy Consulting
Fund
Raising
Typical Reporting Challenges
Financials
Housing/
Judicial
Human
Resources
Student
Admin
Alumni
Recruiting
All Rights Reserved 2003, iStrategy Consulting
• Transactional systems don’t provide
sufficient reporting capabilities
• No ability for self service access to
information – users are totally dependent
upon others to produce information
• Time consuming, manually intensive
process to produce reports
• Different people produce reports with the
same information but have different
results
– What is the real answer?
– How do you know if reported information
is correct?
• Have to repeat the same time consuming
process each time you want a report
• No time available for analysis because of
the extensive time required to produce
information
Transaction System Reporting
Complexity
• Student Administration
application database structures
are very complex
• Reporting requires queries for
database extracts – need to
know SQL language
• Reporting results are subject to:
– a) users understanding of
database structure,
– b) “interpretation” of query
criteria, and
– c) proper SQL syntax.
• Its easy to get the wrong
answer!
• No easy way to combine data
across multiple systems and
database.
• Limited number of people who
know how to query databases
All Rights Reserved 2003, iStrategy Consulting
2002 Higher Education ERP Survey
• 39% of institutions surveyed have implemented
or are in the process of implementing a Data
Warehouse
• 37% of institutions surveyed plan to implement
a Data Warehouse within the next three years,
with almost 1/3 of the projects beginning in
2003
Source: The Promise and Performance of Enterprise Systems,
2002 ECARS Research Study by Dr. Robert Kvavik
(500 Institutions surveyed)
All Rights Reserved 2003, iStrategy Consulting
DW Success Rates
• Research shows that many DW projects
do not achieve targeted results!
• Typical approach that is prone to fail:
– Start by looking for application data to source
a DW
– Move as much transactional data as possible
into a “warehouse database”
– Purchase a relational reporting or query tool
– Send users to training
All Rights Reserved 2003, iStrategy Consulting
Information vs. Data
• Data – raw facts that have been collected,
processed, stored, but not organized to convey
meaning.
• Information – a collection of data organized in a
manner to be meaningful to a recipient.
• Knowledge – information combined with
understanding, experience, accumulated
learning, and expertise relevant to a problem,
decision, or process.
Big Difference between Data and Information
All Rights Reserved 2003, iStrategy Consulting
DW Casual User vs. Power User
• Different audiences with different:
–
–
–
–
–
Information needs
Analytical capabilities
Technical aptitudes and skills
Level of understanding of application data
Time constraints
• 80% – 90% of information consumers
are casual users
Need to consider both in technology decisions
All Rights Reserved 2003, iStrategy Consulting
Data Warehousing and
Business Intelligence Architecture
Data Sources
Data Warehouse
Departmental
Data Marts
Financials/HR
OLAP
Server
Data
Mart
Data
Mart
OLAP
OLAP
Data
Mart
Data
Mart
OLAP
OLAP
E
Reporting & Analysis
Analytical
Applications
OLAP
Tools
E T L
T
Student
Enterprise Data
Warehouse
L
Relational
Query &
Reporting
Tools
Data Mining
Other
ETL – Extraction, Transformation and Load
All Rights Reserved 2003, iStrategy Consulting
Critical Success Factors in Delivering Self
Service Information & Analytics
1. Address both Power Users and Casual Users
2. Focus on Information
• Incorporate a Dimensional Data Model
• Recognize the need for Data Transformation and
Derivation
3. Utilize a combination of relational and OLAP
technology (relational technology alone will
not meet the needs)
4. Target a quick high value success, instead of
trying to provide every bit of data that
someone could possibly request (80/20 rule)
5. Implement an Analytical Application instead of
deploying query tools
All Rights Reserved 2003, iStrategy Consulting
Confusing BI Product Space
• 20 to 40 vendors; many overlapping products that may
appear similar but are fundamentally different in capabilities
and architecture
• Reporting vs. Analytics – there’s a big difference!
• Relational vs. OLAP Technology
– Multidimensional Presentation vs. OLAP engine
– MOLAP vs. ROLAP vs. HOLAP
• Products/Vendors: Front-end only vs. Back-end only vs. Both
• Open vs. Proprietary platforms
• Web vs. Client Server
– HTML vs. Rich web client (JAVA, Active-X)
• Open component architecture vs. self contained products
– Portal integration
Conclusions
 There’s no magic product that does it all!
 Need to select BI technology based on user
community, analytical reporting needs and objectives
All Rights Reserved 2003, iStrategy Consulting
Research Findings by
The Data Warehouse Institute
“ ...most decision support software is gathering
dust on office bookshelves”
“Whether you build and/or buy, the key is to …
deliver a robust analytic application that
delivers the information and analysis that users
need.”
Wayne Eckerson, Director of Education and Research for
The Data Warehousing Institute (TDWI)
All Rights Reserved 2003, iStrategy Consulting
Why an Analytical Application?
(vs. Reporting Tools)
• Definition – domain-specific solution that enables users to
access, analyze, and act upon information in the context of
business or management processes [by TDWI]
• Casual Users – majority of information users (80 – 90 %) are
casual users who will have difficulty mastering a reporting tool.
An Analytical Application will be more effective and be more
highly utilized
• Hide Database Complexity – most reporting tools require the
user to understand the reporting database content and
relationships. An analytical application enables casual users to
get information without understanding the underlying database
and functionality of reporting tools
• Guided Analysis – an application framework provides the
opportunity to guide users through an embedded analytical
process and better leverage the metrics and analytical
capabilities inherent in the solution
• Personalization – provide users with the ability to personalize
their content and interface
• Custom Analytical Functionality – enables customized
application functionality to be integrated with reporting and
analysis (e.g., Student Peer Group Analysis)
All Rights Reserved 2003, iStrategy Consulting
Analytical Application for Higher Education
• Information Scope
– Serve a broad audience: institutional research, management
reporting, compliance reporting, operational analysis
– Span complete student lifecycle: admissions, enrollment,
course activity, graduation
– Address institutional objectives: recruiting effectiveness,
retention, student achievement, course curriculum, etc.
• Provide self service access to information:
– Intuitive and easy to use (the basics are simple)
– Minimal training required
– Easy to deploy
• Functionality:
–
–
–
–
–
–
Interactive standard reports and charts,
Guided Analysis,
Key Performance Indicators (KPIs),
Personalized Dashboard (KPIs and Charts)
Ad hoc analysis,
“Actionable” analytical tools (e.g., support early intervention
through student risk analysis, student peer group analysis)
All Rights Reserved 2003, iStrategy Consulting
Build vs. Buy
• Analytical Applications market will grow to
$6 Billion by 2005 - IDC
• Value Proposition:
–
–
–
–
Reduced Implementation Time
Reduced Cost
Reduced Risk
Expectations validated before implementation
• Would you consider building a student
registration system or a general ledger?
• If a packaged Data Warehouse and
Analytical Application for Higher Education
was available, would it make sense to
consider buying instead of building?
All Rights Reserved 2003, iStrategy Consulting
iStrategy’s HigherEd AnalyzerTM
... 1st Analytical Application for HigherEd
Information Delivery Engine
Analytical Application
Personal
Dashboard
Ad Hoc
Analysis
Personal
Reports
Key Perf.
Indicators
Guided
Analysis
Analytical
Modules
Download
Extracts
Compliance
Reports
Standard
Reports
Data Warehouse
Admissions
Class
Offering
Student
Term
Student
Class Enr.
All Rights Reserved 2003, iStrategy Consulting
Faculty
Term
Graduation
Information Consumers
Strategic
Planning
Compliance
Reporting
Institutional
Research
Academic
Affairs
Admissions
Office
Deans/
Assoc. Deans
Registrar’s
Office
Department
Chairs
Financial
Aid
Administrative
Departments
HigherEd Analyzer Product Positioning
HigherEd Analyzer Portal
Personalized
Dashboard
Guided
Analysis
Interactive
Reports
HigherEd Analyzer
Data Warehouse
ETL Modules
• A packaged analytical application for
Higher Education
– Open technology platform
– 80% to 95% out-of-the-box
– Framework to enhance data model
and content for specific needs
– Pre-built integration with leading ERP
vendors (SCT and Datatel by Q3)
– API to integrate with Legacy apps or
existing DW
• Accelerated implementation process
ERP:
Legacy
• Peoplesoft
• SCT
• Datatel
Existing
DW
All Rights Reserved 2003, iStrategy Consulting
– Install and configure
– Validate data conversion and
recurring data feed
– Workshop for content customization
– Train and deploy
Application Demonstration
All Rights Reserved 2003, iStrategy Consulting
Student Administration Information
Categories
1. Admissions
2. Student Demographics
3. Enrollment Trends
4. Retention
5. Class Offering and Utilization
6. Student Class Enrollment
7. Student Performance
8. Student Risk Analysis
9. Student Peer Group Analysis
10.Graduation
11.Faculty Information
All Rights Reserved 2003, iStrategy Consulting
Technology Architecture
Windows 2000 Server
ProClarity Analytical Server
Microsoft IIS Web Server
Microsoft Analysis Services
Microsoft SQL Server
All Rights Reserved 2003, iStrategy Consulting
Data Warehouse Architecture
DW Build Process
1. Bulk load data from
transaction system into
temporary staging tables
(most recent n terms)
2. Perform edit, data
derivation and relational
DW build transformations
3. Build aggregate OLAP
cubes
Microsoft SQL Server Data Warehouse
Microsoft Analysis Server (OLAP) Cubes
3
Data Transformation Services (DTS)
Relational Warehouse
Dimensions/Attributes Star Schema Fact Tables
Edit & Transformation
Student Admin
Application
2
Data Transformation Services (DTS)
Staging Tables
Operational
Databases
Flat Files
All Rights Reserved 2003, iStrategy Consulting
Bulk Load Process
1
Data Transformation Services (DTS)
Why OLAP Technology?
• Multi-dimensional presentation is the
natural orientation for business
information and analysis
– Intuitive and easy to use
– Hides user from underlying relational data
model
• OLAP Technology is very fast
– Most reports run within 1-3 seconds
– Speed advantage substantial in highly
aggregated reports such as multi-year
trends
– Without OLAP, the burden is on the
developer to build the aggregation
• Enables calculations that are impractical
using relational technology
– e.g., moving averages, prior period %
change
• Produces consistent information
– Pre-calculated results
– Not subject to unexpected SQL query
behavior
All Rights Reserved 2003, iStrategy Consulting