Data Warehousing for the SUNY System

1. Data Warehousing for the SUNY System AIRPO, Winter 2006 Maggie Moehringer maggie.moehringer@suny.edu

2. SUNY “Data Warehouse” • A collection of data repositories (files, tables), with data geared to different functional and data usage needs • Interrelated (or interrelate-able) at some level of data summarization and time slicing • Read only • May contain transactional detail but do not directly support transaction processing • Optimized for self service for analysts and knowledge workers who need to create or execute queries/inquiries

3. SUNY Information Environment Components • The information audience • The data itself • The data repositories • Access: Tools to get to the data • The “Plan”

4. Our Information Audiences • Indirect users: • Prospective students and parents • The interested public • Media • NYS Senate and Assembly • NYS executive/agencies (Governor, DoB, etc.) • SUNY Board • Direct Users: SUNY System and campus functional office and analytical/planning staff

5. Direct “Hands On” Audience for the Data Warehouse • System and campus functional offices • Administrative/Operational Data Usage: • Detailed, low level granular, current and historical, transactional; within a function. • System and campus analytical staff • Analytical / Planning Data Usage: • Longitudinal, comparative, cohort, statistical and projective purposes; cross functional; detailed, not transactional; stable time slice

6. Data: What We Have…and Don’t Have • “We” = SUNY analytical staff • Employees: • at State operated campuses…. • but not at community colleges • and not everyone who provides instruction. • Applicants/Applications: • for ASC participants, but not all applications… • and not non-participating campuses. • Student/applicant socio-economic and financial aid: • None.

7. Data: What We Have…and Don’t Have (cont’d) • Funding: • that flows through state accounts… • but not funding that flows through RF, CF or local campus accounts. • Enrollment: • as of the census date… • but not changes in student enrollment after that, • and not some populations that are funded • and not unfunded activity. • Instructional activity/cost/workload: • for the State Operated campuses… • but not for Community Colleges.

8. Data: Major Frustrations • Production information systems often do not include the complete information necessary to support management inquiries and decision making. • Knowledge workers are forced to bring together data from different sources, summary levels, and time slices, and must be very knowledgeable about data shortcomings. • “Yes, we kind of have that info, but…” • Hard to allow unfettered access, but we must figure out a way.

9. Data Repositories: Current Technology • Old legacy production systems • New Oracle relational versions of old legacy data • New Oracle star schema versions of old legacy data • New Oracle systems • Spreadsheets, summary data feeds, special compilations, etc.

10. Data Repositories: Future Technology • Oracle instances supporting transactional systems and functional operations • Oracle instances supporting reporting: • Relational data bases • Dimensional data bases

11. Future Repository Design: Getting our Staff There • 1999: Short information gathering project. • Technical staff: training for two people on data warehousing, dimensional modeling. • Training for two staff on Oracle Warehouse Builder. • 2001: One star in an area with good data (SDF Enrollment). • Then two more stars (ASC Applicants, State Employees). • Training for users and technical staff on a query tool (Oracle Discoverer). • Refinement of extract, transformation and load (ETL) procedures.

12. Data Repository Design: DW Expertise on Campuses • “Banner Reporting Initiative” Survey • Expertise deficit on campuses. • Ways to improve it: • Some training • Oracle tools • Using what we have • Collaborative assistance • Possible product acquisition.

13. Tools to Access Data • Major consideration: the security environment at System Administration • UserID/Password Secured • Web access is “portal” driven • Web clients for most users • Single sign-on • Distributed maintenance of identification/authorization information • Therefore, access to SUNY systems by client tools (Access, Cognos, etc.) with internal security that must be centrally maintained is an administrative issue • Access to SUNY systems by clients tools is a support issue.

14. Tools: the Possibilities • At the simplest level, web pages can display pre-formatted data (HTML, PDFs, etc.); not enough. • Custom Inquiries • Canned Queries • Distributed Datasets for static data • Query or analytical tool in local use with downloaded data • Direct query access.

15. Tools: Probabilities for Campus Access • Custom inquiries, developed in Cold Fusion or Java (e.g. current SMRT for Finance) • Developing “SMRT for Enrollment” • Can be smarter than a dumb query • Canned queries (Discoverer) • Optimized, parameter driven for flexibility • Distributed Reports and Datasets for static data • If it’s necessary, query access.

16. The Plan:The SMRT Environment • “SUNY Management Reporting Tool” • S-M-R-T was intended for use as a general acronym. • “The SMRT Portal” • “SMRT for Enrollment”, “SMRT for Human Resources”, “SMRT for Academic Programs”…..

17. What belongs in the SMRT inquiry environment? • Designed to address this problem: “I can’t allow them to have access to my data because they don’t understand the data, they don’t know how to ask the question, they might make a mistake.” • Guided, mistake-proof, supported by metadata, always inquiry only, and • An inquiry that’s useful for users who are not working in the specific business area, OR • An inquiry that’s useful for a broader audience than the specific business area user, and often • A higher level inquiry than the most granular level of detail, and often • Geared to users who are likely to want to see reporting out of multiple business areas OR • Users who will not be using the transactional and update capabilities of the business application.

18. Measures of Success for SMRTs • QUICKLY developed • East to change, enhance • Cover most of the need • Easy to use • Impossible to misinterpret the data.

19. SMRT Development Process • Input at the System level • Development of basic views • Review with campus interest groups • Enhancement and deployment • Provision of “gap filling” queries and reports. • Ongoing assessment and improvement with campus and system user groups.

20. DW/Reports User Interface Environment Fast Facts SUNY.edu Facts Data Mart (non-Web) Publicly Accessible Inquiries Employee Portal Business Area Apps SMRT Portal Data Policies & Procedures User SMRT/DW Documentation SMRT Inquiries Discoverer Viewer: Business area specific inquiries and output Legacy Reports (temporary) Interim Metadata Canned BA Query Output Metadata Canned DW Query Portal and Query Output UI Color Key: Common Facilities Business Applications DW facilities

21. Candidate SMRT Inquiries • Don’t wait for the perfect systems and DW; use what we have • Pockets of readiness: HR, Enrollment, Academic Programs • BUT serious data vacuums • AND questions about SUNY wide access • SMRT for Enrollment: Helen Ernst – Technical Lead • Of use to: • Budget analysts • Enrollment managers • Institutional researchers • Executive management • SUNY wide data • Requirements for the System office views defined • Requirement for campus views needed • Common features to all SMRTs: printer friendly version, Excel downloads, etc.

22. Other “SMRTs” • SMRT for Academic Programs • Enrollment, degrees granted • AND, through relationships, costs, staffing, ... • SMRT for Student Outcomes • SMRT for Human Resources • SMRT for Faculty • SMRT for Campus Profiles • SMRT for Applicant Profiles • SMRT for SUNY Allocations and Expenditures • etc.

23. Where We Are Now • Improving the repositories • Enrollment: filling gaps, adding detail, adding metrics • Degrees Granted • Academic programs • “SMRT for Enrollment”: 23 views • Preparing for campus demos and comments to perfect the tool. • Input groups: AIRPO, ABB • AIRPO sub committee?