Unsure whether to use One-to-One Relationship or Factless Fact Table

user_15008948 0
Viewed 56

I have a Dimension Table called Dim Case. This is a conformed dimension that is linked to 3 different fact tables. I need to answer questions about the Cases themselves however.

Questions such as

  1. Cases opened / closed between dates
  2. Time between Inquiry Date and Case Start
  3. Time from Start to Completion

I feel as though there are 2 solutions to this but unsure which would be best.

  1. Create a Fact Cases table with a 1:1 relationship to the Dim Case table (and relationships to other needed tables such as Customer) as questions above seem as though they should be calculated measures within the Fact Table.

Are 1:1 relationships frowned upon?

  1. Put all of the Measures/answers to the questions into the Dim Case Table as attributes and create a Factless Fact Table which links related tables such as Dim Case, Dim Date, Dim Customer etc.

Example Table Schemas below:

Dim Case

  • Case Key
  • Case No.
  • Status
  • Manager
  • Inquiry Date
  • Case Start Date
  • Source
  • Completed Date

Possible Fact Cases Table:

  • Case Key
  • Customer Key
  • Inquiry Date
  • Completed Date
  • Time between Inquiry Date and Case Start
  • Time from Start to Completion
powerbi relationship dimensional-modeling data-modeling sql
1 Answers

If this is just a question of where to store your measures, you're probably overthinking it. Most well organised models store their measures in a measures table with subfolders (a measure frequently involves more than one table and finding a suitable home is not always obvious if you store all measures in the respective tables).

1-2-1 relationships are not frowned upon but they are rare as they are effectively pointless and raise the question of why the columns are not all in the same table. There could be a logical separation but otherwise there isn't much point.

Creating measures for metrics about your dimensions is not unusual.

I don't know what else is in your model but why do you think Case is a dimension and not a fact table? From what you have described, it sounds like a fact table.

user_183450370
Related