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).
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.
|Column||Description||Used to filter ODS_SURVEY_ANSWER_FACT_VW records by|
|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 |
E=Educational Experience Objective
|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_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.
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,
instr.name 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, instr.name