Skip to main content
Blackboard Help

Outcomes Goals Schema

This schema—not technically a star schema—provides data about Goals, who owns them, and how they are aligned to other Goals, and instruments (Artifacts, Portfolios, Course Evaluations, and Surveys).


Image illustrating associated text


ODS_ALIGNED_SOG_VW contains a row for each Goal alignment. This is the only view that is close to being a fact table so the descriptions have more detail.

Survey Answer Facts
Column Description Used to filter ODS_SURVEY_ANSWER_FACT_VW records by
pk1 Unique Identifier.  
title The Goal title. Personal Attributes such as gender, or zip code.
description The Goal description.  
owner_title The title of the place of the Goal. The place can be a Goal, a Unit, a Program, a Course, an Educational Experience, or an Improvement Project. The Survey, Course Evaluation or question that this response is answering.
type A=Improvement Project Objective
C=Course Goal
E=Educational Experience Objective
O=Unit Goal
P=Program Goal
In cases where a single student responds twice to the same deployment, the two sets of answers can be grouped by their response_pk1 values.
ip_pk1 Identifies the ods_ip_vw record of the Improvement Project that owns this Goal. This field is only non-null if the type=A. Improvement Project (IP) or Initiative (II) attributes.
ancestor_ou_pk1 Identifies the ods_ou_vw record for the deployment that caused the Survey or Course Evaluation being answered to be sent to the submitter. Deployment, or by the instrument being deployed. Only one instrument can be deployed at a time.
ancestor_ou_title The title of the Unit that ancestor_ou_pk1 refers to.  
program_pk1 If this Goal is a Program Goal (type=P) this will identify the ods_program_dim_vw record of the owning Program. Program attributes or the attributes of the Unit that owns the program.
ou_pk1 If this Goal is a Unit Goal (type=O), this will identify the ods_ou_vw record of the owning Program. Unit attributes.
course_pk1 If this Goal is a Course Objective (type=C), this will identify the owning Outcomes Course. clp_course_ext table columns.
ee_pk1 If the Course Evaluation or Survey was deployed to this submitter because of a Program affiliation, this column identifies the ods_program_vw record for that program. If this Goal is an Educational Experience Goal this will identify the Educational Experience.
std_category_pk1 If this Goal is a Goal, this will identify the owning Goal Category. Unit attributes.
status D=Draft
status_label A tag that can be used to retrieve an internationalized string for the status.  
aligned_sog_pk1 Identifies the Goal that this Goal is aligned to.  
direction O = Outbound - An alignment was created from this Goal (S1) to another Goal (S2). The result is S2 will display in the list of alignments for S1 as an Outbound Alignment.
I = Inbound - The converse of an Outbound and essentially an inferred alignment. In the same scenario as above, when viewing S2, S1 will display as an Inbound Alignment.

Sample Query

This query starts with an Improvement Initiative (II), selects all of its Improvement Projects (IPs) and all of their Improvement Project Objectives (IPOs). It also lists all of the Goals aligned to each IPO as well as the instruments that are mapped to each of those Goals.

SELECT ii.title ii_title, ip.title ip_title, ipo.title ipo_title,
                    alignment_direction,sog.title aligned_sog_title,
           instrument_name, instr.type instrument_type

FROM ods_ii_vw ii

                    LEFT OUTER JOIN ods_ip_vw ip on ip.ii_pk1 = ii.pk1

                    LEFT OUTER JOIN ods_sog_vw ipo on ipo.ip_pk1 = ip.pk1

                    LEFT OUTER JOIN ods_aligned_sog_vw sog on sog.aligned_sog_pk1 = ipo.pk1

                    LEFT OUTER JOIN ods_sog_instrument_vw si ON si.sog_pk1 = sog.pk1

                    INNER JOIN ods_instrument_dim_vw instr ON instr.pk1 = si.instrument_pk1

WHERE ii.pk1 = 1

ORDER BY ii.title, ip.title, ipo.title, sog.direction, sog.title, instr.type,