University of San Diego California Higher Education User Group - - PowerPoint PPT Presentation

ITS

• California Higher Education
• User Group Presentation
• Nov-2014

University of San Diego

Meeting Agenda

• Business Intelligence and Operational Analytics Project Team
• Operational Analytics Architectural Overview
• Operational Analytics Discussion
• Security / Data Custodians
• Project Lifecycle
• Part II – Technical Presentation
• Q&A

BI & Operational Analytics Project Team:

• Avi Badwal – Senior Director ERP Technologies
• Satish Attili – Business Intelligence and Analytics Team

Lead

• Kris Haskell – Senior Business Intelligence Analyst
• ERPT Business Matter Experts: Managers and Technical

Resources

• Designated USD Functional and/or Academic Area

Business Matter Experts

• The individual(s) that will engage the operational analytics

to make actionable decisions and observations within their areas of responsibility

Technical Functional Users

Operational Analytic Architecture Overview

Presentation Layer

Metadata Layer

Data Layer

Data Source Data Extraction Data Transformation Data Presentation

Cognos Tools and Technologies

ODS + Report Views EDW + Star Schemas Oracle Banner SIS Transactional + Oracle ODS >EDW + Third Party Data Integration + Third Party Transactional +

Operational Analytic Architecture Overview (continued):

