Image Modified

Introduction

The purpose of this document is to provide information on how a customer may connect and access the R8 Regent Award Data Extract Views and query the data views to obtain information. It is important to note that the R8 Data Extract Views are still undergoing development. Therefore, the views referenced in this document and the document itself is not finalized.  

How to Access the Views

Summary

This section of the training is provided to assist customers in accessing the data views which can be used to obtain data that cannot be currently obtained via the R8 Regent Award application or to obtain large amounts of data which if accessed via R8 Regent Award may have a negative impact on application performance. For the purposes of this training SQL Server Management Studio is used to access where the data views are stored. SQL Server Management Studio Express can be obtained free from Microsoft via this link http://www.microsoft.com/en-us/download/details.aspx?id=7593.

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 Cisco AnyConnect Secure Mobility Client

...

6. 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

Summary

The purpose of this section is to provide a general overview of the R8 Data Extract Views. This overview is not meant to serve as a data-dictionary for the views.

Data Extract Views List

There are currently over 100 Data Extract Views offered by R8. 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 REM Regent Award interface as well as some data that is not available in the REM user interface. Some of these views have been deprecated and replaced by newer versions. Please see the Section 3.3.3 to understand the versioning policy.
The full list of views is contained in the Regent Award Data View Data DictionaryThe 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:

• R8 Regent Award Internal Student Id (studentId)
• R8 Regent Award External Student Id #1 (externalId1)

...

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 R8 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 R8 Regent Award product evolves, it is quite likely that any changes made to the underlying R8 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. Please see section 3.3.4.2 for the definition of a breaking 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:

...

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

...