[ARCHIVED] Tableau and struggles with basic queries

Jump to solution
jblumberg
Community Contributor

I am new to Tableau and am struggling with some very basic queries.  Here is a basic query I am starting with:

For each course, what are the total number of assignments.  This simple metric is a good way to measure Canvas adoption on Campus (we used the same metric on the old LMS).  I have gotten this from the API before but wanted to give Tableau and Hosted Data a Chance. 

My strategy:

Join the course_dim and assignment_dim tables   (I also tried looking at assignment_fact but this caused even more issues)

This gives me something like:

SELECT "course_dim"."id" AS "id",

  "course_dim"."canvas_id" AS "canvas_id",

  "course_dim"."name" AS "name",

  "course_dim"."workflow_state" AS "workflow_state",

  "assignment_dim"."id" AS "id__assignment_dim_",

  "assignment_dim"."canvas_id" AS "canvas_id__assignment_dim_",

  "assignment_dim"."course_id" AS "course_id",

  "assignment_dim"."title" AS "title"

FROM "public"."course_dim" "course_dim"

  INNER JOIN "public"."assignment_dim" "assignment_dim" ON ("course_dim"."id" = "assignment_dim"."course_id")

The problem is that there are multiple entries for each assignment (anywhere from 1-3).  Only one of them has a valid assignment ID.  In all cases I've tested the highest number assignment ID is the valid one.  The other ones don't exist in Canvas.

The way I was trying to get a count of the number of assignments is to count the number of rows per course.  This of course makes it impossible to get an accurate count of the number of assignments since each assignment is returning multiple rows some of which are invalid. 

Can someone assist me?  This seems obvious but it is really tripping me up.

Labels (1)
1 Solution
jblumberg
Community Contributor
Author

I may have answered my own question... I guess I need to look at the Workflow state.

Starting to look more closely at the data schema now.  (read the documentation... duh!)

Josh

View solution in original post