SAS Business Intelligence

Introduction to

Introduction to

SAS Business Intelligence

Reporting at UVic

Reporting at UVic

Presented by Cheryl Sivertson

Institutional Planning & Analysis Institutional Planning & Analysis, University of Victoria, BC

(250) 721-8030 [email protected]

Topics

I tit ti l Pl i & A l i l t UVi • Institutional Planning & Analysis: our role at UVic • Kinds of data/analysis/reports we produce

• UVic’s project NOVA

• Enrolment Management Reportingg p g • SAS Business Intelligence Suite

• Historical reporting from ISIS (mainframe) • Phase 1 approach to BI Reporting

• SAS datasets • SAS datasets • SAS cubes

• SAS information maps

• SAS Web Report Studio (WRS) reports

• SAS Portal and three UVic reports LIVE DEMO (if internet available)

• Current Accomplishments, Next Objectives • Questions?

Institutional Planning & Analysis

The primary role of our office is to provide information The primary role of our office is to provide information,

research/statistical support, and other analytical services for decision making and policy planning at the University of Victoria. We are

involved in a number of projects, including:

• accountability reporting: performance measures, service plans, and service plan reports • support to strategic planning

• enrolment projections and enrolment management

• enrolment analysis, monitoring and internal reporting • faculty analysis and internal reporting

• applicant, student and graduate surveys

• external statistical reporting to government agencies, Maclean's, etc

• classroom laboratory and other space analysis projection and performance indicatorsclassroom, laboratory and other space analysis, projection and performance indicators • economic impact of UVic on the community

UVic’s project NOVA

Project Nova was an initiative to replace the university’s

• chose higher education specific product SCT Banner

Project Nova was an initiative to replace the university s

technologically-dated and stand-alone systems with a state-of-the-art information technology infrastructure:

• chose higher education specific product SCT Banner

• integrated Student, Human Resources, Finance/Accounting, Alumni , and Facilities Management components

• Oracle-based

The change in technology infrastructure required, among other

things the replacement of all existing mainframe reports as our IBM things, the replacement of all existing mainframe reports, as our IBM 3270 was set to be decommissioned on March 31, 2009.

Enrolment Management Reporting

Enrolment Management reports help the university monitor its Enrolment Management reports help the university monitor its enrolment goals and statutory accountability reporting to the government.

Since the Banner suite is not well-suited for report delivery, the reporting strategy for Project Nova articulated the need early on to acquire a comprehensive set of tools that would complement the transactional system and allow for:

transactional system and allow for:

• web-based custom reporting,

• ad-hoc querying,

• point-in-time reporting, • time-series reporting and time series reporting, and • statistical analysis.

We decided to leverage our existing investment in SAS by purchasing the SAS Business Intelligence (BI) Suite

SAS Business Intelligence Suite

Aspects of the Business Intelligence Suite we were most interested in Aspects of the Business Intelligence Suite we were most interested in at the time of purchase:

Server: Allows business metadata management. Create and centrally store native SAS datasets, as well

as interface with a wide range of disparate information systems on campus.

OLAP Server/Cubes: Create On-Line Analytical Processing cubes that pre-summarize data for fast

retrieval and analysis.

Information Map Studio: Ability to create an interface to disparate information sources that users can

then use to access data or create reports via SAS Web Report Studio or MS Office then use to access data or create reports via SAS Web Report Studio or MS Office.

Web Report Studio: Provides web-based report building, distribution, and advanced data exploration using

traditional tables and/or charts.

Portal: Serve up reports via an interactive web interface.

Portal: Serve up reports via an interactive web interface.

Enterprise Guide: Access disparate data sources via SAS Server. Query, manipulate and analyze local and

networked data sources via the SAS Server’s Metadata. Potential for automated process and report creation.

MS Office Integration: Directly access SAS datasets, SAS stored processes, and SAS Information Maps

Historical Reporting from ISIS

Tabular manual _web

post ISIS -Adabas Reports in Excel & PDF post Course R Populations record set Run ClassPops Job

automatic manual _Access import

Phase 1 Approach to BI Reporting

