Data Collection Data Export | HelioCampus Assessment Management Knowledge Base

Data Collection Data Export Samples

dw_fact_form_item_content.csv

form_item_content_fact_id,parent_form_item_content_fact_id,form_item_option_dim_id,form_entry_id,response,document_id,data,response_hierarchy,attributes,print_order,print_header,data_collection_schedule_id,data_collection_id,data_collection_form_id,term_id,academic_year_id,comment
"87",,"72","189","Suzanne Program Chair",,,,,,,,,,,,
"88",,"73","189","1st AY",,,,,,,,,,,,
"89",,"74","189","2nd AY",,,,,,,,,,,,

dw_fact_form_item_content_practicum.csv

form_item_content_practicum_fact_id,form_item_option_dim_id,parent_form_item_content_fact_id,form_entry_id,response,document_id,data,comment,response_hierarchy,attributes,print_order,print_header,data_collection_schedule_id,data_collection_id,data_collection_form_id,term_id,academic_year_id
"10","133",,"24","Does Not Meet Requirements",,,,,,,,"1","2","1","1",
"11","137",,"24","Meets Requirements",,,,,,,,"1","2","1","1",
"12","141",,"24","Exceeds Requirements",,,,,,,,"1","2","1","1",

Data Collection Data Export Table Details

Core Tables:

dw_fact_form_item_content: Contains fact data related to data collection responses.
dw_fact_form_item_content_practicum: Contains fact data related to practicum responses.
data_collection: Contains data collection instance specific data like activation_date, start_date, end_date, status, form_template and workflow.
data_collection_task: Steps data of workflow.
data_collection_schedule: Contains data which triggers data collection instance creation.
data_collection_form: Has form related data including user or user role who submit the form and workflow step information if data collection has a workflow.
data_collection_assignment: Contains assigned object to data collection. Assigned objects could be program, college, department and course section etc.
data_collection_schedule_type: Describes data collections for specific purposes like new course or program proposal, clinical recommendation.
workflow: Has workflow information like name, is_library.
form_item: Stores questions related data collection form template; question text, question type and form template section that contains question.
form_item_option: Some of the question types like dropdown or multi choice answer have more than one options. This table stores options text.
form_item_type
form_type
syllabus_form_item_map: Mapping information of question and form template.
syllabus_section: Form template may contain more than one section. This table stores section header, section description and order.
syllabus_template: Data collection form template table. Contains template name, owner and status.
form_entry: Contains form submission details like form submission date, username and submitted flag.
form_item_comment: Stores user comment entered to the form.
form_item_content_history: Contains historical data of user responses.
dw_fact_form_item_content: Contains fact data related to data collection responses.
form_template_section: Contains template, name, section number and hierarchy.
form_item_content: Stores user data entered to the form.
dw_form_item_content_syllabus_fact
dw_data_collection_hierarchy: The dw_data_collection_hierarchy model is a tool that organizes and presents data related to academic activities. It tracks details like the academic year, course, department, and faculty activity. It also records when data was collected, who completed it, and whether it's confidential. This model can help in planning and monitoring academic activities, ensuring timely data collection, and maintaining confidentiality.
form_template
strategic_plan: Defines strategic plan.

strategic_plan_objective: Defines strategic plan objectives.

Sample Data Collection Data Export Queries

form_query.sql manager_query.sql

select
  fact.form_entry_id
  , dch.data_collection_form_id
  , p.program_id
  , p.name as ProgramName
  , d.department_id
  , d.name as DepartmentName
  , c.college_id
  , c.name as CollegeName
  , sec.form_template_section_dim_id
  , sec.form_template_section_name
  , sec.section_hierarchy
  , item.sort_order
  , item.form_item_dim_id
  , item.form_item_description
  , item.form_item_name
  , fact.response
  , fact.data
  , fact.response_hierarchy
  , item.form_item_type_code
  , sec.form_template_id
  , sec.form_template_section_number as section_number
  , course.name as course_name
  , course.code as course_code
  , dch.course_section_id as course_section_id
  , cs.name as course_section_name
  , cs.code as course_section_code
  , CASE
  WHEN COALESCE(fact.print_header, 'true') THEN 1
  ELSE 0 END
  as print_header
  , fact.document_id as document_id
  , CASE
  WHEN dch.workflow_id is null THEN fe.submitted_flag
  ELSE dcf.workflow_completed
  END as form_completed
  -- fact and hierarchy
from dw_fact_form_item_content fact
  inner join dw_data_collection_hierarchy dch on dch.form_entry_id =
  fact.form_entry_id
  -- data_collection related dimensions
  inner join form_item_option opt on opt.form_item_option_dim_id =
  fact.form_item_option_dim_id
  inner join form_item item on item.form_item_dim_id = opt.form_item_dim_id
  inner join form_template_section sec on sec.form_template_section_dim_id =
  item.form_template_section_dim_id
  inner join form_entry fe on fe.form_entry_id= fact.form_entry_id
  inner join data_collection_form dcf on dcf.data_collection_form_id =
  dch.data_collection_form_id
  -- common dimensions
  left join program p on p.program_id = dch.program_id
  left join department d on d.department_id = dch.department_id
  left join college c on c.college_id = dch.college_id
  left join course on dch.course_id = course.course_id
  left join course_section cs on dch.course_section_id = cs.course_section_id
  left join term ayTerm on ayTerm.term_id = dch.term_id
where
  dch.data_collection_id = 6
order by fact.form_item_content_fact_id

manager_query.sql : All the data seen on data collection home page returned from this query

form_query.sql : all the form items, user inputs and other attributes from common dimensions returned by this query.

Relational Data Form Item

Relational Data is a nested group of related questions that can be organized into a hierarchy via XML. Once set up by the Client Success Data Collection Designer, the users can answer the multiple standardized questions for a particular parent question (typically Measure or Outcome) and then press "Add Another" to duplicate the standardized questions into a second grouping.

Through this form item, we can support Multi Choice Single Answer, Text Memo, Single Line Text, HTML, Dropdown select, Document Upload, and Table questions. Additionally, setting questions as required/optional for 1+ steps in the workflow is an available option.

Sample form with Relational Data Form Item

Check below xml file and screenshots to see how relational data form is defined, rendered and queried.

relational_data_simple_x…

Sample form with Relational Data Form Item - Advanced Example

Sample relational data form item is created with this xml file. See comments on each item on how it works.

relational_data_xml.xml

Below form is created by this xml file

Below screenshot is from form_query.sql executed for this form. section_hierarchy column has value for relational data items. In this case 36 is the top most item. Parent-child relationship can be resolved with the help of section_hierarchy column. response and data columns have user inputs.