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

Loading Woes

Build bespoke ETL Loads?

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 (batch account)
  • Custom routines needed for every file
  • Data often not loaded unless all files are ready
  • Difficulty with deletes
  • Constantly changing file formats
  • Overnight Schedule

 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, Cloud Solution

Data Controller enables Data Owners to self-serve their data updates - with full security, transparency and 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.

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

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

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 30 day free demo version!

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.

 

 

Frequently Asked Questions

More info

Do you have an evaluation version we can try?

Yes - bundled as a single SPK for ease of deployment

Coming Soon

Roadmap

 

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

Data Controller

By Allan Bowe

Data Controller

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

  • 1,005
Loading comments...

More from Allan Bowe