Dimension tables

Dimensions provide context surrounding the fact tables. Simply put, they give the who, what, where of a fact.

fact_learn_events, for example, can be joined to dim_content to get more information on the content items (title, technology, topic, etc.)

• The dimension tables available are:

  • dim_content

  • dim_user

  • dim_team

  • dim_track

  • dim_certification

  • dim_date

dim_content

This table stores detailed information on the different types of content available on the platform.

Field	Description
group_id	The unique group identifier
content_type	The type of content (course, chapter, project, assessment, etc.)
content_id	The unique content identifier
exercise_id	The unique exercise identifier
chapter_id	The unique course chapter identifier
course_id	The unique course identifier
content_title	The title of the content item
technology	The technology of the content item (R, Python, SQL, Spark, etc.)
topic	The topic of the content item (Programming, Data Manipulation, Artificial Intelligence, etc.)
slug	The slug of the content item, if applicable
xp_available	The maximum XP points the user can earn by completing the content item
course_state	The current status of the course (Live, Soft Launch, Archived)
course_is_custom	Whether the course is custom or not
assessment_id	The unique identifier of the assessment
assessment_active	The current status of the assessment
project_id	The unique identifier of the project
project_is_guided	Whether the project is guided or unguided
project_state	The current status of the project (Live, Archived)
project_is_custom	Whether the project is custom or not
practice_pool_id	The unique identifier of the practice pool
practice_pool_status	The current status of the practice pool (Live, Archived)
exercise_is_deleted	Whether the exercise has been deleted
time_needed_in_hours	The estimated time needed in hours to complete the content item
description	A description of the content
short_description	A short description of the content
assessment_is_custom	Whether the assessment is custom or not

dim_user

This table contains detailed information on the user. This table is commonly joined with other tables to enrich user-related data, as most fact event tables contain a user ID.

Field	Description
group_id	The unique group identifier
user_id	The unique user identifier
email	The user’s email address
first_name	The user’s first name
last_name	The user’s last name
slug	The slug of the user, used in the user's portfolio/profile URL
avatar_file_name	The avatar file name of the user

dim_team

This table stores detailed information about teams.

Field	Description
group_id	The unique group identifier
team_id	The unique team identifier
team_name	The name of the team
created_at	The timestamp when the team was created
updated_at	The timestamp when the team was last updated
deleted_at	The timestamp when the team was deleted, if applicable
group_created_at	The timestamp when the group was created
slug	The slug of the team
team_color_hexcode	The hexacode of the team’s color

dim_track

This dimension table is designed to store detailed information about skill, career, and custom tracks. It’s commonly used to enrich track metrics with track information. For example, it helps in the Progress report where it provides the track title.

Entries in this table contain both a track_id and track_version_id because a track can contain multiple versions.

Track fact events are always tied to a track_version_id.

Field	Description
group_id	The unique group identifier
track_version_id	The unique track version identifier. Every time a particular track is updated, it gets a new track_version_id while keeping the same track_id
track_id	The unique track identifier
track_type	The type of track (public or custom)
track_title	The title of the track. The full name of the track is a concatenation of track_title and track_subtitle
track_subtitle	The subtitle of the track
track_category	The category of the track (career or skills)
is_current_version	Whether this track version is the current (latest) version of the track
track_version_number	The ordinal version number of the track. Each time the track is updated, the track_version_number increases by one
track_published_at	The timestamp when the track was published
track_archived_at	The timestamp when the track was archived, if applicable
track_technology	The technology of the track (R, Python, Shell, etc.)
track_state	The current status of the track (live, archived)
track_short_description	A short description of the track
track_slug	The track slug

dim_certification

This table stores details on certifications.

Field	Description
group_id	The unique group identifier
certification_id	The unique certification identifier
certification_name	The name of the certificate
certification_type	The certification is restricted if applicable (Public, Restricted, Specialist)
certification_is_custom	Whether the certification is custom or not

dim_date

This table contains a row per date from the year 2010 onwards. It serves as a support table for joining other tables with date_id.

This table includes pre-calculated values for common date-related operations, such as the day of the year, the day of the month, and the day of the week.

Field	Description
date_id	The unique date identifier (YYYYMMDD)
date	The date
day_of_week	The day of the week for that date (1-7)
day_of_month	The day of the month for that date (1-31)
day_of_quarter	The day of the quarter for that date (1-91)
day_of_year	The day of the year for that date (1-366)