architecture, administration and security ... groups...cricos provider code 00301j architecture,...
TRANSCRIPT
CRICOS Provider Code 00301J
ARCHITECTURE, ADMINISTRATION AND SECURITY CONSIDERATIONS IN A SAS BASED PROJECT
Western Australian SAS User Professionals (WASUP) May 2017
Curtin University is ranked in the top two per cent
of universities worldwide in the prestigious
Academic Ranking of World Universities 2016
The Assessment Quality Process is integral to
Curtin’s assessment quality (AQ) assurance process
as outlined in the Assessment and Student
Progression Manual.
The AQP unit survey is conducted twice per year
and the report constructed from each unit's survey
data helps to identify AQ and policy compliance
issues to ensure consistent and fair assessment
practices at Curtin.
ASSESSMENT QUALITY PROCESS (AQP)
Background
o Data processing
o Input data: 3 surveys + supporting data
o ETL: calculate measures, conform and load to Data Warehouse
o ETL: run daily during survey period (2 months, twice a year)o Output: 3 VA Reports
o Faculty: Available to anyone; Aggregated data
o School: Available to AQ panel members; Individual unit details of panel member’s school
o Unit Coordinator: Available to Unit Coordinator and AQ panel member; Units they were the UC of, or belong to the school they are a panel member of
o Project Lifecycle
o Prototype of ETL (SAS Enterprise Guide)
o Prototype and beta test reports (SAS Visual Analytics)
o Design, build, verify and schedule ETL (SAS Data Integration Studio & LSF Platform Manager)
o Publish VA reports (SAS Visual Analytics)
o Monitor and improve system performance and AQ process
ASSESSMENT QUALITY PROCESS (AQP)
Project overview (Requirements, Design & Lifecycle)
o Four main workspace servers
o SASDI - heavy workload overnight
o SASBI – most workload during office hours
o SASEG – unpredictable workload
o SASOLAP – minimum workload
o Infrastructure is provided by Curtin IT Services (CITS)
o Virtual Machines with low maximum specifications (2–4 core, 2-8GB RAM)
o Unable to support specifications required for SAS Visual Analytics
SAS ARCHITECTURE AT CURTIN
Curtin Architecture history and constraints
o Two SAS Environments:
o SAS Visual Analytics environment (VA):
o 1x SAS server (Metadata, Workspace, Web & Web Services)
o 5x LASR & HADOOP server
o Total 6x AWS EC2 r3.8xlarge instances (32vcpu, 244GB RAM)
o Total LASR memory available: 1.2TB
o SAS 9.4 Enterprise Data Warehouse & Business Intelligence environment (EDW/EBI)
o 5 x Compute Servers (4vcpu, 32GB RAM, in a SAS Grid)
o 1 x Metadata server
o 2 x Mid tier servers
o 2 x Web server
o 1 x Environment Monitoring Server
o 1x PC Files Server
SAS ARCHITECTURE AT CURTIN
Current Curtin Architecture
SAS ARCHITECTURE AT CURTIN
Benefits of SAS Grid
Allows us to have one SAS
workspace server (SASAPP)
distributed across 5 physical
servers
Whichever server is least busy
will be selected by the grid
controller to run the job
SAS ARCHITECTURE AT CURTIN
Benefits of SAS Environment Manager
SAS ARCHITECTURE AT CURTIN
Benefits of SAS Grid Manager (SAS Environment Manager Add-in)
SAS ARCHITECTURE AT CURTIN
Benefits of SAS Grid Manager – High Availability Settings
SAS ARCHITECTURE AT CURTIN
Benefits of SAS Grid Manager – LSF Configuration (Hosts & Queues)
SAS ARCHITECTURE AT CURTIN
Benefits of SAS Grid Manager – Queue Priority, Hosts, Limits & Dispatch windows
o Data processing
o Input data: 3 surveys + supporting data
o ETL: calculate measures, conform and load to Data Warehouse
o ETL: run daily during survey period (2 months, twice a year)
o Output: 3 VA Reports
o Faculty: Available to anyone; Aggregated data
o School: Available to AQ panel members; Individual unit details of panel member’s school
o Unit Coordinator: Available to Unit Coordinator and AQ panel member; Units they were the UC of, or belong to the school they are a panel member of
o Project Lifecycle
o Prototype of ETL (SAS Enterprise Guide)
o Prototype and beta test reports (SAS Visual Analytics)
o Design, build, verify and schedule ETL (SAS Data Integration Studio & LSF Platform Manager)
o Publish VA reports (SAS Visual Analytics)
o Monitor and improve system performance and AQ process
ASSESSMENT QUALITY PROCESS (AQP)
Recap
o Metadata (Users & Groups)
o Daily maintenance (add/change/remove) is performed in the SAS EDW/EBI Environment
o Data
o Data Warehouse is hosted in Curtin’s (on-site) Oracle DB
MULTIPLE SAS ENVIRONMENTS
Synchronising metadata and data between SAS environments
Using scheduled (hourly) DIS Jobs in the EDW environment:
o Extract Users & Groups metadata using the %MDU macros
o Compress output
o Upload compressed output to VA environment
15 minutes after the hour in VA environment
o De-compress output
o Import Users & groups metadata using the %MDU macros
MULTIPLE SAS ENVIRONMENTS
Synchronising metadata between SAS environments
Using scheduled (daily) DIS Jobs in the EDW environment:
o Extract Data from Oracle Data Warehouse
o Using the ‘Load data into LASR Server’ transform, send data directly into the VA environment via
appropriate SAS/Connect
MULTIPLE SAS ENVIRONMENTS
(Ideally) Synchronising data between SAS environments
Using scheduled (daily) DIS Jobs in the EDW environment:
o Extract data from Oracle Data Warehouse
o Compress output
o Upload compressed output to VA environment
VA environment:
o De-compress output
o Perform ‘autoload’ of data into LASR (built-in, configurable VA scripts)
MULTIPLE SAS ENVIRONMENTS
Synchronising data between SAS environments
o Data processing
o Input data: 3 surveys + supporting data
o ETL: calculate measures, conform and load to Data Warehouse
o ETL: run daily during survey period (2 months, twice a year)o Output: 3 VA Reports
o Faculty: Available to anyone; Aggregated data
o School: Available to AQ panel members; Individual unit details of panel member’s school
o Unit Coordinator: Available to Unit Coordinator and AQ panel member; Units they were the UC of, or belong to the school they are a panel member of
o Project Lifecycle
o Prototype of ETL (SAS Enterprise Guide)
o Prototype and beta test reports (SAS Visual Analytics)
o Design, build, verify and schedule ETL (SAS Data Integration Studio & LSF Platform Manager)
o Publish VA reports (SAS Visual Analytics)
o Monitor and improve system performance and AQ process
ASSESSMENT QUALITY PROCESS (AQP)
Recap
o Unit Level Report
o Report shows an individual unit’s progress towards meeting the standards
o For Unit Coordinators & AQ panel members
o A unit coordinator may only see data related to the units they coordinated
o An AQ panel member may only see data related to the units offered by the schools they are panel members of
o School Level Report:
o Report shows aggregated unit compliance across the schools and reflection/response plan for the schools
o Ability to drill down to unit level
o For AQ panel members and executives
o An AQ panel member may only see data related to the units offered by the schools they are panel members of
o Faculty Level Report
o Report shows aggregated unit compliance across the faculty and schools and reflection/response plans for the schools and faculties
o For executives and AQ panel members
o An AQ panel member may only see data related to the units offered by the schools they are panel members of
ROW LEVEL SECURITY
Report Security, Privacy and Confidentiality Requirements
o Three groups/types of users
o Unit coordinators
o AQ panel members
o Executives
o Three reports
o Unit Report: Detailed data on individual units
o School Report: Aggregated & detailed data of units across the school & school reflections
o Faculty Report: Aggregated data of schools across the faculty & school reflections & faculty reflections
o Two subsets of data
o Detailed unit responses (used in unit and school report)
o Aggregated responses + school & faculty reflections (used in school and faculty report)
o Row Level security required on Data
o A Unit coordinator should only see units they coordinate
o An AQ panel member should only see schools they are panel members of
ROW LEVEL SECURITY
Design
o Three groups of users:
o Define 3 groups in SAS Metadata Console (SMC)
o DIS jobs which utilise the %MDU macros to import/update users via Active Directory & Curtin HR Database
ROW LEVEL SECURITY
Implementation
o Three reports
o Create and apply ACTs with SMC to which allow only the relevant groups to access the relevant reports
o Two subsets of data
o Create and apply ACTs with SMC to which allow only the relevant groups to access the relevant data
ROW LEVEL SECURITY
Implementation
o Row Level security
o Create additional column in each row of data which contains the staff ID of the unit coordinator
o Create additional column in each row of data which contains the staff ID of all AQ panel members
o Using VA’s Row Level Security (conditional grants), create and define rules which allow the logged in user to access specificrows of data
ROW LEVEL SECURITY
Implementation
ROW LEVEL SECURITY
Implementation
o Streamlined the AQ process
o Reduced the overall time spent on the process
o Improved and standardised the reports
o Previously used print-out reports
o Improved security/confidentiality of data
o Printed/emailed reports were easier to misplace and be seen by others
ASSESSMENT QUALITY PROCESS (AQP)
Project Outcomes
ARCHITECTURE, ADMINISTRATION AND SECURITY CONSIDERATIONS IN A SAS BASED PROJECT
Herbert Au – BI Applications Administrator