Chief SAS App Officer
How might we load data from business laptops to an IT secured SAS® platform?
Direct Data Updates?
Certain power users may be granted permissions to update data directly, eg using SAS code or a wizard. Main issues are:
- Risk of data corruption
- Risk of data locks
- High chance of a data model death spiral
Flexible - yes Quick - yes Safe - no
Batch ETL Loads?
We could build routines to test and load files via a designated 'landing area'. However:
- Raw data exposed in shared filesystem
- Fragility against changing input formats
- Overnight Schedule
Flexible - no Quick - no Safe - yes
Use an Administrator?
It may be possible to request an administrator to perform direct production updates. Unfortunately:
- Need for code review / testing
- Need for change requests, even for small changes
- Soaks up valuable administrator time / resources
Flexible - yes Quick - no Safe - no
Bonus Woe Factors
- Complete lack of data auditability
- Custom code for every new data source
- Code Reviews
- Release Gates
- Ever increasing artefacts
A Secure, Cloud Solution
Data Controller enables Data Owners to self-serve their data updates - with full security, transparency and auditability.
- SPEED - eliminate the dev / test promote cycle
- ACCURACY - self-serve data quality rules
- TRACEABILITY - back to the user, and excel file
- Data Alerts
- Data Lineage
- Data Dictionary
- Data Catalog
- Metadata Explorer (SAS 9 only)
- User Navigator
- DDL Generation
Editors can modify records, add new records, and mark records for deletion. These changes are uploaded to a secure staging area prior to approval.
- Data quality rules at source
- Automatic not null / length /data type protection
- Primary Key protection
- Drag & drop Excel or CSV
- URL to individual records or data subsets
Proposed changes approved before applied to target. The Approver only sees the changes that will be applied to the data and can accept / reject as appropriate.
- All changes require one or more approvals
- "Delta view" reduces risk of inadvertent changes
- Post-approve script enables workflow
- Can provide a reason for accept / reject
When setting up permissions, it's necessary to know which users belong to which group. This information is available in both SAS 9 and Viya versions (Viya version does not contain roles)
Essentially, "metabrowse" for the browser! (SAS9 only)
- Choose Repository (eg Foundation)
- Choose metadata type
- Browse objects, associations, properties & attributes
- Link to any object
Trace tables and columns from source to target (and back)
- Distinguish datasets, db tables and files
- View transform info
- View transformation logic
- Export as SVG or PNG
- Download CSV of Jobs, Tables etc
- Queries are cached for performance
- Link to a particular lineage trace
Attributes of Libraries, Tables and columns - including row counts, primary key information, and metrics such as number of tables without primary keys
Frequently changing attributes are kept in seperate tables (eg rowcounts, size info, modified date)
Store long & short descriptions (markdown) along with "owner" and "responsible" information for:
What about the technical implementation? Security? Administration?
Supports Multiple Load Types
The following technical implementations are natively supported:
- SCD2 loads
- Composite natural keys
- Surrogate / retained keys
- Full Replace
- Format Catalogs
Supports Multiple Databases
If you have the requisite SAS/ACCESS engine, then the Data Controller can manage your data updates.
We've deployed successfully against:
- SQL Server
Adding new tables, user groups, drop down data validation menus etc is simply a matter of configuration. Adding a new table is just 2 entries - table attributes, and security attributes.
No need to define every column, as these attributes are taken from the target table at runtime!
Secured by SAS®
The following SAS® security mechanisms are utilised:
- SASLogon authentication
- SAS Groups (Metadata or OS level)
- for editors / approvers
- determines authorisation
- Front end, deployed to SAS® Web Server
- Backend Services (Stored Processes or Job Execution)
- Data Model, deployed to the database of your choice
Additional, optional components:
- Macro library for data management
- DI Studio Bitemporal Transform
Right, I'm sold. I'd like to try it out. What next?
Frequently Asked Questions
What are the prerequisites?
A modern web browser (IE11+) and SAS 9.3 or Viya 3.5.
Will this work for my preferred database (Postgres / Oracle / Netezza / Teradata)?
If you have the relevant SAS Access Engine, then most likely yes. However there may be instances where the data controller is not suitable (eg for BLOBS, files, very long strings). If you have specific / unusual requirements, lets have a chat.
Frequently Asked Questions
Do we need to grant special permission to end users?
No - updates are made using the stored process system account (eg `sassrv`). External credentials can be taken from metadata or granted directly to that account. End users are therefore enabled to make controlled updates (via the tool) without needing to have direct (uncontrolled) access to the data.
Frequently Asked Questions
Do we need to enable XCMD (OS level access) on the STP server?
Nope - all services use regular SAS code (no XCMD needed).
Are file uploads supported?
Yes. We have an OEM licence for sheetJS - you can upload any type of excel file. If you have a lot of data (eg 100k rows), you can upload a CSV, this will bypass the frontend rendering part.
- Restore Previous Version
- Choose previous version and resubmit for approval
- Usage Reports
- Data Quality reports
- Data Masking
By Allan Bowe