Institutional Reporting and Analysis Utilizing OLAP Technology and

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, i. Strategy Consulting

Mark Max Bio email: mmax@istrategyconsulting. com • B. S. Accounting & M. S. Business – University of Maryland • University of Maryland, Instructor • 20+ years Consulting, Corporate, Software Vendor Work Experience • Founded i. Strategy 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, i. Strategy Consulting

Higher Education Lifecycle Fact-based Decisions/Improved Results Information Chain Recruitin g Admissi ons Enrollme nt 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, i. Strategy Consulting Fund Raising

Typical Reporting Challenges Financials Housing/ Judicial Human Resources Student Admin Alumni Recruiting All Rights Reserved Ó 2003, i. Strategy 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, i. Strategy 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, i. Strategy 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, i. Strategy 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, i. Strategy 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, i. Strategy Consulting

Data Warehousing and Business Intelligence Architecture Data Sources Data Warehouse Departmental Data Marts OLAP Server Data Mart Financials/HR E Data Mart OLAP Tools E T L T Student Analytical Applications OLAP Data Mart Reporting & Analysis Enterprise Data Warehouse L Relational Query & Reporting Tools Data Mining Other ETL – Extraction, Transformation and Load All Rights Reserved Ó 2003, i. Strategy 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, i. Strategy 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, i. Strategy 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, i. Strategy 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, i. Strategy 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, i. Strategy 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, i. Strategy Consulting

i. Strategy’s Higher. Ed Analyzer. TM. . . 1 st Analytical Application for Higher. Ed 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, i. Strategy 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

Higher. Ed Analyzer Product Positioning Higher. Ed Analyzer Portal Personalized Dashboard Guided Analysis Interactive Reports Higher. Ed 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 Q 3) – API to integrate with Legacy apps or existing DW • Accelerated implementation process ERP: Legacy • Peoplesoft • SCT • Datatel Existing DW All Rights Reserved Ó 2003, i. Strategy Consulting – Install and configure – Validate data conversion and recurring data feed – Workshop for content customization – Train and deploy

Application Demonstration All Rights Reserved Ó 2003, i. Strategy 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, i. Strategy Consulting

Technology Architecture Windows 2000 Server Pro. Clarity Analytical Server Microsoft IIS Web Server Microsoft Analysis Services Microsoft SQL Server All Rights Reserved Ó 2003, i. Strategy 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, i. Strategy 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, i. Strategy Consulting