Breadcrumbs

DW Interface 1.1

Introduction

The Data Warehouse Interface (“DW Interface'') provides access to the OLAP-ready version of your Assessment platform data to import into your data warehouse. Once the DW Interface is implemented, you can use platform data in your institutional reports or dashboards. 

To import data into a data warehouse, the relational data model must be converted to a snowflake data model and requires an ETL process to take place. We provide a data warehouse-ready data model, the model we use in our internal reporting, to customers, and update the data on regular intervals. Please note, the data we provide is institutional level data and the necessary permission structure within the organization should be implemented by your institution internally.

This documentation describes the process of preparing and sending the data, explains the data format, and provides samples.

This document does not cover all capabilities of the DW Interface. If you have questions on how to use the DW Interface for a specific use-case, please contact support@heliocampus.com.

Transfer of Data

Once the DW Interface has been configured for your institution, Secure FTP (SFTP) server access will be provided where a weekly snapshot of your data will be available. Another option is AWS S3 bucket. The data will be provided as CSV files compressed into a ZIP file (or .gzip format). You can download the snapshot file, decompress it and import it into your data warehouse using your native ETL process and tools.

In order to obtain the DW data, we can work with you to do one of the following:

  1. Make the data available in your existing SFTP account, where your SIS data feed is uploaded to the Assessment Platform. 

    1. If you choose this option we will create a folder at the root of your existing account called datawarehouse where you can download the data.

  2. If you do not have an existing SFTP account, we work with you to create one for you to make the data available for download. Please refer to our SFTP Guide for the requirements to set up an account.

Format and Samples

Each CSV file is encoded with Unicode (UTF-8) encoding and is compatible with RFC 4180 standards namely:

  1. MS-DOS-style lines that end with (CR/LF) characters 

  2. A header record

  3. NULL fields are not omitted. Each line has the same number of fields

  4. Every field is quoted with double quotes (“)

  5. Double quotes in field values are represented by two consequent double-quotes. 

  6. For datetime fields, the UTC format is used. (yyyy-MM-ddTHH:mm:ss±hhmm)


Below are a couple example csv files. See instances of examples showing datetime fields, empty fields, fields with special characters.

  • see created_date and updated_date columns for date fields

  • line# [12 - 16] show and example record with new line character in content.

  • line# 18 (in lms_data column) show a json structure stored in a field. “ characters in content escapted as two consequent “ characters.

Assignment Example

DB Schema Structure and Sample Data Export

Design Concepts

This section describes general data warehouse concepts and types of objects in the Data Warehouse.

Fact tables

Contains all the data to be analyzed such as measurements and metrics. For example assessment results, assignment grade information, and all similar measures would be placed in fact tables.

Hierarchy tables

Combines multiple dimensions with parent-child relationships. Contains textual attributes like Name, Code, and Identifier columns per dimension. Typically an institution, college, department, course hierarchy, or relationships between programs, outcomes, and courses would be in these types of tables. Some Hierarchy tables are interchangeable by their table structure but they contain different levels of data, for example, dw_user_major_enrollment_hierarchy and dw_user_enrollment_hierarchy have the same column structure but one has major level one does not, instead it has null in related columns.

Data Exports

The DW Interface is broken down to groups of files that contain specific data around platform functions. See the data export details below for a specific group of data:

