AEFIS DW Interface 1.0
Introduction
AEFIS Data Warehouse Interface (“DW Interface”) provides access to the OLAP-ready version of your AEFIS data to import into your data warehouse. Once AEFIS DW Interface is implemented, you can use AEFIS 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. AEFIS provides a data warehouse-ready data model, the model we use in our internal reporting, to customers, and updates the data with 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 AEFIS DW Interface. If you want to use the AEFIS DW Interface for a specific use-case, please contact [email protected].
Transfer of Data
Once you sign up with AEFIS DW Interface, AEFIS will provide you access to our Secure FTP (SFTP) server, where a daily snapshot of your data will be available. The data will be provided as CSV files compressed into a ZIP file. 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 from AEFIS, we can work with you to do one of the following:
- Make the data available in your existing AEFIS SFTP account, where your data feed is uploaded to AEFIS.
- If you choose this option we will create a folder at the root of your existing account called aefis_dw_interface where you can download the data.
- If you do not have an existing AEFIS SFTP account, we work with you to create one for you to make the data available for download.
AEFIS DW Interface Format and Samples
Each CSV file is encoded with Unicode (UTF-8) encoding and is compatible with RFC 4180 standards namely:
- MS-DOS-style lines that end with (CR/LF) characters
- A header record
- NULL fields are not omitted. Each line has the same number of fields
- Every field is quoted with double quotes (“)
- Double quotes in field values are represented by two consequent double-quotes.
- For datetime fields, the UTC format is used. (yyyy-MM-ddTHH:mm:ss±hhmm)
dw_course_hierarchy.csv
| 1 2 3 4 | “institution_id”,”institution_code”,”institution_name”,”institution_description”,”college_id”,”college_code”,”college_name”,”college_description”,”department_id”,”department_code”,”department_name”,”course_id”,”course_code”,”course_name”,”course_description”,”course_version_uuid”,”course_section_id”,”course_section_code”,”course_section_name”,”course_section_section” “1”,”AU”,”AEFIS University”,”AEFIS University opened its doors in 2012 and is one of the country’s top higher education institution. “,”2″,”SoE”,”School of Education”,”School of Education”,”2″,”DTU”,”Department of Teacher Education”,”2″,”BUS”,”Fundamentals of Business”,”Fundamentals of Business”,”2121306D-F670-454B-BA14B46637A53B89″,”1″,”BUS” “101 1″,”Fundamentals of Business”,”1″ “1”,”AU”,”AEFIS University”,”AEFIS University opened its doors in 2012 and is one of the country’s top higher education institution. “,”2″,”SoE”,”School of Education”,”School of Education”,”2″,”DTU”,”Department of Teacher Education”,”2″,”BUS”,”Fundamentals of Business”,”Fundamentals of Business”,”2121306D-F670-454B-BA14B46637A53B89″,”29″,”BUS” “101 1″,”Fundamentals of Business”,”1″ “1”,”AU”,”AEFIS University”,”AEFIS University opened its doors in 2012 and is one of the country’s top higher education institution. “,”2″,”SoE”,”School of Education”,”School of Education”,”2″,”DTU”,”Department of Teacher Education”,”2″,”BUS”,”Fundamentals of Business”,”Fundamentals of Business”,”2121306D-F670-454B-BA14B46637A53B89″,”59″,”BUS” “101 1″,”Fundamentals of Business”,”1″ |
dw_fact_assessment.csv
| 1 2 3 4 5 | “assessment_detail_response_id”,”assessment_mapping_response_id”,”assessment_syllabus_id”,”course_section_id”,”program_map_id”,”term_id”,”course_user_major_id”,”user_id”,”concentration_id”,”program_id”,”program_version_uuid”,”program_user_major_id”,”response”,”expected_percentage”,”expected_level”,”performance_indicator_rubric_id”,”performance_indicator_rubric_name”,”performance_indicator_rubric_description”,”program_rubric_id”,”rubric_level”,”success” “509”,”66″,”3″,”24417″,”69″,”25″,,”23092″,,”6″,”7367C24B-833C-48A0-9D57F3918329CF0E”,,”1″,”80″,”2″,”542″,”Meets Expectations”,”Meets needs for interaction.”,”23″,”2″,”True” “512”,”66″,”3″,”24417″,”69″,”25″,,”18249″,,”6″,”7367C24B-833C-48A0-9D57F3918329CF0E”,,”1″,”80″,”2″,”542″,”Meets Expectations”,”Meets needs for interaction.”,”23″,”2″,”True” “514”,”66″,”3″,”24417″,”69″,”25″,,”19839″,,”6″,”7367C24B-833C-48A0-9D57F3918329CF0E”,,”1″,”80″,”2″,”542″,”Meets Expectations”,”Meets needs for interaction.”,”23″,”2″,”True” “515”,”66″,”3″,”24417″,”69″,”25″,,”24331″,,”6″,”7367C24B-833C-48A0-9D57F3918329CF0E”,,”1″,”80″,”2″,”542″,”Meets Expectations”,”Meets needs for interaction.”,”23″,”2″,”True” |
AEFIS Data Warehouse Introduction
This section describes general data warehouse concepts and types of objects in the AEFIS 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. Fact tables in AEFIS Data Warehouse ER Diagrams are color-coded with a green header.
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. Hierarchy tables in AEFIS Data warehouse ER Diagrams are color-coded with a red header. 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. These types of hierarchy tables are color-coded with a violet header.
Links between tables
Links between tables represent join conditions used to join related tables. The link would show the related column in each table that could be used for the relation. Links marked with (*) should be joined in a way that matches null values (null inclusive joins).
At this time only AEFIS assessment data is available for access using the AEFIS DW Interface.
AEFIS Assessment Data Warehouse Design
These tables are used to report assessment data, including CLO Assessments and Program Outcome Assessments including assessment results, assessment assignment linking information and curriculum mapping information.
Tables:
- dw_fact_assessment: Contains fact data about assessment results for courses linked to a program. This table is ONLY used for assessment data when courses are part of a program thus relates to curriculum mapping information.
- dw_fact_course_assessment: Contains fact data about course assessments when the course is not part of a program. This can be used for CLO Assessment reporting only.
- dw_fact_assignment_submission: Contains fact data about assignment submissions and grades.
- dw_fact_assignment_rubric: Contains fact data about assignments with rubric details. An assignment with rubrics will have the rubric information in this table.
- dw_organizational_hierarchy: Defines organizational hierarchy as Program ⇒ Department ⇒ College ⇒ Institution
- dw_term_hierarchy: Defines term hierarchy as Term ⇒ Academic Year
- dw_course_section_hierarchy: Defines Course Section Hierarchy as Course Section ⇒ Course
- dw_clo_rubric_hierarchy: Defines Course Learning Objective Hierarchy as Course Rubric ⇒ Course Learning Objective ⇒ Course Learning Objective Category.
- dw_course_hierarchy: Defines Course Hierarchy as Course Section ⇒ Course ⇒ Department ⇒ College ⇒ Institution
- dw_assignment_hierarchy: Defines Assignment Hierarchy as Assignment ⇒ Assignment Template
- dw_assignment_rubric_hierarchy: Defines Assignment Rubric Hierarchy as Rubric ⇒ Assignment ⇒ Assignment Template
- dw_course_section_major_hierarchy – dw_course_section_no_major_hierarchy: This pair implements an interchangeable hierarchy dw_course_section_major_hiearachy includes major level information but even though it has the related columns dw_course_section_no_major_hierarchy does not have major level data thus it can be used when major information is irrelevant.
- Defines Course Section Major Hierarchy as Major ⇒ Course Section
- dw_user_major_enrollment_hierarchy – dw_user_enrollment_hierarchy: Similar to the above, this pair also will be used depending on the need for major information; dw_user_major_enrollment_hierarchy includes major level information and dw_user_enrollment_hierarchy does not have that data.
- Defines User Major Enrollment Hierarchy as Major ⇒ User ⇒ Course Section.
- dw_mapping_hierarchy: Contains information about outcome mappings. There are four types of mappings
- course <-> outcome: Course to outcome mapping
- course <-> PI: course to performance indicator mapping
- CLO <-> outcome: course learning objective to outcome mapping
- CLO <-> PI: course learning objective to performance indicator mapping
- Depending on the value of the mapping_type column, related columns will contain values accordingly and non-related columns will be null.
AEFIS Data Collection and Workflow Data Warehouse Design
These tables are used to report data collection data.
Tables:
- dw_fact_form_item_content: Contains fact data about assessment results for courses linked to a program. This table is ONLY used for assessment data when courses are part of a program thus relates to curriculum mapping information.
- form_item_content_history: Contains fact data about course assessments when the course is not part of a program. This can be used for CLO Assessment reporting only.
- data_collection_form:
- form_item_option: Contains fact data about assignment submissions and grades.
- form_item: Contains fact data about assignments with rubric details. An assignment with rubrics will have the rubric information in this table.
- form_template_section: Defines organizational hierarchy as Program ⇒ Department ⇒ College ⇒ Institution
- dw_term_hierarchy: Defines term hierarchy as Term ⇒ Academic Year
- college: Defines colleges
- data_collection_schedule: Defines Course Learning Objective Hierarchy as Course Rubric ⇒ Course Learning Objective ⇒ Course Learning Objective Category.
- department: Defines departments
- program: Defines programs
- data_collection: Defines Assignment Rubric Hierarchy as Rubric ⇒ Assignment ⇒ Assignment Template
- course – Defines courses
- dw_data_collection_hierarchy : Similar to the above, this pair also will be used depending on the need for major information; dw_user_major_enrollment_hierarchy includes major level information and dw_user_enrollment_hierarchy does not have that data.
