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

Language