SAS SAS OLAP SAS Web Report Studio and Portal: Course Populations WRS Report ETL Banner to Oracle tables: Course Populations records ETL Oracle to SAS dataset: Course Populations dataset auto SAS dataset to OLAP Cube: Course Populations manual now auto later SAS OLAP Cube to Information Map: Course Populations auto auto ETL Banner WRS Report records ETL Oracle dataset SAS dataset to Populations cube SAS OLAP C be to Populations IM SAS Web Report Banner Student OR historical data to Oracle tables: Student Program records to SAS dataset: Student Program dataset auto dataset to OLAP Cube: Student Program cube manual now auto later Cube to Information Map: Student Program IM p Studio and Portal: Student Program WRS Report auto auto auto data ETL Banner to Oracle ETL Oracle to SAS cube SAS dataset to OLAP IM SAS OLAP Cube to Information SAS Web Report Studio and Portal: tables: Student Registration records dataset: Student Registration dataset auto OLAP Cube: Student Registration cube manual now auto later Information Map: Student Registration IM Portal: Student Registration WRS Report auto auto

Phase 1 Approach to BI Reporting

table: Course Populations records Transform data in Oracle Table(s): Course Populations data tables auto Oracle View: V_SECTION_CLASSPOPS view auto manual now auto later Banner Student

Extract Banner data to Oracle table: Student Program records Transform data in Oracle Table(s): Student Program data tables

auto _{V_STUDENT_PROGRAM} Oracle View: view auto auto manual now auto later DFAD OR

Extract Banner data to Oracle Transform data in _{Oracle View:}

(historical data) table: Student Registration records Oracle Table(s): Student Registration data tables auto Oracle View: V_STUDENT_REGISTRATI ON view auto manual now auto later

Phase 1 Approach to BI Reporting

Studio and Portal: Course Populations WRS Report

Oracle View to SAS dataset: Course Populations SAS dataset manual now auto later SAS dataset to OLAP Cube: Course Populations OLAP cube manual now auto later

SAS OLAP Cube to Information Map:

Course Populations

Information Map

auto auto

p

SAS Web Report St di d

Oracle View to SAS dataset: Student Program SAS dataset manual now auto later SAS dataset to OLAP Cube: Student Program OLAP cube manual now auto later

SAS OLAP Cube to Information Map: Student Program Information Map Studio and Portal: Student Program WRS Report auto auto

Oracle View to SAS SAS dataset to SAS OLAP Cube to

SAS Web Report Studio and Portal: dataset: Student Registration SAS dataset manual now auto later OLAP Cube: Student Registration OLAP cube manual now auto later Information Map: Student Registration Information Map Student Registration WRS Report auto auto

SAS Datasets

Takes record level data from Oracle views and imports into SAS datasets stored on the SAS Server ETL Banner records ETL Oracle dataset Server. Course Populations

Course section registration data.

Cardinality is one record per section per

Banner Student to Oracle tables: Student Program records to SAS dataset: Student Program dataset manual now auto later auto academic term. Student Program

Student’s program sought along with

demographic data Cardinality is one record

ETL Banner to Oracle

ETL Oracle to SAS

demographic data. Cardinality is one record per student per academic term.

Student Registration

Students course registration data combined ith t d t h t i ti d t C di lit tables: Student Registration records dataset: Student Registration dataset manual now auto later

with student characteristic data. Cardinality is one record per student per academic term per course section.

SAS On-line Analytical Processing (OLAP) Cubes

Takes all or a subset of fields from the SAS datasets and pre-summarizes the data for faster querying, analysis and reporting. ETL Oracle dataset SAS dataset to Populations cube p g Cube Measures

Items you want to apply mathematical operations to: • sum headcounts, FTEs

to SAS dataset: Student Program dataset dataset to OLAP Cube: Student Program cube manual now auto later

sum headcounts, FTEs • % headcounts, FTEs • count unique students • etc. ETL Oracle to SAS cube SAS dataset to OLAP Cube Dimensions

Ways to group data by: • age • gender dataset: Student Registration dataset OLAP Cube: Student Registration cube manual now auto later gender

• program year level • degree sought • etc.

SAS Information Maps

SAS SAS OLAP _{T k SAS d t t SAS OLAP b d} SAS dataset to OLAP Cube: Course Populations SAS OLAP Cube to Information Map: Course Populations auto

Takes SAS dataset or SAS OLAP cube and allows for the creation of a user friendly interface to the dataset or cube. Can use as many or as few items from the data

SAS dataset to Populations cube SAS OLAP C be to Populations IM y source as is appropriate. Course Populations dataset to OLAP Cube: Student Program cube Cube to Information Map: Student Program IM auto

All fields from the corresponding SAS dataset are used.

Student Program

Nearly all fields from the corresponding SAS dataset are used. Fields that contain highly sensitive

cube SAS dataset to OLAP IM SAS OLAP Cube to Information

are used. Fields that contain highly sensitive information are not included: Student Number, minority status, aboriginal status, etc.

