STARS Cube DemonstrationPED Data Conference

Fall 2007

• Understand what cubes are and how they are different than traditional reports

• Understand basic cube functionality• Understand the data in the staff-related

cubes• Be able to use the staff-related cubes to

generate own queries and analyses

Session Objectives

• Cubes contain pre-summarized subject-oriented data– Dimensions– Measures

• Cubes are dynamic and powerful– “Drill down”– “Slice and dice”– Filter data– Export data– Create charts

• Similar to Microsoft Excel pivot tables • Accessed through Cognos• Cubes are accessible to both state and district level users• PED cubes only contain aggregated data; individual staff

or student data is available in “Drill Through” reports

What are Cubes?

Cubes vs. Traditional ReportsReports Cubes

Timeliness Contain “real time” data

Data in cubes is updated weekly (every Sunday)

Flexibility Static Dynamic

Data Security User can only view data for their district/school

User can view aggregated data for all districts and schools; detailed data available through drill through reports where district/school level security is applied

Use Management/ statistical reporting

Reporting and Analysis

STARS Technical Overview

STARS Data

Warehouse

Student Snapshot Cube

Staff Snapshot Cube

Assessment Cube

District Student

Information System

Data Extracts

District PED

• MEASURE - a discrete, quantifiable data element, often preceded by “count” or “number of”– Number of Students– Number of Classes

• DIMENSION - a qualitative column or row header that describes a measure, answers “when, where, why, and who” and is often preceded by the word “by”– By District– By Staff Gender

• HIERARCHY - represent increasing level of detail available within each dimension

Terms

DistrictDistrict

AgeAge

Age RangeAge Range

LocationLocation

Terms (cont’d)

• OLAP – On-Line Analytical Processing – a method of analyzing data by applying and removing dimension hierarchies

• “CUBE VIEWS” or REPORTS - have combinations of measures and dimensions that answer business questions

A Question...

What was the total number of teachers by district by

qualification status for school year 2007?

Measure:• # of Staff

Dimensions:• District• School Year• Staff Qualification Status

• Assessment– NMSBA results at the subtest, cluster and skill levels

• District/Location• Programs Fact

– Students programs such as BEP, T1A, T1C, 3Y, 4Y, etc.

• School Enrollment• Special Education Snapshot• Staff Assignment• Staff Licenses

– Based on AS/400 licensure system; contains current licenses for staff members whether or not they’ve been reported to STARS

• Staff Snapshot• Student Course Enrollment

– Classes, the students enrolled in classes and staff teaching them

• Student Snapshot

What Cubes are Available?

Staff Snapshot Cube

• Based on the Staff Snapshot table

• Provides duplicated and unduplicated counts of staff members, their average annual salary and average years of experience by various dimensions

• Grain: One record per district/staff/report period

Staff Assignment Cube

• Based on the Staff Assignment table• Provides unduplicated counts of staff members,

a count of the number of assignments and other staff-related measures by various dimensions

• Also contains staff demographic dimensions sourced from the Staff Snapshot table– A Staff Assignment will not appear in this cube if there

is no corresponding Staff Snapshot record for the individual and report period in question

• Grain: One record per district/staff assignment/report period

Staff Licenses Cube

• Based on the PED Licensure data that is imported into STARS

• Provides unduplicated counts of staff members, as well as duplicated and unduplicated counts of licenses by various dimensions

• Grain: One record per district/location/license type/license level/endorsement

Important Notes on the Staff Licenses Cube

• Only includes current licenses• Contains information on staff members who

have not been submitted to STARS– These licenses show as attached to the “PED” District– Staff demographic dimensions will have values of “No

Data”

• Includes licenses that are not “valid” (e.g., Status = Denied or Pending)

• If a license has multiple endorsements attached to it this results in multiple records in STARS

Student Course Enrollment Cube

• Based on the Student Course Enrollment table• Provides unduplicated counts of students enrolled in

classes, primary staff members teaching classes, and sections as well as other measures by various dimensions

• Also contains staff and student demographic dimensions sourced from the Staff/Student Snapshot and Course Instructor tables– A Student Course Enrollment record will not appear in this cube

if there is no corresponding Course Instructor and Staff/Student Snapshot records for the report period in question

• Grain: One record per district/location/course/section/student

• New STARS terms and structure may be unfamiliar– For example, staff positions now called staff assignments– Where do I find information on bilingual?

• Understanding what cube to use– Data could be in multiple cubes– Not all STARS subject areas are included in a cube– Not all student related data is in the Student Snapshot cube

• Understanding what you are counting– For example, what does the # of Program Enrollments mean? – Understanding when a measure is duplicated and when it is

unduplicated; if duplicated, how is it duplicated– Key is to understand the grain, or lowest level of detail, in each

cube

Challenges to using the Cubes

Where do I go for more information on cubes?

• Following documents available on STARS project web site: (www.ped.state.nm.us/stars/documentation.html)

– Cube training document– Cognos presentation from Spring 2007

Conference– This presentation and additional detailed cube

documentation will be available soon

• Contact the STARS Help Desk

Exercise 1

• How many unduplicated certified staff members were reported for your district by location for SY 06-07?

• Questions:– Which cube?– Which dimensions?– Which filters?– Which measure?

Exercise 2• How many unduplicated related service

personnel were reported, and what was their total FTE, by your district for 120 day of SY 06-07 where the individual has 16 or more years of total experience? Create a report by Ethnicity and Gender.

• Questions:– Which cube?– Which dimensions?– Which filters?– Which measures?

Exercise 3• How many unique primary instructors taught

courses reported by your district at EOY of SY 06-07 where the class was determined to be highly qualified but not qualified by the NM license rules?

• Questions:– Which cube?– Which dimensions?– Which filters?– Which measures?