1

What's the best way to model 37 different attributes/"checkpoints" (that can be graded as Pass/Fail/Not Applicable) in a dimension for a star schema where each row in the fact table is a communication that is graded against the checkpoints in question?

TL;DR:

I've developed a star schema model where each row in the fact table is a single communication. These communications go through a series of graded "checks" (e.g. "Posted on Time", "Correct Email Subject", "XYZ content copied correctly", etc.) and each check can be graded as "Passed", "Missed", or "Not Applicable".

Different types of communications are graded on different sets of checks (e.g. one type of communication may only be graded on three checks, the rest being "Not Applicable", whereas another type of communication is graded on 19 checks). There are 37 total unique checks.

I've built a "CommunicationGrading" Type 2 slowly changing dimension to facilitate reporting of which "checks" communications are scoring most poorly. The dimension has 37 columns, one for each attribute, and each row is a permutation of the attributes and the score they can receive (pass/fail/NA). A new row is added when a new permutation becomes available - filling all possible permutations unfortunately returns millions of rows, whereas this way is < 100 rows, much less overhead. I've created 37 separate measures that aggregate the # of communications that have missed each of the 37 separate "checks".

I can quickly build a treemap in PBI, drag the 37 measures on there, see the total # of communications that have missed each "check", and determine that X # of communications missed Y checkpoint this month. The problem comes when I want to use the visual as a slicer, (e.g. selecting a check/tile on the treemap to see what individual communications missed that check in a table beneath the treemap) or determining the top N "checks" given a slice of data.

From what I can tell, the issue is because I'm using 37 different attributes and measures rather than one attribute and one measure (where I could drag the single measure into Values and the single attribute/column containing all checks into Group field in the treemap visual). The problem is, I'm stumped on how to best model this/the Grading dimension. Would it involve trimming the dimension down to just two columns, one for the checks and one for the checks' possible scores, then creating a bridge table to handle the M:M relationship? Other ideas?

Ryan H
  • 23
  • 1
  • 6

1 Answers1

0

Your dimension (implemented as a junk dimension- something to google) is one way of doing it, although if going down that road I'd break it down into multiple dimensions of related checkpoints to massively reduce the permutations in each. It also isn't clear why this would need to be a Type 2- is there a history of this dimension you would need to track?

However I'd suggest one approach to explore is having a new fact for each communication's score at each checkpoint- you could have one dimension of grade result (passed, failed, not applicable) and one dimension of each checkpoint (which is just the checkpoint description). It would also allow you to count on that fact rather than having to have 37 different measures. You may wish to keep a fact at the communication level if there is some aggregate information to retain, but that would depend on your requirements.

Rich
  • 2,207
  • 1
  • 23
  • 27
  • Thanks for the reply Rich! Regarding the Type-2 SCD, I meant I was adding rows on an as-needed basis rather than populating the dimension with all possible permutations. I'm still a bit new to all of the terminology, so my apologies for the mix up! If I understand you correctly, you're suggesting lowering the grain of the communication fact table from a new fact for each communication to a new fact for each checkpoint per communication, correct? It's not something I had considered before, but I think that should work. Thanks! – Ryan H Apr 23 '17 at 15:12
  • Ah yes, Type 2 SCD is a history tracking technique rather than the rows-as-they-come technique. No worries, the terminology isn't that important, its the ideas that matter. Yes I was suggesting a lower/more detailed grain for each result for each checkpoint in a communication. It might not be ideal, but its one thing to try. – Rich Apr 23 '17 at 20:34