[ARCHIVED] Tableau and struggles with basic queries

[jblumberg]

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.

[jblumberg · Accepted solution]

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

[JAWILL9209]

Hello,

       My name is Jaime Williams and I'm struggling in my course-work.I don't do very well in my writing assignments or discussions. I really just needed to be honest about my true feelings. I have no clue what this is but I'm trying to do whatever to help myself. I think it is wonderful your problem was solved. I want to be a better learner.