Redshift BI Connector: data reference

This is the reference guide for building your own reporting on top of the Fuse Redshift BI Connector. It documents every table exposed through the connector, the fields available in each one, and the metrics that power Universal Analytics so you can reproduce or extend them in your own BI tool.

Who this is for

You are a BI analyst or report builder with access to the Fuse Redshift BI Connector, and you want to build your own dashboards rather than rely solely on the Universal Analytics dashboard. This page is the starting point.

The connector is a read-only Redshift database, so it works with any tool that can query Redshift or PostgreSQL — Power BI, Tableau, Looker, Qlik, Metabase, Sigma, a SQL client, or your own warehouse pipeline. Nothing in this reference assumes a particular tool.

How to use this reference

Start with the tool page if you are setting up for the first time. It covers connecting, the modelling patterns this schema will hand you, and notes for the common BI tools.
Find the area you are reporting on — users, content, learning, assessments, events — and open the matching table reference page. Expand any table to see what it holds, what Fuse uses it for, and every field available.
Check the metrics page before you build a measure from scratch. If Universal Analytics already reports it, the formula is documented and you can mirror the same logic so your numbers agree with Fuse's.

Reference pages

Page	What it covers
Using the connector with your BI tool	Connecting, extract vs live query, the seven modelling patterns you will need to translate, notes per tool, and Redshift-specific gotchas.
Redshift tables: people, profiles & communities	Users, companies, custom profile fields and values, group and community membership, manager relationships, and assessment access control.
Redshift tables: content, engagement & events	Content, video, topics and chapters, views, likes, comments, shares, follows, gamification, and the full events model.
Redshift tables: learning, courses & assessments	Learning plans, SCORM, course progress, expiry rules, assessments, questions and answers.
Universal Analytics metrics reference	The metrics available in Universal Analytics, what each one means, and the formula behind it.

Before you model anything: five things that will save you time

1. Almost every table carries `company_id`

Fuse is multi-tenant, so company_id appears on nearly every table. Your connector view is already scoped to your organisation, but keep company_id in any join key you build if you are combining extracts, and never assume an ID is unique across companies without it.

2. Polymorphic relationships are everywhere

Several tables point at "any kind of object" using a pair of columns — an ID plus a type. For example views.viewable_id + views.viewable_type, shares.shareable_id + shares.shareable_type, learning_plan_items.item_id + learning_plan_items.item_type, and featured_contents.featureable_id + featureable_type.

No BI tool can define a relationship whose target changes depending on a column value. Filter to a single *_type value first (for example viewable_type = 'Content'), then join to that one dimension table. Build a separate filtered query per type if you need more than one. Doing the filtering in SQL is usually simplest and moves the least data.

3. Soft deletes: check `deleted_at`

Records are frequently soft-deleted rather than removed. Tables including answers, comments, follows, questions, question_choices, profile_fields, gamification_activities and assessment_results carry a deleted_at column. Unless you specifically want historical records, filter to rows where deleted_at is null — otherwise your counts will run high.

4. Denormalised counter columns exist — and they are shortcuts, not truth

Tables like contents carry likes_count, comments_count, shares_count, total_view_count and follows_count. These are fast for headline figures. They cannot be sliced by date, user or community, and contents.likes_count_boost means the stored like count may have been manually adjusted. For anything filterable, count rows in the underlying likes, comments, shares, follows and views tables instead.

5. Suggested core star schema

A practical starting model for most dashboards, whatever tool you are using:

Role	Table	Joins on
Fact — engagement	`views`, `likes`, `comments`, `shares`, `follows`	`user_id`, and the relevant `able_id` filtered by `able_type`
Fact — learning	`user_course_progresses`, `user_scorm_reporting_data`	`user_id`, `course_id` / `content_id`
Fact — assessment	`assessment_results`, `user_assessment_stats`	`user_id`, `assessment_template_id`
Fact — events	`participation_requests`, `participation_approvals`	`participant_id`, `occurrence_id`
Dimension — people	`users` + `profile_values` + `profile_fields`	`users.id` → `profile_values.user_id`
Dimension — content	`contents`, `topics`, `chapters`	`contents.chapter_id` → `chapters.id` → `chapters.topic_id`
Dimension — community	`communities` + `memberships`	`memberships.community_id` → `communities.id`
Dimension — date	Your own date table	The connector has no date dimension — generate one and join it to `created_at`

Custom profile fields are stored as rows in profile_values, not as columns on users. To use department, location or job title as a slicer, pivot them into columns first — there is a SQL recipe on the tool page.

Known gaps in this reference

Flagged so you know where to be careful, rather than discovering it mid-build:

usage_logs is documented with only two fields (user_id, created_at). The table holds more than this in practice — treat the field list as incomplete.
apprentice_course_progresses is described but has no field list available.
companies, scorm_courses, chapter_items and comments have full field lists but no written description in the source. Short descriptions have been added here for orientation.
Field lists show what is exposed, not data types or nullability. Confirm types against the connector itself before you set relationship cardinality.

Getting help

If a table or field you need is not listed, or a metric does not reconcile with the Universal Analytics dashboard, raise it with your Fuse Customer Success contact.