Overview of Data Relationship
Diagram of Relationship Mapping
Image Added
Overview of Data Model
Image Added
Tables highlighted in yellow are visible in the PowerBI dataset. The tables not highlighted are the bridge tables (created for the purpose of not dropping data for the many-to-many relationships between some of the tables, but NOT needed for reporting the data, and thus hidden in the model).
Mapping for the Data Model
EPAs- Entrustable Professional Activity. Mapped to Program Objective.
Program Objective - The program objective is mapped to a course objective and then the pair is mapped to a course. This is a pick list of objectives provided by AAMC. Mapping is completed by Course Coordinator or Course Director. Mapped to course objectives.
Course Objective - The program objective is mapped to a course objective and then the pair is mapped to a course. Mapping is completed by Course Coordinator or Course Director. Mapped to courses and session objective.
Session Objective - The session objective is mapped to a course objective and then the pair is mapped to a session. Mapping is completed by Course Coordinator or Course Director. Mapped to sessions.
Content Objective - Objectives given by Content Area national organizations (like the American Association of Pediatrics). Integration directors map them to individual sessions. Mapped to sessions
Sessions are also mapped to Courses.
Methods - If methods needed for course objective use table named Methods to Course Objectives. Mapped to course objectives; If methods are needed for sessions use the table named Methods. Mapped to sessions.
Template Description
The template is designed for the self-service creation of Power BI Report development Reports for future Curriculum Mapping reports. Currently the Template can be located in Box and at \\usffs.forest.usf.edu\odrive$\IT-BIA\Projects\PowerBI\Templates by the name of "Self-Service Curriculum Mapping Template" is accessible once given access to the Box folder. The Template contains all the currently mapped data and details needed for Curriculum Mapping reports. The template contains the first tab includes core filters used in all Curriculum Mapping reporting along with a , while the Relationship documentation tab that provides information for the data fields that can be used in a report (which should be deleted . Please delete this tab before a created report is published)shared with others.
Filters
...
...
| Academic Year - MCOM CM |
---|
nopanel | true |
---|
|
Insert excerpt |
---|
| Med Year - MCOM CM |
---|
| Med Year - MCOM CM |
---|
nopanel | true |
---|
|
Insert excerpt |
---|
| Curriculum Program Filter- MCOM CM |
---|
| Curriculum Program Filter- MCOM CM |
---|
nopanel | true |
---|
|
Table Details
Tables and description in grey italics are not displayed in 'FIELDS' in PowerBI template, however, table can be made to be visible in 'Relationships' by right-clicking on grey table (tables in lighter grey are currently visible under FIELDS).
...
the Power BI template. There is no additional data in those tables except how items are mapped to one another.
TABLE NAME | TABLE DESCRIPTION |
---|
Content Objective | Contains Content Objectives with description and discipline, linked to Courses from the Course by Academic Year table. Table is filtered for active records. |
Course by Academic Year | Contains Course attributes by academic year. Table is filtered by Course Status = 2 ('Completed'), and Source = 'Health' which brings in only Health courses. |
Course by Academic Year Program | Contains mapping between course_by_academic_year_id and program. Courses linked to both Core and SELECT programs will have two separate records in the table. This table was created to help report writers with the program button functionality on the report. |
Course Objective | Course objective description and the course objective ID. |
EPAs | Contains EPA (Entrustable Professional Activities) information. |
EPA by Progam Objective | Contains mapping between EPA and Porgram Objective table; linked by EPA ID. |
Keyword Hierarchy | Contains Keyword information for keyword search. |
Methods | Contains method information (for sessions) with method type description noting Primary Instructional Method, Additional Instructional Method, or Assessment. |
Methods to Course Objectives | Contains method information (for course objectives), with method type description noting Primary Instructional Method, Additional Instructional Method, or Assessment |
. |
Program Objective Course Objective | Bridge table to handle many to many relationship between program objective and course objective. |
Program Objective | Contains Program Objectives. |
Session | Contains Sessions, linked to Courses from the Course By Academic Year table. Table is filtered by Status = 2 (Completed). |
Session Objective Course Objective | Bridge table to handle many to many relationship between session objective and course objective. |
Session Objective | Contains session objectives, linked to Sessions from the CM_SESSION table. |
Column Details
...
_NAMECOLUMN____0Course by Academic Year | Course Location | when location = 1, 'Lehigh Valley' when location = 2, 'Tampa' Else 'Unknown' | Unknown | (derived) Appian.CR_REC_courseByAcademicYear.location |
Course by Academic Year | Course is Core Flag | Yes' if the course belongs to either 'Core' or 'Core and SELECT', else 'No' | No | (derived) Appian.CR_REC_courseByAcademicYear.program |
Course by Academic Year | Course is SELECT Flag | Yes' if the course belongs to either 'SELECT' or 'Core and SELECT', else 'No' | No | (derived) Appian.CR_REC_courseByAcademicYear.program |
Course by Academic Year | Course Academic Year | course academic year | UNKN | Appian.CR_REC_courseByAcademicYear.academicYear |
Course by Academic Year | Course Contact Hours | course contact hours | Content Objective | Content Objective | Content objective with number followed by description (e.g. 1.17.8 Predict the changes...) | Unknown | Appian.MCOM_CM_contentObjectives.DESCRIPTION |
Content Objective Discipline | Content Objective | Discipline or content area of a given content objective (e.g. Pharmacology, Neuroscience, Biochemistry) | Unknown | Appian.MCOM_CM_REF_disciplineDirector.DESCRIPTION |
Content Objective Heading | Content Objective
| Content objective heading description. Each heading will have subheadings up to level five. | Unknown | Appian.MCOM_CM_contentObjectiveHeading.DESCRIPTION |
Content Heading Objective ID | Content Objective | Archivum content objective heading foreign key. | -1 | Appian.MCOM_CM_contentObjective.HEADERID |
Content Objective ID | Content Objective | Archivum content objective unique identifier. | (not null) | Appian.MCOM_CM_contentObjectiveHeading.DESCRIPTION |
Course Academic Year | Course by Academic Year | Academic year for the course, where the year is the year the academic year began. | UNKN | Appian.CR_REC_courseByAcademicYear. |
contactHoursacademicYear |
Course by Academic Year ID | Course by Academic Year |
IDappian primay primary key for Course By Academic Year | (not null) | Appian.CR_REC_courseByAcademicYear.courseAcademicYearId |
Course Contact Hours | Course by Academic Year | Course |
Numbercourse number | Unknown | contact hours, pulled from Health Banner into Archivum | 0 | Appian.CR_REC_courseByAcademicYear. |
courseNumbercontactHours |
Course Description | Course by Academic Year | Course |
Descriptioncourse description. blanks ltrimed and rtrimeddescription as entered by course director/coordinator in Archivum | Unknown | Appian.CR_REC_courseByAcademicYear.description |
Course End Date | Course by Academic Year | Course |
End Datecourse end date as pulled from Health Banner into Archivum. | Date in Power BI will display with Day, Month, Year, and Quarter which can be removed in a table | Appian.CR_REC_courseByAcademicYear.endDate |
Course is Core Flag | Course by Academic Year | Yes if the course belongs to either Core or Core and SELECT, else No as entered into Archivum. | No | (derived) Appian.CR_REC_courseByAcademicYear.program |
Course Is Outside Banner Flag |
course
| Course by Academic Year | Course is outside |
banner Health Banner flag. Yes if |
outsideBanner=1the course is not contained in Health Banner, Else No. | No | Appian.CR_REC_courseByAcademicYear.outsideBanner |
Course is SELECT Flag | Course by Academic Year | Yes if the course belongs to either SELECT or Core and SELECT, else No as entered into Archivum. | No | (derived) Appian.CR_REC_courseByAcademicYear.program |
Course Location | Course by Academic Year | Location as entered in Archivum; Lehigh Valley or Tampa. | Unknown | (derived) Appian.CR_REC_courseByAcademicYear.location |
Course Med Year | Course by Academic Year | Course med year number taught in as entered in Archivum by course director/coordinator. | 0 | Appian.CR_REC_courseByAcademicYear.year |
Course Number | Course by Academic Year | Course number as pulled from Health Banner into Archivum | Unknown | Appian.CR_REC_courseByAcademicYear.courseNumber |
Course Start Date |
course Course by Academic Year | Course start date as pulled from Health Banner into Archivum. | Date in Power BI will display with Day, Month, Year, and Quarter which can be removed in a table | Appian.CR_REC_courseByAcademicYear.startdate |
Course Subject Code | Course by Academic Year | Course |
Subject Codecourse subject code as pulled from Health Banner into Archivum. | Unknown | Appian.CR_REC_courseByAcademicYear.subjectCode |
Course Title | Course by Academic Year | Course |
Titlecourse title. blanks ltrimed and rtrimedtitle as pulled from Health Banner into Archivum. | Unknown | Appian.CR_REC_courseByAcademicYear.title |
Course by Academic Year ID | Course |
Med YearBy Academic Program | Archivum course |
year number taught in0by academic year foreign key | -1 | Appian.CR_REC_courseByAcademicYear. |
yearCourse ObjectivecourseAcademicYearId |
Curriculum Program | Course By Academic Program
| Can be 'Core' or 'SELECT'. If a course is taught to 'Core' and 'SELECT', there are separate records for each curriculum program/course combination. | -1 | (derived) Appian.CR_REC_courseByAcademicYear.program |
Course by Academic Year ID |
appian Course Objective | Archivum foreign key for Course By Academic Year | (not null) | Appian.MCOM_CM_objectivePair.courseId |
Course Objective | Course Objective |
course Course objective description |
. blanks ltrimed and rtrimedas entered in Archivum by course director/coordinator. | Unknown | Appian.MCOM_CM_objectivePair.courseobjective |
Course Objective ID | Course Objective |
IDappian Archivum course objective |
primary keyCourse By Academic Program
Curriculum Program | Can be 'Core' or 'SELECT'. If program is 1 then both 'Core' and 'SELECT' (separate records). If program is 2 then 'Core'. If program is 3 then 'SELECT'EPA Description | EPA | Description of EPA (exteral professional activity) (e.g. Gather a history and perform a physical examination). | text | Appian.MCOM_CM_objectivePair.epadescription |
EPA ID | EPA | Archivum course objective unique identifier. | (not null) | Appian.MCOM_CM_objectivePair.epaid |
EPA Title | EPA | Identifier for EPAs, (e.g. EPA 1, EPA 2, SEL EPA 1, SEL EPA 2)/ | text | Appian.MCOM_CM_EPA.epa_title |
Keyword | Keyword Hierarchy | Ilios term/keyword | Unknown | ILIOS.term.title |
Keyword Description | Keyword Hierarchy | Ilios term/keyword level (not consistently populated). Does not contain a keyword description. | Unknown | ILIOS.term.description |
Keyword ID | Keyword Hierarchy | Ilios term/keyword unique identifier | (not null) | ILIOS.term.term_id |
Keyword ID Level 1 (Level 2, Level 3, Level 4, Leve5) | Keyword Hierarchy |
|
|
|
Keyword Level | Keyword Hierarchy | Keyword hierarchy level | -1 | (derived) |
AppianCR_REC_courseByAcademicYear.programCourse By Academic Program | Course by Academic Year ID | appian course by academic year foreign key | -1 | Appian.CR_REC_courseByAcademicYear.courseAcademicYearId |
CM_PROGRAM_OBJ_COURSE_OBJ | Course Objective ID | appian course objective foreign keyterm_id, ILIOS.term.parent_term.id |
Keyword Level 1 (Level 2, Level 3, Level 4, Level 5) | Keyword Hierarchy |
|
|
|
Keyword Level Hierarchy | Keyword Hierarchy |
|
|
|
Parent Keyword ID | Keyword Hierarchy | Ilios term/keyword parent key. Keywords have a hierarchy. | -1 | ILIOS.term.parent_term_id |
School ID | Keyword Hierarchy | School unique identifier value from Ilios. | -1 | ILIOS.vocabulary.school_id |
School Title | Keyword Hierarchy | School description from Ilios. | Unknown | ILIOS.school.title |
Vocabulary ID | Keyword Hierarchy | Ilios vocabulary unique identifier. Joins to the Ilios vocabulary table. | -1 | ILIOS.term.vocabulary_id |
Vocabulary Title | Keyword Hierarchy | Vocabulary description value from Ilios. | Unknown | ILIOS.vocabulary.title |
Method Description | Methods | Description of type of method | Unknown | Appian_MCOM_CM_METHODS.METHOD |
Method Code | Methods | Alphanumeric as determined by Ilios (e.g IM001) | Unknown | Appian_MCOM_CM_methods.METHOD |
Method ID | Methods | Archivum method unique identifier | (not null) | Appian_MCOM_CM_methods.METHODID |
Method Type | Methods | Primary Instructional Method; Assessment Method | Unknown | (derived) Appian_MCOM_CM_methods.PRIMARY and Appian_MCOM_CM_methods.METHODTYPE |
Method Type Code | Methods | IM = Primary Instructional Method; AM = Assessment Method | U | Appian_MCOM_CM_methods.METHODTYPE |
Primary Method Flag | Methods | Yes for Primary Instructional Method; No for Assessment Method | Unknown | Appian_MCOM_CM_methods.PRIMARY |
Method Description | Methods to Course Objectives | Description of type of method | Unknown | Appian_MCOM_CM_METHODS.METHOD |
Method Code | Methods to Course Objectives | Alphanumeric as determined by Ilios (e.g IM001) | Unknown | Appian_MCOM_CM_methods.METHOD |
Method ID | Methods to Course Objectives | Archivum method unique identifier | (not null) | Appian_MCOM_CM_methods.METHODID |
Method Type | Methods to Course Objectives | Primary Instructional Method; Assessment Method | Unknown | (derived) Appian_MCOM_CM_ |
objectivePairProgramObjectiveJoin.objectivePairIdCM_PROGRAM_OBJ_COURSE_OBJ | Program Objective ID | appian program methods.PRIMARY and Appian_MCOM_CM_methods.METHODTYPE |
Method Type Code | Methods to Course Objectives | IM = Primary Instructional Method; AM = Assessment Method | U | Appian_MCOM_CM_methods.METHODTYPE |
Primary Method Flag | Methods to Course Objectives | Yes for Primary Instructional Method; No for Assessment Method | Unknown | Appian_MCOM_CM_methods.PRIMARY |
Course Objective ID | Program Objective Course Objective | Archivum course objective foreign key | (not null) | Appian.MCOM_CM_objectivePairProgramObjectiveJoin. |
programObjectiveIdobjectivePairId |
Program Objective ID | Program Objective Course Objective |
IDappian Archivum program objective |
primary foreign key | (not null) | Appian.MCOM_CM_ |
programObjectiveobjectivePairProgramObjectiveJoin.programObjectiveId |
Program Objective Title | Program Objective |
Titleprogram . blanks ltrimed and rtrimed | Unknown | Appian.MCOM_CM_programObjective.title |
Session |
Program Objective ID | Program Objective | Archivum program objective unique identifier | (not null) | Appian.MCOM_CM_programObjective.programObjectiveId |
Course by Academic ID |
appian foreign key for | Session | Archivum Course By Academic Year unique identifier | (not null) | Appian.MCOM_CM_session.courseid |
Session | Session |
Descriptionsession description. blanks ltrimed and rtrimed | Session title as entered in Archivum by course director/coordinator | Unknown | Appian.MCOM_CM_session.title |
Session Description | Session | Session description as entered in Archivum by course director/coordinator | Unknown | Appian.MCOM_CM_session.description |
Session Duration Hours | Session |
Duration Hourssession Session duration hours as entered in Archivum by course director/coordinator | 0 | Appian.MCOM_CM_session.duration |
Session ID | Session |
IDappian primary key Preparation Hourssession UnknownSession student preparation hours as entered in Archivum by course director/coordinator | 0 | Appian.MCOM_CM_session.preparationHours |
Session Total Hours | Session |
Session title; blanks ltrimed and rtrimed | Total hours, combining session duration hours and session preparation hours | 0 | Appian.MCOM_CM_session. |
titleCM_SESSION_OBJ_COURSE_OBJ | Course Objective ID | appian totalduration |
Course Objective ID | Session Objective Course Objective | Archivum course objective foreign key | (not null) | Appian.MCOM_CM_sessionObjectivePairCourseObjectiveJoin.courseObjectiveId |
CM_SESSION_OBJ_COURSE_OBJSession Objective ID | Session Objective |
IDappian Archivum session objective foreign key | (not null) | Appian.MCOM_CM_sessionObjectivePairCourseObjectiveJoin.sessionObjectiveId |
Session |
Objective IDappian session objective primary keyArchivum session unique identifier | (not null) | Appian.MCOM_CM_sessionObjectivePair. |
idsessionid |
Session Objective | Session |
IDappian session foreign key | (not null)Objective | Session objective name as entered in Archivum by course director/coordinator | Unknown | Appian.MCOM_CM_sessionObjectivePair. |
sessionidsessionobjective |
Session Objective ID | Session Objective | Archivum session objective |
name. blanks ltrimed and rtrimedUnknownsessionobjective ...
title | CM_COURSE_BY_ACADEMIC_YEAR Copy |
---|
Currently within the data model there are 2 tables that contain the same data, the table is CM_COURSE_BY_ACADEMIC_YEAR.
The two tables provide ways to access data point(s) differently due to how objectives are mapped to each other in the source. The path you take depends on what question is been asked with the data.
Not all course objectives are always mapped to a session objective and hence a session. Keeping that in mind here are the possible scenarios:
To get all connected data points here is the path that needs to be followed:
Content Objective Subheadings <- Content Objective <-Course -> Session -> Session Objective -> Course Objective -> Program Objective
To get all the course objectives connected to a course (not necessarily mapped to a session objective), and program objectives connected to those course objectives, here is the path that needs to followed:
...
Report Tips
- In addition to the pre-made filters mentioned at the top of the page (Academic Year, Med Year, and Curriculum Program), report level filters can be set, e.g. "Course Academic Year" is not blank, or "Program Objective" is not blank.
- It may be needed at times to obtain a single line for an item (when using a table in the report) or when used as a measure to provide a count, to select "Count Distinct," such as for SessionID.
- For Session Duration Hours or Session Preparation Hours, select "Don't Summarize" instead of "Count(Distinct)" to obtain correct hour count for sessions.
Videos
Overview for CM documentation: CM Documentation Overview Video
Overview for data model for PowerBI reports: CM Data Model Video
Data Refreshed On
Data in this report is not live, but refreshed regularly. The date on the bottom right of the report displays the last day the data was refreshed from Archivum Curriculum Mapping.
Disclaimer
This report only displays values as the were in Archivum Curriculum Mapping at last refresh as indicated at the bottom of the report. Those values are subject to change over time. Unauthorized use of this report is prohibited. If you log on to this computer system, you acknowledge your awareness of and concurrence with the University of South Florida policies. The University will prosecute violators to the full extent of the law.