Skip to content

WNE Dimensional Model Data Dictionary

Overview

This directory contains data dictionary documentation for the Weld North Education (WNE) Dimensional Model tables in the WELD_NORTH_PROD.WNE_DIM_MODEL schema.

Schema Information

  • Database: WELD_NORTH_PROD
  • Schema: WNE_DIM_MODEL
  • Source System: WNE Data Warehouse
  • Tables: 10 tables
  • Total Columns: 358 columns documented

Tables

Fact Tables

ACTIVITY_ATTEMPT_FACT (41 columns)

Captures individual student activity attempts within lessons and courses - Key Fields: ACTIVITY_ATTEMPT_FACT_KEY, USER_KEY, ASSIGNABLE_KEY, ORG_KEY - Purpose: Tracks student interactions with learning activities including scores, time spent, and academic integrity metrics - Key Metrics: ACTIVITY_SCORE, TIME_SPENT_IN_SECONDS, PLAGIARISM_SCORE, AI_DETECTED_SCORE

ASSESSMENT_FACT (48 columns)

Stores assessment results and diagnostic testing data - Key Fields: ASSESSMENT_KEY, USER_KEY, ORG_KEY, APPLICATION_KEY - Purpose: Records assessment outcomes including placement tests, benchmark assessments, and progress monitoring - Key Metrics: SCALED_SCORE, SCORE_GIVEN, SCORE_MAX, INSTRUCTIONAL_GRADE, DOMAIN_THETA

ENROLLMENT_PROGRESS_EVENT_FACT (25 columns)

Tracks student enrollment progress events over time - Key Fields: ENROLLMENT_UUID, USER_KEY, ORG_KEY, EVENT_DATE_KEY - Purpose: Captures daily snapshots of student progress in enrollments for pacing and performance analysis - Key Metrics: ACTUAL_PROGRESS, ACTUAL_GRADE, OVERALL_GRADE, ACTUAL_PROGRESS_STATUS

Dimension Tables

ASSIGNABLE_DIM (39 columns)

Master dimension for assignable content (courses, sections, independent content) - Key Fields: ASSIGNABLE_KEY, ASSIGNABLE_UUID, SCHOOL_ORG_KEY - Purpose: Defines courses, sections, and content that can be assigned to students - Key Attributes: TITLE, SUBJECT, GRADE_LEVELS, COURSE_FAMILY, ASSIGNABLE_TYPE

ASSIGNABLE_ACTIVITY_DIM (31 columns)

Dimension for individual activities within assignable content - Key Fields: ASSIGNABLE_ACTIVITY_KEY, ASSIGNABLE_KEY, ACTIVITY_PATH_ID - Purpose: Maps the hierarchical structure of activities within courses (Units > Lessons > Activities) - Key Attributes: ACTIVITY_NAME, ACTIVITY_TYPE, LESSON_NAME, UNIT_NAME

ORG_DIM (47 columns)

Type 2 Slowly Changing Dimension for organizations (districts, schools) - Key Fields: ORG_KEY, ORG_UUID, ORG_ID - Purpose: Stores organization hierarchy and attributes with full history tracking - Key Attributes: ORG_TITLE, ORG_TYPE, ORG_STATE, ORG_DISTRICT, PARENT_ORG_UUID - SCD Type: Type 2 (DW_EFFECTIVE_START_DATE, DW_EFFECTIVE_END_DATE, DW_IS_ACTIVE)

ORG_DIM_LATEST (39 columns)

Current state view of organizations (latest active record only) - Key Fields: ORG_KEY, ORG_UUID, ORG_ID - Purpose: Provides simplified access to current organization data without historical versions - Note: Use this for current state queries; use ORG_DIM for historical analysis

USER_DIM (41 columns)

Type 2 Slowly Changing Dimension for users (students, teachers, administrators) - Key Fields: USER_KEY, USER_UUID, USER_ID - Purpose: Stores user profiles and attributes with full history tracking - Key Attributes: FIRST_NAME, LAST_NAME, EMAIL, USER_GRADE_LEVEL, ROLE - SCD Type: Type 2 (DW_EFFECTIVE_START_DATE, DW_EFFECTIVE_END_DATE, DW_IS_ACTIVE)

USER_DIM_LATEST (26 columns)

Current state view of users (latest active record only) - Key Fields: USER_KEY, USER_UUID, USER_ID - Purpose: Provides simplified access to current user data without historical versions - Note: Use this for current state queries; use USER_DIM for historical analysis

USER_ORG_DIM (21 columns)

Bridge table linking users to organizations with role assignments - Key Fields: USER_ORG_KEY, USER_KEY, ORG_KEY - Purpose: Tracks user membership in organizations including role and status - Key Attributes: ROLE, IS_ENABLED, IS_DELETED

Common Key Relationships

Primary Keys

  • *_KEY columns (e.g., USER_KEY, ORG_KEY) are surrogate keys for dimension tables
  • *_UUID columns are natural keys from source systems

Foreign Key Patterns

  • USER_KEY: Links to USER_DIM or USER_DIM_LATEST
  • ORG_KEY: Links to ORG_DIM or ORG_DIM_LATEST
  • ASSIGNABLE_KEY: Links to ASSIGNABLE_DIM
  • APPLICATION_KEY: Identifies the source application (Edgenuity, MyPath, etc.)
  • APP_DOMAIN_KEY: Application domain context

Data Warehouse Metadata Columns

Most tables include these standard DW audit columns: - DW_CREATED_BY: ETL process that created the record - DW_CREATED_ON: Timestamp when record was created - DW_UPDATED_BY: ETL process that last updated the record - DW_UPDATED_ON: Timestamp of last update

For SCD Type 2 dimensions: - DW_EFFECTIVE_START_DATE: When the record version became active - DW_EFFECTIVE_END_DATE: When the record version was superseded (NULL if current) - DW_IS_ACTIVE: Boolean flag indicating current version

Usage Notes

Choosing Between Historical and Latest Tables

  • Use ORG_DIM / USER_DIM when you need historical analysis or point-in-time queries
  • Use ORG_DIM_LATEST / USER_DIM_LATEST for current state queries (better performance)

Common Join Patterns

-- Get current student activity with user and org info
SELECT 
    u.first_name,
    u.last_name,
    o.org_title,
    a.activity_score,
    a.time_spent_in_seconds
FROM activity_attempt_fact a
JOIN user_dim_latest u ON a.user_key = u.user_key
JOIN org_dim_latest o ON a.org_key = o.org_key
WHERE a.is_activity_completed = true;
  • LANDING_ZONE_OPEN: Source data from ILC (Imagine Learning Classroom)
  • STAGING: Intermediate processing tables
  • SISENSE: Reporting and analytics views