Student Registration All fi ld f th di SAS d t t d OLAP Cube: Student Registration cube Information Map: Student Registration IM auto

SAS Information Map Example: Student Program

Cube Measures are items you want to apply mathematical operations to:

measures

to:

• sum headcounts, FTEs

• % headcounts, FTEs

• count unique students

• etc.etc.

Cube Dimensions are ways to group data by:

dimensions

group data by:

• age

• gender

• program year level

• degree soughtdegree sought • etc.

SAS Web Report Studio Reports

SAS OLAP SAS Web _{M k f SAS i f ti hi h} Report Studio and Portal: Course Populations WRS Report SAS OLAP Cube to Information Map: Course Populations auto

Makes use of SAS information maps, which reference OLAP cube dimensions and

measures. WRS Report SAS OLAP C be to Populations IM SAS Web Report Cube to Information Map: Student Program IM p Studio and Portal: Student Program WRS Report auto IM SAS OLAP Cube to Information SAS Web Report Studio and Portal: Information Map: Student Registration IM Portal: Student Registration WRS Report auto

SAS Portal & Reports

Of Note

Challenges: Challenges:

• Preparation of data took the most time. Once data is prepped, can use virtually any reporting tool

• Time investment required to learn new tools and construct reporting data warehouse infrastructure (Oracle-based, in our case)( , )

• Limit to institutional resources (people, time, money) required adjustment of project scope

Advantages: Advantages:

• Constructed institutional infrastructure for reporting data warehouse

• Once datasets and/or cubes are created, multiple reports can utilize same dataset/cube (to facilitate different audiences or levels of access to data)

• When new data is available and loaded into datasets/cubes, all reports associated / , p with a specific dataset or cube are automatically updated

• SAS server-based licensing means no additional cost incurred to expand user base • SAS Metadata Server can act as a hub to link disparate information systems, while presenting user with common interface

• SAS Portal provides common interface for data/report retrieval (no software • SAS Portal provides common interface for data/report retrieval (no software required by report consumers).

• SAS Web Report Studio provides developers with a common interface to develop reports (no desktop software installation required).

Current Accomplishments, Next Objectives

Phase 1 Accomplishments (SAS Development environment) Phase 1 Accomplishments (SAS Development environment)

UVic’s SAS reporting environment infrastructure enables us to extract student data from Banner for numerous snapshot dates, including five key reporting dates: August 1, Sep first day of fall term, Oct 1st_{, Nov}

1st _{and March 1}st _{in order to:}

• meet our statutory accountability reporting to government;

• internal enrolment monitoring and reporting.

• reporting system supports monthly/weekly Banner extracts;p g y pp y y • loading additional historical snapshot data;

• rolling the reporting system out to a wider UVic audience;

• training staff to use and customize the three master enrolment reports.

Phase 2 Objectives (SAS Production environment)

The following projects have been outlined or are underway:

• Roll out production version of our Reporting System to a wider audience

(no additional costs per user, as we have server-based licensing) (no additional costs per user, as we have server based licensing)

• Student Lifecycle dataset/cube/web reports;

• course experience survey results;

Questions?

Cheryl Sivertson

SAS Reporting – Project Manager Analyst / Statistician

Institutional Planning & Analysis

Mike Motek

SAS Reporting – Project Manager Team Lead

Enterprise Applications UVic Online Institutional Planning & Analysis

University of Victoria

(250) 721-8030 [email protected]

Enterprise Applications - UVic Online

University of Victoria

(250) 721-7661 [email protected]

Download a copy of this presentation:

Use the following slides if

Use the following slides if

LIVE demo not possible

SAS Portal – Log In

SAS Portal – Homepage

SAS Portal

SAS Portal – Section ClassPops

SAS Portal – Student Registration

SAS Portal – Student Program

SAS Portal – Student Program

Expand

SAS Portal – Student Program

Drill Down

SAS Portal – Student Program

Filter and Rank

SAS Portal – Student Program

Filter and Rank

SAS Portal – Student Program

Filter and Rank

Select snapshot dates we want to show.

SAS Portal – Student Program

Filter and Rank

Show enrolment data as we progress in time.

SAS Portal – Student Program Data Gizmo We want to show “gender” as an

SAS Portal – Student Program Data Gizmo “Gender” attribute selected.

SAS Portal – Student Program

Data Gizmo

“Gender” attribute added to report and appears as a row item.

SAS Portal – Student Program

Export

We are happy with the report and now want to export it to Excel.

SAS Portal – Student Program Export Saves or opens a .zip file.

SAS Portal – Student Program Export Report exported and opened in MS Excel.