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

Version 1 Next »








R8 Data Extact Views Training – Day Training Agenda



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

First open a VPN connection:

  1. Click Start, select All Programs, Cisco, Cisco AnyConnect Secure Mobility Client
  2. In the "Ready to Connect" box type asa-regent.regenteducation.net and click Connect


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



  1. Once you've successfully connected via VPN, open SQL Server Management Studio (SSMS) by clicking Start, All Programs, Microsoft SQL Server 2008, SQL Server Management Studio
  2. 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.


  1. 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 Section 3.4.

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


Data Extract Views – Overview of Each View

Note: This section is not up-to-date. Please refer to the latest R8 Data View Data Dictionary for the full reference list of all available data views and their columns.

dataExtract_AcademicBreak_Config_View

This view provides access to the data displayed on the Academic Break configuration screen in R8.

dataExtract_AcademicPlan_View (deprecated)

This view has been deprecated.
This view provides access to much of the data displayed on the Academic Plan tab in R8.

dataExtract_AcademicPlan_View_v001

This view provides access to some of the data displayed on the Academic Plan tab in R8. Please note that the Academic Plan tab pulls in data from several different sources. This view does not provide access to all data surfaced by that tab in R8.
To get a full perspective you will need to join to the dataExtract_AcademicYear_View_v001 and dataExtract_Enrollment_View_v001 for enrollment data. Likewise you would need to walk the Award Chain data (via the dataExtract_AwardPeriod_View_v001, dataExtract_Award_View_v002) views to pull in award data.

dataExtract_AcademicYear_View_v001

This view provides access to some of the data displayed on the Academic Plan tab in R8. Specifically data relating to a Student's Academic Year.

dataExtract_ActivityLog_View

This view pulls in all data as found in the activity log section of the Activity tab screen in R8

dataExtract_Award_View_v001 (deprecated)

This view has been deprecated

dataExtract_Award_View_v002

This view pulls in data as found on the Awards and Academic Plan tab in R8.

dataExtract_AwardDisbursement_View (deprecated)

This view has been deprecated.
This view pulls in the awarding, payment period and disbursement data that is found on the Awards tab in R8. This includes the secondary screens and dialogs.

dataExtract_AwardDisbursement_View_v001

This view provides access to disbursement data totaled/aggregated at the Payment Period level. Please use paymentPeriodId to join against the dataExtract_PaymentPeriod_View_v001 data view.

dataExtract_AwardLetterCommunication_View_v001

This view is customer-specific and can be ignored.

dataExtract_AwardPackagingStatus_View

This view pulls in the same data as found on the Award Packaging Status report in R8

dataExtract_AwardPeriod_View_v001

This view contains Award Period data, as shown on the Academic Plan and Awards tab in R8.

dataExtract_COA_View (deprecated)

This view has been deprecated.
This view pulls in Cost of Attendance summary information.

dataExtract_COA_View_v001

This view pulls in Cost of Attendance summary information. This information is displayed on the Awards tab in R8 and in various reports. This view breaks data down at the payment period level – therefore all calculations (unmet need, unmet cost) are totaled at the payment period level.

dataExtract_COADetail_View

This view pulls in Cost of Attendance detail information. This is meant to augment the information found in the dataExtract_COA_View. Each individual enrollment cost item is listed in this report.

dataExtract_CODAward_Export_View (deprecated)

This view has been deprecated.

dataExtract_CODAward_Export_View_v001

This view provides access to Award-Level data that has been exported to COD.

dataExtract_CODAward_Import_View (deprecated)

This view has been deprecated.

dataExtract_CODAward_Import_View_v001

This view provides access to Award-Level Data that has been imported from COD.

dataExtract_CODAwardDisbursement_View (deprecated)

This view has been deprecated.
This view pulls in data from the COD Awarding and Disbursement tables in R8. A good portion of this data is displayed on the COD tab in R8.

dataExtract_CODDisbursement_Export_View (deprecated)

This view has been deprecated.

dataExtract_CODDisbursement_Export_View_v001

This view provides access to Disbursement-Level data that has been exported to COD.

dataExtract_CODDisbursement_Import_View (deprecated)

This view has been deprecated.

dataExtract_CODDisbursement_Import_View_v001

dataExtract_CODStudent_Export_View (deprecated)

This view has been deprecated.

dataExtract_CODStudent_Export_View_v001

dataExtract_CODStudent_Import_View (deprecated)

This view has been deprecated.

dataExtract_CODStudent_Import_View_v001

dataExtract_CommunicationProcessStudent_View_v001

dataExtract_CostGroupSiteProgram_Config_View

dataExtract_CostSetup_Config_View

dataExtract_CourseAdjustment_View

This view pulls in Course Adjustment data from R8.

dataExtract_CourseData_View

This view pulls in Course Data from R8. Much of this data is represented on the Courses tab in R8.

dataExtract_CourseEnrollmentRollup_View_v001

dataExtract_CreditDecision_View_v001

dataExtract_Disbursement_View_v001

dataExtract_DisbursementSchedule_View

dataExtract_Document_Config_View

dataExtract_DocumentRequirement_View

This view pulls in Student Document Requirement data. This data is represented on the Documents tab in R8.

dataExtract_Enrollment_View_v001

