Analytics & Data

BI Connector Recipes

Battle-tested SQL snippets for the NRT BI Connector. Each recipe documents a specific data quirk, deprecated-table replacement, or analytical pattern — ready to drop into Power BI, Tableau, Looker, dbt, or psql.

7 recipesRedshiftVerified

How recipes are organized

Difficulty — beginner / intermediate / advanced

Tags — categorize by concern: data-quality, analytics, deprecation.

Tables — every recipe lists the tables it touches; filter to the ones you use.

Every recipe has a last_verified date so you know it has been checked against the current schema.

Browse recipes

Filter by tag

Filter by table

Showing 7 of 7 recipes

beginner

Filtering to Active Enrollments Only

Exclude archived, inactive, and deleted enrollments from usercourses using statusgroup.

Verified 2026-05-19

Tables

usercourses, users

Handling Clamped 9999 Timestamps

Normalize the 9999-12-01 sentinel that the BI Connector uses for "no expiry" values.

Verified 2026-05-19

Tables

usercourses, userlicenses, userbundles

Replicating the Deprecated coursetags Table

Replicate the deprecated coursetags table by joining courses, coursegrouptags, and tags.

Verified 2026-05-19

Tables

courses, coursegrouptags, tags

deprecation

redshift

Deduplicating Records in Redshift

Remove duplicate ids from any BI Connector table using ROW_NUMBER().

Verified 2026-05-19

Tables

usercourses, quizattempts, userlearningpaths, userbundles

Latest (or Highest-Scoring) Quiz Attempt per Learner

Reduce the quizattempts table to one canonical row per learner per quiz — either the most recent or the highest scoring.

Verified 2026-05-19

Tables

quizattempts, quizattemptsresponses

Pivoting coursecustomfields from Long to Wide

Convert the EAV-shaped coursecustomfields table to one row per course with each custom field as a column.

Verified 2026-05-19

Tables

coursecustomfields, courses

Recalculation of quizattemptresponses Correct Column

Correct historical quiz response data where unanswered questions were defaulted to correct = TRUE before 2025-07-04.

Verified 2026-05-19

Tables

quizattemptsresponses, quizattempts

Contributing a recipe

Recipes live in biconnector/recipes/. To add one:

Copy an existing .mdx file as a template.
Fill in the front-matter — title, tags, tables, difficulty, last_verified.
Follow the section order: TL;DR → Problem → Solution → How it works → Variations → Related.
Run npm run build to refresh the recipe index and reverse cross-links on table pages.