Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 2 Next »



Introduction

The purpose of this document is to provide information on how a customer may connect and access the R8 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 application or to obtain large amounts of data which if accessed via R8 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

2. In the "Ready to Connect" box type asa-regent.regenteducation.net and click Connect


3. When prompted, enter your username and password (provided previously) in the appropriate box and click OK



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

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

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. The views offer access to nearly all data surfaced by the REM interface as well as some data that is not available in the REM 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 Dictionary. 

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 Internal Student Id (studentId)
  • R8 External Student Id #1 (externalId1)


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 R8 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 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 vs. non-breaking change.
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




  • No labels