Common Tables Details

  • institution: Contains institution specific information.

  • college: Define colleges. A college belongs to the institution.

  • department: Defines departments. Department is part of a college.

  • program: Defines programs.

  • course: Defines courses.

  • course_section: Course sections are term specific objects of courses.

  • document: Details of document which is attached to the form.

  • role: Contains role for which assessment, survey or data collection is created.

  • artifact: Details of artifact which is assigned to the form.

  • term: Contains the term for which assessment, survey or data collection is created.

  • note: Details of the note which is entered to the form.

  • user_account: Details of user are stored ie; user_id, user_name, name, surname, status, email, user creation time.

  • user_role: Details of user role which is assigned to the user_id and role_id.

  • academic_year: Contains academic year for which assessment, survey or data collection is created.

  • dw_course_section_hierarchy: The dw_course_section_hierarchy model is a comprehensive database that provides detailed information about various courses and their sections. It includes data such as course name, description, status, and unique identifiers. It also provides information about the course sections, including enrollment count, term details, campus code, and instructional method. This model can be used to analyze course enrollment trends, evaluate the effectiveness of different instructional methods, and plan for future course offerings. It can also help identify patterns in course enrollment and term-based courses.

  • dw_course_section_major_hierarchy: The dw_course_section_major_hierarchy model is a tool that provides a detailed overview of course sections and their corresponding major fields of study. It includes information such as the course section name, major name, and whether the course is co-curricular or term-based. This model can be used to track student enrollment patterns, identify popular majors, and analyze the impact of co-curricular activities on student performance. It can also help in planning course schedules and managing resources effectively.

  • dw_course_section_no_major_hierarchy: The dw_course_section_no_major_hierarchy model is a tool that provides a detailed overview of various course sections without focusing on specific majors. It includes information such as course section ID, name, code, and whether it's co-curricular or term-based. It also provides the start and end dates for co-curricular activities. This model can be used to track and analyze course section enrollment, helping to identify trends and patterns in student course selection. It can also assist in planning and scheduling by providing insights into the popularity and timing of co-curricular activities.

  • dw_organizational_hierarchy: The dw_organizational_hierarchy model is a tool that provides a detailed view of an educational institution's structure. It organizes data from the institution level down to individual programs, including colleges and departments within. This model can be used to understand the administrative structure, identify patterns in program offerings, and analyze relationships between different parts of the institution. It's like a map, showing how different parts of the institution connect and interact.

  • dw_term_hierarchy: The dw_term_hierarchy model is a tool that organizes academic terms, their types, and corresponding academic years. It's like a calendar for an educational institution, detailing when each term starts and ends, and which academic year it belongs to. This model can be used to track and analyze academic progress over time, identify patterns in term durations, and plan future academic calendars. It can also help in understanding the correlation between term types and academic years.

  • dw_user_enrollment_hierarchy: The dw_user_enrollment_hierarchy model is a tool that provides a detailed view of student enrollment in a university. It includes information about the students, the courses they are enrolled in, and whether these courses are part of the co-curricular activities. It also provides data on the start and end dates of these activities. This model can be used to track student progress, identify patterns in course enrollment, and understand the popularity of different majors and co-curricular activities. It can also help in planning future course offerings and co-curricular programs.

  • dw_user_major_enrollment_hierarchy: The dw_user_major_enrollment_hierarchy model is a tool that provides a comprehensive view of student enrollment in a university. It combines information about students, their chosen majors, and their participation in co-curricular activities. This model can be used to track student progress, identify trends in major selection, and evaluate the impact of co-curricular activities on student success. It can also help in planning academic programs and resources. The model reveals patterns such as the popularity of certain majors, the correlation between co-curricular participation and academic performance, and the distribution of students across different terms.

  • course_section_link

  • dw_course_hierarchy: The dw_course_hierarchy model is a comprehensive database that organizes information about academic institutions, colleges, departments, courses, and course sections. It's like a digital filing cabinet that keeps track of everything from the name and description of an institution to the specific details of a course section. This model can be used to answer questions like "What courses are offered by a particular department?" or "What is the instructional method of a specific course section?". It can also reveal patterns such as the most common instructional methods or the most popular courses.

  • course_section_faculty

  • artifact_document

  • dw_user_enrollment_linked_hierarchy: The dw_user_enrollment_linked_hierarchy model is a tool that provides a comprehensive view of student enrollment data for linked course sections. It links students to their respective courses, majors, and universities, and also provides personal details like names. This model can be used to analyze patterns in student enrollment for linked course sections, such as which majors or courses are most popular. It can also help identify correlations, such as if certain majors are more common at specific universities.

  • dw_user_major_enrollment_linked_hierarchy: The dw_user_major_enrollment_linked_hierarchy model is a tool that provides a comprehensive view of a student's academic journey with linked course sections. It links students' personal information, their major, and the courses they've taken, including co-curricular activities. This model can be used to track students' progress, identify patterns in course enrollment, and understand the impact of co-curricular activities on academic performance.

  • contacts: Defines contacts

    • Connects to different faculty activity type tables through object_type_id and object_type_name columns. object_type_name column includes constant values (TeachingAdvising, IntellectualWork, IntellectualContribution, StrategicPlan, ProfessionalCredentialsDevelopment, Program, etc) to define which table to connect to and object_type_id columns filters the rows

  • experience_type

  • dw_major_hierarchy: The dw_major_hierarchy model is a tool that organizes and presents data about students' academic paths. It provides a snapshot of each student's chosen major, degree, and emphasis, along with specific codes for each. This model can be used to track academic trends, identify popular fields of study, and understand individual student journeys. It groups data by term and user, offering a detailed view of academic choices over time. This can be useful for advising, curriculum planning, and strategic decision-making in an educational setting.