0

I feel it is similar to the age old Sales --> Orders setup but I can't seem to finalize a solution.

I have a Business process of "Complaints" which are placed into a fact table which are in a traditional star schema linking to other dimensions such as customer and date.

There is also a process of Complaint Actions. Each complaint can have 1 or more actions which can result from it.

I am wondering how to model this so the user can drill through from a Complaint to its actions in Power BI.

The way it would ideally be displayed is a table visual with :

  • Complaint Date
  • Complaint Number
  • Customer
  • Action Number
  • Action Date

Currently the only common dimensions are Customer and Date.

My ideas are:

  1. Fact Complaints and Fact Complaints actions with conformed dimensions then place into a matrix visual so it is all visible on one page?

  2. Create a Dim Complaints table which would hold the data for each complaint (being the same amount of rows as the Fact Complaints) and link that to the complaints action table. Is this a bad approach in Dimensional modelling?

  3. Combine the 2 Fact Tables

  4. Create a "bridging" Dimension table which has each complaint ID and its associated Action IDs and use the ID here in the Power BI visuals I need.

Example of tables below:

Fact Complaints

| Customer Key| | Date Key| | Measure 1 | | measure 2 |

Fact Complaint Actions

| Customer Key| | Date Key| | Complaint Number (Degenerate Dimension) |
| Measure 1 | | measure 2 |

Chaddeus
  • 11
  • 4

2 Answers2

0

I think I'd go with option 2. DimComplaints would just be a conformed dimension, and there's nothing wrong with having two fact tables reference it (that's the whole gist of how a Kimball data warehouse works). Each action would just be another row in the FactComplaintAction table, so it'd be easy to aggregate metrics across complains and actions.

BayouKid
  • 69
  • 4
  • Hi bayou, thanks so much for your reply! When I talked about a 'bad' approach I was referring to having a dimension table that grows as the same rate as another fact table (e.g. Dim Complaints and Fact Complaints). Isn't this considered bad practice? – Chaddeus Oct 31 '22 at 18:29
  • Also, I assume the Dim Complaints table would be the same "structure" as the fact complaint table but using data and not keys obviously. Complaint ID, Complaint Date etc.? – Chaddeus Oct 31 '22 at 18:40
  • Not really, @Chaddeus. I asked Margie this exact question at her "data warehouse university" a few years ago and she didn't think this design pattern was a problem. I've used it a few times in my warehouses and it works fine. – BayouKid Oct 31 '22 at 19:22
0

There nothing wrong with giving a fact table a key and treating it as a dimension from the context of another fact. The complaint action can hold the id of the complaint.

Rich
  • 2,207
  • 1
  • 23
  • 27