Using the connector with your BI tool

Several tables reference "any kind of object" through an ID column plus a type column: views.viewable_id + viewable_type, shares.shareable_id + shareable_type, likes.likable_id + type, learning_plan_items.item_id + item_type, and others.

The problem: no BI tool can define a relationship that changes its target depending on a column value.

The fix, in any tool: create one filtered query per type. A "Content views" query filtered to viewable_type = 'Content' joins cleanly to contents. A separate "Topic views" query filtered to viewable_type = 'Topic' joins to topics. Do the filtering in SQL if you can, so Redshift does the work and you move less data.

Do not join on the ID alone and hope. Content ID 500 and topic ID 500 are different objects, and the join will silently produce nonsense.

profile_values holds one row per user per field. To use department, region or job title as a filter, you need those as columns.

The cleanest fix is SQL, because it works identically everywhere:

SELECT
  pv.user_id,
  MAX(CASE WHEN pf.label = 'Department' THEN pv.value END) AS department,
  MAX(CASE WHEN pf.label = 'Region'     THEN pv.value END) AS region,
  MAX(CASE WHEN pf.label = 'Job Title'  THEN pv.value END) AS job_title
FROM profile_values pv
JOIN profile_fields pf ON pf.id = pv.profile_field_id
WHERE pf.deleted_at IS NULL
GROUP BY pv.user_id

Every tool also has its own pivot step if you prefer to do it there. Either way, respect profile_fields.exclude_from_reporting — fields flagged that way are deliberately kept out of analytics.

There is no manager_id column. A user's manager is another row in users, pointed at by users.owner_id.

The fix: bring users in twice — once as your people dimension, once aliased as "Managers" — and join users.owner_id to managers.id. In SQL that is a straightforward self-join. In a visual modelling tool you will usually need a second copy of the table, because most tools refuse a direct self-relationship.

If you need more than one level of hierarchy (manager's manager), do it in SQL with repeated joins. Recursive hierarchy support varies a lot between tools and is rarely worth fighting.

There is no single engagement fact table. Views, likes, comments, shares and follows are separate, and Universal Analytics adds them together.

The fix: union the five into one fact table with an added column naming the engagement type. That gives you a single date axis, a single user join, and an engagement-type filter for free — and it performs better than five separate joins.

SELECT id, user_id, created_at, 'view' AS engagement_type FROM views
UNION ALL
SELECT id, user_id, created_at, 'like'    FROM likes
UNION ALL
SELECT id, user_id, created_at, 'comment' FROM comments WHERE deleted_at IS NULL
UNION ALL
SELECT id, user_id, created_at, 'share'   FROM shares
UNION ALL
SELECT id, user_id, created_at, 'follow'  FROM follows WHERE deleted_at IS NULL

Add the object ID and type columns to each branch if you need to slice by what was engaged with.

The connector has no date dimension. Every serious BI tool needs one for period comparisons, running totals and fiscal calendars to behave.

The fix: generate a date table covering your full reporting range, mark it as your date dimension in whatever way your tool requires, and join it to created_at on your fact tables. Include fiscal year and quarter columns if your organisation does not run on the calendar year — retrofitting that later is painful.

Many of the useful Fuse figures are ratios, and several denominators can legitimately be zero or null. event_occurrences.spaces_number is null for uncapped events. A learning plan with no assignments has an audience of zero.

The fix: use your tool's safe-division function rather than a bare /, and decide deliberately whether a zero denominator should show as blank or as zero. Blank is usually more honest — it says "not applicable" rather than "nothing happened".

This is the single most common cause of numbers that do not tie. A user in five communities produces five rows in memberships. A content item in three communities gets its views attributed three times if you join through community_items carelessly.

The fix: be explicit every time about whether you are counting people or events. Use a distinct count on user_id when you mean people. When a join fans out, aggregate before joining rather than after.