CODEWITHSUNDEEP
powerbidata-warehousedimensional-modelingstar-schema
Chaddeus
Chaddeus

Reputation: 35

How should I model a business process and its subsequent related processes and actions?

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 :

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 |

Upvotes: 0

Views: 92

Answers (2)

Rich
Rich

Reputation: 2279

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.

Upvotes: 0

BayouKid
BayouKid

Reputation: 79

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.

Upvotes: 0

Related Questions