Outcomes Learning Standard - Course Objective Star Schema

The Learning Standard - Course Objective star schema provides information about learning standards, course objective, and alignments.

ODS_CONTENT_ALIGNMENT_FACT

ODS_CONTENT_ALIGNMENT_FACT contains a row for each alignment of a learning standard or course objective to any content item.

Fact table
Column Description
sog_pk1 Identifies the clp_sog record, by sog_pk1 join with ods_learning_standard.sog_pk1 to get all of attributes of one standard.
course_content_pk1 Identifies the content within a learning system course, by course_content_pk1, join with ods_course_content to get the detail information of one content aligned.
content_alignment_pk1 Not used. It is present to facilitate delta processing.

ODS_COURSE_SOG_FACT

ODS_COURSE_SOG_FACT contains a row for each association between a standard (learning standard or course objective) and a learning system course that should address that standard.

Fact table
Column Description
sog_pk1 Identifies the clp_sog record, by sog_pk1 join with ods_k12_standard.sog_pk1 to get all of attributes of one standard.
ls_course_pk1 Identifies the learning system course, by ls_course_pk1, join with ods_ls_course to get all of detailed information of each course.

ODS_COURSE_CONTENT

ODS_COURSE_CONTENT contains a row for each piece of alignable content within a course.

Fact table
Column Description
pk1 Uniquely identifies the piece of alignable content.
content_name Name of the item.
content_type B=blog M=message thread F=forum G=gradebook C=course content T= test Q=question S=Survey J=Journal
content_owner Display the parent of the item as defined below:
  • Content Items (both gradable & non-gradable): Parent is the container—content area, folder, learning module or lesson plan—that contains the item
  • Tests: If the test is deployed in the course, the parent is the container—content area, folder, learning module or lesson plan—that contains the link to the test. If the test isn't deployed, the parent is the Test Manager
  • Surveys: If the survey is deployed in the course, the parent is the container—content area, folder, learning module or lesson plan—that contains the link to the survey. If the survey isn't deployed, the parent is the Survey Manager
  • Questions: Parent is the test, survey, or pool that owns the question
  • Grade Center items: Parent is the Grade Center
  • Discussion items: For forums, the parent is the discussion board; for threads the parent is the rorum
  • Blogs: Parent is the container—content area, folder, learning module or lesson plan—that contains the link to the blog
  • Journals: Parent is the container—content area, folder, learning module or lesson plan—that contains the link to the journal
content_description The description of this piece of content. If the piece is a question this will be the first 100 characters of the question text. If the question text contains html, the html will be stripped out and the first 100 non-html characters will be shown
gradable_ind Y if there there is a grade column associated with this item, N if not.
ls_course_pk1 Identifies the learning system course that this piece of content belongs to.
type_label The label for this content item's type.
question_type If this is a question, this column is the label for the question type. If it's not a question, this column's value will be null.

ODS_LS_COURSE

ODS_LS_COURSE contains a row for each learning system course.

Fact table
Column Description
pk1 Unique identifier.
course_name Name of the learning system course.
batch_uid Batch_uid of the learning system course.
available_ind

Indicates if the course is available or not.
term_pk1 The term that this course is created in. Can be blank.
node_pk1 The course's primary node.
organization_ind Indicates if this record is for an organization.

ODS_LEARNING_STANDARD

ODS_LEARNING_STANDARD contains a row for each learning standard.

Fact table
Column Description
sog_pk1 Identifies the goal.
standard_doc_number The number that the provider normally uses to identify this standard, such as 1.1.a. Normally only unique within a sub-document.
sub_doc_name The name given by the provider of the sub-doc that this standard belongs to, such as First Grade or Algebra I. Also known as grade level.
sub_doc_pk1 Uniquely identifies the sub doc.
standard_type A string that the provider uses to identify the type of standard, such as Indicator or Benchmark.
stem Provides context to the clp_sog.description for this standard. Consider a case where you have this standards sub-tree.

1. The child shall be able to do simple arithmetic
1.a Add numbers between 1 and 100.
1.b Subtract numbers between 1 and 100.

The stem for 1.a and 1.b would be "The child shall be able to do simple arithmetic."
grade_hi The upper grade range that this sub doc applies to. We use age based grade ranges so that we can numerically represent kindergarten as 5. Thus 1st grade corresponds to 6 and so on.
grade_lo The lower grade range that this sub doc applies to. We use age based grade ranges so that we can numerically represent kindergarten as 5. Thus 1st grade corresponds to 6 and so on.
document_type The type of of the standards document that this standard belong to, such as Assessment Standards.
document_type_pk1 Uniquely identifies the type of of the standards document that this standard belongs to.
discipline_code Code that identifies the discipline, such as MATH.
discipline_name Discipline name, such as Mathematics or Language Arts.
discipline_pk1 Uniquely identifies the document, such as California Math Assessment Standards for 1997, that this standard belongs to.
provider_name The name of the entity which owns the standard, normally a state or standards body, such as California.
provider_code A code which identifies the provider, such as CA.
provider_country The country of the provider, such as USA.
provider_pk1 Uniquely identifies the provider.
parent_sog_pk1 Identifies the owning clp_sog record if the goal is part of a hierarchical set of goals.
display_order Indicates the display order of standards within a sub document. The actual value has no significance. A java counter is used to assign these numbers in increments of 100 to standards as they are imported from the XML files. This allows them to display in the same order represented by the XML file. An increment is used because we may have to insert new standards during in-place updates.
description Contains the text of the standard.
title The learning system's title.
layer Identifies the depth of this goal within its hierarchical set. If it isn't part of such a set, will be null or 1. This is information which could be determined by walking the parent_sog_pk1 tree but was denormalized to make it conveniently display goals.
doc_status A=active, I=Inactive, R=In Review
doc_publication_date The date on which the provider published this document. Normally, only a year is provided, so it defaults to January 1 of the publication year.
doc_last_update_date The date of the last "in-place" update to this standards document. If there has been no "in-place" udpate, it should be same as the publication_date.
alignable_ind Indicates if this learning standard can be aligned to or not. Can be Y or N.
owner No longer used.
standard_set The name of the standard set that this standard belongs to. For K-12 standards, this is a concatenation of the provider code, discipline name, and publication date. For non K-12, it's just the discipline name.

Sample query

This query shows each standard associated with each course and the contents aligned with each standard.

select c.course_name, ls.standard_doc_number, ls.description, cc.content_name

from ods_content_alignment_fact caf, ods_learning_standard ls, ods_course_content cc, ods_ls_course c

where caf.sog_pk1=ls.sog_pk1

and caf.course_content_pk1=cc.pk1

and c.pk1 = cc.ls_course_pk1