The EDW project implementation team (functional areas and ITS/EDW team) defined snapshot star ‘Events’ – events define and capture data at predetermined points in time during an academic period and academic year (e.g. 10 Weeks Before Class, Census, Last Day of Class, Final Period). Ellucian architected EDW using ‘star schemas’, which surround a ‘fact table’ containing interesting, often numeric data (e.g. # enrolled students) specific to

• ne analytic area with associated referential ‘dimension tables’ that provide

context to the facts (e.g. By College, Department, Program, Course) The Star schemas design, in conjunction with the ‘time dimension’, provide the

• pportunity to look across ‘like events’ (i.e. same relative point in time each academic

period and year) to analyze trends.

Architecture Overview: EDW Student Stars

• EDW Academic Program Course

Dimensions: Time; Academic Program; Concentration; Course: Demographic; Enrollment Status; Instructor; Major; Minor; registration Facts: Student ID; Course Reference Number; Curriculum Priority Number; Age; Age Range; Major (Group, Order, Count, Description; Minor (Group Order, Count, Description); Concentration (Group, Order, Count, Descriptions, Major Group, Order, Count, Description)

• EDW Course Registration

Dimensions: Time; Academic Study; Course: Demographic; Enrollment Status; Instructor; Registration, Student Facts: Student ID; Course Reference Number; Age; Age Range; Credits Generated; Credits Attempted; Credits Earned; GPA Credits; Quality Points; Credits Passed

• EDW Enrollment

Dimensions: Time; Academic Study; Demographic; Enrollment Status; Student Facts: Student ID; Age; Age Range; Total Credits Generated; Total Billing; Total Contact Hours; FTE Numerator; FTE Denominator; Tuition Charges; Financial Aid Amount; Total CEU, Total CEU Billing; Academic Period (Credits Attempted; Credits Earned, GPA credits; Quality Credits; Credits Passed); Student Level (Credits Attempted; Credits Earned, GPA credits; Quality Credits; Credits Passed); Institution Level (Credits Attempted; Credits Earned, GPA credits; Quality Credits; Credits Passed

• EDW Graduation Completion

Dimensions: Time; Academic Study; Demographic; Graduation; Student Facts: Student ID; Outcome Number, Age; Age Range; Active Academic Periods; Credits Attempted; Credits Earned; GPA Credits; Quality Points; Credits Passed)

Operational Analytics Security and Data Custodians:

• Cognos Groups and Roles are being defined to restrict access to folders,

EDW Cognos ‘packages’, and underlying reports and dashboard components.

• Report Studio analytic applications are developed specifically for each

college or school (or operational area) and contain embedded ‘parameter filters’ that restrict the queries to analysis appropriate to that location.

• In a number of cases, Report Studio analytics applications will be

developed specifically for the recipient of the analysis – this could be for a department chair that will only have access to his/her dashboard specifically developed for that department.

• Cognos also supports ‘session parameters’ where Cognos user definable

session attributes determine, preset, and restrict drop-down access to applicable parameters.

• Data Custodian ‘authorization’ is still required before an Operational Analytic

project can be launched – the data custodian responsible for an ODS package will be responsible for the corresponding EDW package.

• Operational Analysis is NOT ‘Official IRP Data’ and should not be

released to external sources. EDW is sourced directly from ODS and is not cleansed data.

Banner

Student Financial Services Financial Aid Registrar Admissions

Operational Analytics Project Lifecycle:

• Kick-off meeting to review presentation and EDW demo
• Requirement Gathering to identify and discuss:
• Key Performance Indicators (KPI)
• Analytic Requirements/Business Use-Cases
• Determine Best-Candidate EDW Baseline Star(s)
• Identify Project Team Members
• Development Phases: Report Studio analytics; Workspaces

and Dashboards – additional meetings / conversations to refine scope and resolve outstanding questions/issues

• Review and Hand-Off Analytics to recipient for user testing:

support user testing, modifications and/or enhancements

• f deliverables
• Incremental Development: iterate between development,

hand-off, and refinement phases until requirements have been met

• Embrace ‘Agile BI’ approach and ‘initial’ Data Governance,

standardizing/defining titles, sub-titles, KPIs, labels, etc.

Functional Demo

Below appears on the ‘landing’ page of the College of Arts and Sciences EDW Academic Program Course Subject Analysis and states the objectives of our initial proof-of-concepts:

University of San Diego

Part II – Technical Review –

EDW and IBM Cognos Analytics: Report Studio>Workspace>Dashboard

IBM Cognos Report Studio Analytics

• Initially began development of Report Studio analytics with all charts and

graphs envisioned/mined (50+?) from a single EDW (relational) Star

• Best Practice now: Design Report Studio reports with limited/scoped KPI or

KPI category to reduce impact on any changes to underlying analytics on workspace updates (Admission Applications Completed, Gender, Ethnicity = 18 charts); scalability; and maintain focus

• ‘Prompt Page’ with ‘named parameters’ provide ‘Listen for Widget Events’

(Workspace) capabilities for a flexible and navigable analytic user experience

• Design consideration: can develop a consolidated prompt page in one area
• f analysis containing ‘named’ prompts referenced by charts in other Report

Studio analytics – this can provide ‘drill-thru’ capabilities (e.g. drill into ‘Age Range’; ‘GPA range’)

• Charts Titles, Sub-Titles and Footers: use reference data item values,

calculated columns or parameter values to provide an opportunity for reusing underlying analytics in a different area (i.e. department)

IBM Cognos Report Studio Analytics

• Initially developed analytics exploring relative size or proportion using Pie

Charts

• Best Practice now:100% Stacked Column charts
• A standard practice for us is to provide KPIs by both academic year and

academic period within academic year (2 sets of same analysis) – as is the case in all aggregation strategies, counts differ because unique student PIDM’s appear

• nce per year, but more than once a year in each academic period the student is

enrolled

• Assign meaningful names to each chart/graph for easy identification when

constructing the Workspace (e.g. CmplAppEthnAcadYrCht)

IBM Cognos Workspace

• Ask IBM: Workspace is a report consumption environment that provides an

integrated Business Intelligence experience for business users

• Global Area now available for sharing prompt page parameters across one or

more Workspace pages (i.e. sub-tabs). Can toggle between expand (i.e. new prompt selections) and collapse (display full page)

• IBM Cognos Content and Toolbox pane (expand/collapse) for selection of

components available for inclusion on the Workspace (e.g. Report Studio, Analysis Studio) – drag and drop on Workspace page

• ‘New Tab’ is a new feature introduced in Cognos 10.2.1 used to create additional

‘pages’ to better organize/segment analysis – can be reordered – components share ‘named parameters’ in Global Area

• ‘Share Workspace’ allows you to set features and copy the URL link to

provide to other users (we’ll use this in another kind of ‘page’ when building our demo dashboard)

IBM Dashboard

• In our Cognos environment, dashboards are organized around one or more

‘pages’ presenting relevant analytic areas of interest (workspaces) based on specific business use-cases.

• A Cognos ‘Page’ can be configured as an HTML Viewer containing the ‘shared

workspace link (i.e. URL)

• A ‘collection’ of pages can be stored and ordered in a folder structure that can

be referenced in another Cognos ‘Page’ configured as a ‘multi-page’ container

• The Cognos ‘Page’ configured as the multi-page container with the folder path

to the ‘collection ‘of pages, can be added as a ‘Portal Tab’ to the requestor of relevant analytic areas of interest – and can then be referred to as a ‘dashboard’

Q & A

Q & A