Regent Award Data Views - Technical Guide

Owned by Katie Macpherson (Deactivated)
Last updated: Mar 16, 2021 by Bradley Shindelman (Deactivated)Version comment
5 min read



Introduction

This is a technical guide for the Regent Award Data Views. This guide provides information about the type of data views available, view versioning policies, conventions and information on how to connect to the database where the data views reside. 

How to Access the Views

Summary

The Regent Award Data Views are made accessible via a reporting database (RPT) that is configured specifically for this purpose. This database is a copy of QA or Prod, depending on which instance you are accessing. The database name will follow the pattern <clientname>_rem_<instance>_rpt. For example, the reporting database for "Frederick College" Regent Award QA2 instance would be something similar to frederickcollege_rem_qa2_rpt. These specific details of database name and IP will be provided to you by the Regent Education Operations team at the time data views are configured for your instance. 

The reporting database is a Microsoft SQL Server 2008 (or later) database. Any application that offers an integration/connection method to Microsoft SQL Server (MSSQL)  can be used to connect to the data views reporting database. Below you will find instructions on how to use SQL Server Management Studio to access where the data views are stored. SQL Server Management Studio Express can be obtained free from Microsoft via this link https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms.

Connect to VPN and SQL Server Management Studio


1. First open a VPN connection (if not already connected). This can be done by opening the application titled Barracuda VPN Client

2. In the Username box type REGENTSAAS\username and enter your REGENTSAAS PW  → click Connect



3. Once you've successfully connected via VPN, open SQL Server Management Studio (SSMS)


4. In the "Connect to Server" window, make sure Database Engine is selected for "Server type", fill in the "Server name" you were provided, select SQL Server Authentication in the "Authentication" drop down, fill in the login and password you were provided and click Connect.

5. After you've successfully logged in to the data views (reporting) server/database you can open a query window by clicking File, New, Query with Current Connection OR the New Query button just under the menu OR Ctrl-N


Using the Data Extract Views

Data Extract Views List

There are currently over 100 Data Extract Views offered by Regent Award. The full list of views is contained in the Regent Award Data View Data Dictionary.

The views offer access to nearly all data surfaced by the Regent Award interface as well as some data that is not available in the user interface. Some of these views have been deprecated and replaced by newer versions. The view versioning policy is explained below. 

Data Extract View Groups and Conventions

View Group List

There are three groups of data extract views:

  • Student-Data Views
    • These views do not contain the string "_Config" in the view name
  • Config-Data Views
    • These views contain the string "_Config" in the view name
  • Lookup-Data Views
    • These views contain the string "_Lookup" in the view name

View Group Conventions


Student-Data Views contain student data and by default should contain the following fields:

  • Regent Award Internal Student Id (studentId)


Config-Data Views contain configuration data and by convention should contain the following fields:

  • Location ExternalId (locationExternalId)
  • Location Name (locationName)
  • Site External Id, if applicable (siteExternalId)
  • Site Name, if applicable (siteName)


Lookup-Data Views are helper views containing "code to name" translation. These views are specific to the data model in question and do not follow a pre-set convention.

View Conventions

The Data Extract Views abide by the following conventions:

  1. All Reference-List codes are left as is without translation. This is possible because all reference list codes in Regent Award are intuitive. Some examples:
    1. dataExtract_AwardDisbursement_View.a_awardStatusCode = 'ACCEPTED'
      1. Equates to 'Accepted' award status
    2. dataExtract_AwardDisbursement_View.d_disbursementStatusCode = 'PAID'
      1. Equates to 'Paid' disbursement status
    3. dataExtract_AcademicPlan_View.academicPlan_enrollmentLevelTypeCode = 'HalfTime'
      1. Equates to 'Half Time' Enrollment Level
    4. dataExtract_AcademicPlan_View.academicPlan_housingStatusCode = 'OffCampus'
      1. Equates to 'Off Campus' housing status
  2. Views may contain deleted rows This rule applies to views that have the potential to contain a large data set and are therefore a candidate for incremental delivery via BCP. Such views must contain a deleted bit as well as a primary key for the view. . This is indicated by a bit column named 'deleted' or contains the string 'deleted'.
  3. Active and Non-Active students are included in all results sets.
    1. The dataExtract_Student_View contains the active indicator and can be used to filter on active/non-active status if necessary.
  4. Any field of data type BIT is represented in raw format (i.e. 1 or 0) – no translation to 'Yes/No' or 'True/False' is put in place.
  5. All records will contain creation/modification information (createdBy, createdOn, modifiedBy, modifiedOn)
  6. Inherited values in configuration tables should pull down the inherited value from the parent table.
    1. dataExtract_Program_View.academicCalendarType – this field can be inherited from Location or Institution and should therefore be pulled down in the event that it is inherited

View Versioning

View Naming Schema

As the Regent Award product evolves, it is quite likely that any changes made to the underlying data model must be then, in turn, propagated to the data extract view data model. Any change that is determined to be a breaking change will result in the creation of a new version of the view. Breaking vs. non-breaking change policy is explained below.


New versions of a view will contain an incremented version value in the view name. This is indicated by a suffix of "_v00x". Where "x" represents the version number.
Example #1 – if the old version of the view was titled dataExtract_ABC_View_v001 then the new version of the view would be titled dataExtract_ABC_View_v002.
Example #2 – if the old version of the view was titled dataExtract_ABC_View then the new version of the view would be titled dataExtract_ABC_View_v001.

Breaking vs. Non-Breaking Changes

Only breaking changes will necessitate the creation of a new version of the view.
Breaking changes are defined as:

  • Adding a new column
  • Renaming of a column (to include a change in case)
  • Removing of a column
  • Making a change to the JOIN logic that will result in a significant change to the returned data set


Non breaking changes are defined as:

  • Minor adjustments to JOIN logic
  • Adding of comments, documentation or other metadata to the definition of the view