dataExtract_EnrollmentLevelSetup_Config_View

dataExtract_Enterprise_Config_View

This is a config-view that pulls in configuration data for the Enterprise entity in R8.

dataExtract_ESTAward_View

dataExtract_ESTDisbursement_View

dataExtract_ESTRecord_View

This view pulls EST Record information from R8. It only includes EST Actual Transactions. The latest EST Actual data can be pulled by querying by the process ID (ioProcessId)

dataExtract_ESTStudent_View

dataExtract_Fund_Config_View

dataExtract_FundBudget_Config_View

This config-view pulls in configuration data for the FundBudget entity in R8. This includes summary FundBudget figures as found on the Fund Budget screen in R8.

dataExtract_FundFeesSetup_Config_View

dataExtract_FundGroupSetup_Config_View

dataExtract_FundProgram_Config_View

dataExtract_FundSite_Config_View

dataExtract_ImportAwardDisbursement_View

dataExtract_Institution_Config_View

This is a configuration view that pulls in configuration data for the Institution entity in R8.

dataExtract_ISIR_ComentCode_View

This view pulls in ISIR Comment Code data. This same data is represented on the ISIR tab in R8.

dataExtract_ISIR_CommentCodes_Lookup_View_v001

dataExtract_ISIR_RejectCode_View

This view pulls in ISIR Reject Code data. This same data is represented on the ISIR tab in R8.

dataExtract_ISIR_Rejects_Lookup_View_v001

dataExtract_ISIR_View

This view pulls in all ISIR records found in R8. This same data is represented on the ISIR tab in R8.

dataExtract_ISIRCorrection_View

This view pulls in ISIR Correction data. This same data is represented on the ISIR tab in R8.

dataExtract_LoanPeriod_View_v001

dataExtract_Location_Config_View

dataExtract_LoginRole_Config_View

This is a config-view that contains User and Role configuration data.

dataExtract_NSLDS_Default_Overpayment_Information_View

This view contains data as found on the NSLDS tab in R8.

dataExtract_NSLDS_Files_Sent_And_Received_View

This view contains data as found on the NSLDS tab in R8.

dataExtract_NSLDS_Financial_Aid_History_View

This view contains data as found on the NSLDS tab in R8.

dataExtract_PackagingPhilosophySetup_Config_View

dataExtract_PaymentPeriod_View_v001

dataExtract_ProcessLog_View

This view contains the data found in the Process Log in R8.

dataExtract_Program_Config_View

dataExtract_ProgramSite_Config_View

dataExtract_ProgramTerm_Config_View

dataExtract_R2T4_View

This view contains the data found in the R2T4 Wizard in R8.

dataExtract_RebuildAwardDisbursement_View

dataExtract_Resource_View

This view contains Student resource data as found in the Awards tab in R8.

dataExtract_SAPRecord_View_v001

dataExtract_SAPSetup_Config_View

dataExtract_SBLValidationErrors_View

dataExtract_Student_View (deprecated)

This view has been deprecated.
This view contains data as found on the Student tabs in R8. This view contains the Active bit for each Student and it is likely that you will want to include this view in your queries in order to filter out active or non-active students.

dataExtract_Student_View_v001

dataExtract_StudentAddress_View

This view contains data Student Address data as found on the Student Details tab in R8.

dataExtract_StudentBBAYModification_View_v001

dataExtract_StudentBlock_View (deprecated)

This view has been deprecated.

dataExtract_StudentBlock_View_v001

dataExtract_StudentBreak_View (deprecated)

dataExtract_StudentBreak_View_v001

dataExtract_StudentDetail_View

This view contains data Student Details data as found on the Student Details tab in R8.

dataExtract_StudentEmail_View (deprecated)

This view has been deprecated.
This view contains data Student Email data as found on the Student Details tab in R8.

dataExtract_StudentEmail_View_v001

dataExtract_StudentInstitutionallyDefinedData_View

This view contains data Student UDF data as found on the Student Details tab in R8.

dataExtract_StudentParentSpouseAddress_View

This view contains data Student Parent/Spouse Address data as found on the Student Details tab in R8.

dataExtract_StudentParentSpouseEmail_View

This view contains data Student Parent/Spouse Address data as found on the Student Details tab in R8.

dataExtract_StudentParentSpousePhone_View

This view contains data Student Parent/Spouse Phone data as found on the Student Details tab in R8.

dataExtract_StudentParentSpouseReference_View

This view contains data Student Parent/Spouse Reference data as found on the Student Details tab in R8.

dataExtract_StudentPhone_View

This view contains data Student Phone data as found on the Student Details tab in R8.

dataExtract_StudentR2T4_View

dataExtract_StudentSAP_View (deprecated)

This view has been deprecated.

dataExtract_StudentSAP_View_v001

dataExtract_StudentTestScores_View

This view contains data Student Test Score data as found on the Student Details tab in R8.

dataExtract_SystemBatchPackagingLog_View

This view contains the same data as found on the System Batch Packaging report in R8.

dataExtract_SystemBatchPackagingLog_View_v001

dataExtract_Task_Config_View

dataExtract_Task_View

This view contains Task data as found on the Task screen in R8.

dataExtract_Term_Config_View

This config-view contains configuration data for the Task Setup in R8.

  • No labels