How might we adjust data on a secure SAS® platform? 

Loading Woes

Save Files on Network Drives?

Loading Woes

We could build a set of batch routines to test and load files via a designated 'landing area'.  However:

  • Raw data is exposed in shared filesystem
  • No tracking of who made the change
  • Custom routines needed for every file
  • Data often not loaded unless all files are ready
  • Difficulty with deletes
  • Constantly changing file formats

 Perform Direct Data Updates?

Certain power users may be granted permissions to update data directly, eg using SAS code.  Issues with this include:

  • Significant risk of unintentional data corruption
  • No tracking of who made the change and when
  • Custom code needed for every update
  • For datasets, users may trigger a 'lock' preventing other users from performing data loads

Loading Woes

Use an Administrator?

For some data, it may be deemed necessary to raise a ticket for an administrator to perform the update.  Unfortunately, this results in:

  • Significant delays to the process
  • Need for code review / testing
  • Need for change requests, even for small changes
  • Soaks up valuable administrator time / resources

Loading Woes

A Secure Solution

The Data Controller is a SAS® Stored Process Web Application which enables secure, real time updates to source data - with full auditability.

Great Updates

Process Overview

Great Updates

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.

Benefits include:

  • Data validation performed at point of entry
  • Copy paste direct from Excel
  • Primary Key protection
  • Filter capability with URL linking

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

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

Features

Supports Multiple Databases

If you have the requisite SAS/ACCESS engine, then the Data Controller can manage your data updates

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 Metadata Groups 
    • for editors / approvers
    • determines authorisation

Features

Components

More info
  1. Front end, deployed to SAS® Web Server
  2. Stored Processes, deployed to SAS® Metadata
  3. Macro library, deployed to SAS® Application Server
  4. Data Model, deployed to the database of your choice

Right, I'm sold.  I'd like to try it out.  What next?

Action Step

Contact us for a friendly, no obligation chat!

https://datacontroller.io

Frequently Asked Questions

More info

What are the prerequisites?

A modern web browser (IE11+) and SAS94.  That's it.

 

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 a 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?

Currently only CSV uploads are supported, other file types will be made available in future releases.

 

 

Coming Soon

Roadmap

  • Excel Uploads
    • Uploading .xls format (currently only .csv is supported)
    • Uploading direct from excel
  • Restore Previous Version
    • Choose previous version and submit for approval
  • Choose Official Version
    • "signoff" capability
  • Usage Reports

Data Controller

By Allan Bowe

Data Controller

The Data Controller lets you load, change, and delete data in SAS - securely, safely, and transparently.

  • 248
Loading comments...

More from Allan Bowe