How might we load data from business laptops to an IT secured SAS® platform? 

Loading Woes

 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

Loading Woes

Flexible - yes
Quick - yes
Safe - no 

Batch ETL Loads?

Loading Woes

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

Loading Woes

Flexible - yes
Quick - no 
Safe - no 

Bonus Woe Factors

• Complete lack of data auditability
• Development costs
  • Custom code for every new data source
  • Code Reviews
  • Testing
  • Release Gates
  • Maintenance
• Ever increasing artefacts

Loading Woes

A Secure, Cloud Solution

Data Controller enables Data Owners to self-serve their data updates - with full security, transparency and auditability.

Process Overview

Process

Business Benefits

Benefits

• SPEED - eliminate the dev / test promote cycle
• ACCURACY - self-serve data quality rules 
• TRACEABILITY - back to the user, and excel file

Bonus Features

Features

• Data Alerts
• Data Lineage
• Data Dictionary
• Data Catalog
• Metadata Explorer (SAS 9 only)
• User Navigator
• DDL Generation

Editor Flow

Great Updates

Editors can modify records, add new records, and mark records for deletion.  These changes are uploaded to a secure staging area prior to approval.

Features include:

• 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

Approval Flow

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

Great Updates

User Navigator

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)

More Features

Metadata Navigator

Essentially, "metabrowse" for the browser! (SAS9 only)

More Features

• Choose Repository (eg Foundation)
• Choose metadata type
• Browse objects, associations, properties & attributes
• Link to any object

Data Lineage

Trace tables and columns from source to target (and back)

More Features

• 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

Data Catalog

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)

More Features

Data Dictionary

Store long & short descriptions (markdown) along with "owner" and "responsible" information for:

• Libraries
• Tables
• Columns
• Formats
• Catalogs
• Directories
• etc

More Features

What about the technical implementation?  Security? Administration?

Features

Read on!

Supports Multiple Load Types

The following technical implementations are natively supported:

• SCD2 loads 
• Composite natural keys
• Surrogate / retained keys
• Bitemporal
• Full Replace
• Format Catalogs

Features

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:
 

• Postgres
• SQL Server
• Oracle
• Teradata
• Netezza
• Redshift

Features

Zero Code

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.

Features

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

Features

Components

More info

1. Front end, deployed to SAS® Web Server
2. Backend Services (Stored Processes or Job Execution)
3. 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?

Action Step

Contact us for a copy!  Free (forever) for 5 users.

https://datacontroller.io

Frequently Asked Questions

More info

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

More info

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

More info

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.

Coming Soon

Roadmap

• Restore Previous Version
  • Choose previous version and resubmit for approval
• Usage Reports
• Data Quality reports
• Data Masking