ARCHITECTURE, ADMINISTRATION AND SECURITY CONSIDERATIONS IN A SAS BASED PROJECT Western Australian SAS User Professionals (WASUP) May 2017 CRICOS Provider Code 00301J
ASSESSMENT QUALITY PROCESS (AQP) Background 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) Project overview (Requirements, Design & Lifecycle) 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 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 o 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
SAS ARCHITECTURE AT CURTIN Curtin Architecture history and constraints 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 Current Curtin Architecture 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 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
ASSESSMENT QUALITY PROCESS (AQP) Recap 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 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 o 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
MULTIPLE SAS ENVIRONMENTS Synchronising metadata and data between SAS environments 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 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 (Ideally) Synchronising data 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 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)
ASSESSMENT QUALITY PROCESS (AQP) Recap 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 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 o 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
ROW LEVEL SECURITY Report Security, Privacy and Confidentiality Requirements 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 o faculties 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 o
ROW LEVEL SECURITY Design 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 o
ROW LEVEL SECURITY Implementation 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 o
ROW LEVEL SECURITY Implementation 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 o
ROW LEVEL SECURITY Implementation 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 specific o rows of data
ROW LEVEL SECURITY Implementation
ASSESSMENT QUALITY PROCESS (AQP) Project Outcomes 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
ARCHITECTURE, ADMINISTRATION AND SECURITY CONSIDERATIONS IN A SAS BASED PROJECT Herbert Au – BI Applications Administrator
Recommend
More recommend
