Data Model Design Question - multiple many to many relationships

[abhiram342]

Hi All - I have data model related to Pipelines , Activities and Tickets . Pipelines table will have list of Pipelines per Product. Each Pipeline will have activties to be executed and stored in Activites table. if there are any failures in activties it results in tickets and tickets are stored in ticket table. We want to calculate

1) Failure Rate for each Product

2) Failure Rate by Platform & Non-Platform for each Product

3) # of Activties by IssueType

Failure Rate = # of Pipelines Failed/ # of Pipelines InProgess.

# of Pipelines Failed = Count of PipelineId with Aborted State

# of Pipelines InProgress = Count of PipelineId with InProgress+ Aborted State

Failure Rate Platform = # of Pipelines Failed ( Issue Type = Platform) / # of Pipelines InProgress

# of Activties by IssueType = # of Activites ( by Issue Type )

Model:

Sample Data:

As per above data, Pipeline B has both Platform and Non-Platform IssueType ( Pipeline B has ActivityId B5467 and it has mulriple issueType , which resulted in many to many relationship)

Also, In Activites data, In Pipeline B , Among two activites only 1 activity has Platform Issue and other activity doesn't have issues.

(ActivityId B5467  has multiple issue types)

Measures:

In Pipelines Table:

# of Pipelines = CALCULATE(COUNT('Pipelines'[PipelineId]), ALL(Pipelines[PipelineId]))

# of Pipelines Active = CALCULATE([# of Pipelines], Pipelines[State] IN {"InProgress","Aborted"})

# of Pipelines Failed = CALCULATE([# of Pipelines], Pipelines[State] == "Aborted" )

FailureRate = DIVIDE([# of Pipelines Failed], [# of Pipelines Active])

Measures in Activities Table:

# of Activites = CALCULATE(DISTINCTCOUNT('Activites'[ActivityId]))

Measures in Tickets Table

# of Platform Pipeline Failures = CALCULATE(DISTINCTCOUNT('Tickets'[PipelineId]),'Tickets'[IssueType]=="Platform")

# of Non-Platform Pipeline Failures = CALCULATE(DISTINCTCOUNT('Tickets'[PipelineId]),'Tickets'[IssueType]=="Non-Platform")

Platform FailureRate = DIVIDE([# of Platform Pipeline Failures], [# of Pipelines Active])

# of Non-Platform Pipeline Failures = CALCULATE(DISTINCTCOUNT('Tickets'[PipelineId]),'Tickets'[IssueType]=="Non-Platform")

Question

1) Is it possible to add IssueType as Column to Pipelines and Activites Table and Use as Common Dimension for all 3 tables?

Ex: by Creating Bridge Table?

Note : Below relationship fails

2) Another  Possible solution is to flattern all 3 tables into single table because most of data will be in sparse  ( ex: consider, there are 2m pipelines, 10m activitesand 10k incidents. After combining 3 tables , most of activiteswill not have tickets and will result in sparsedata)

Expected Column - Common dim between 3 tables ( Due to many to many relationship, not sure if we need to create multiple bridge tables)

Please provide your suggestions 

Thanks,

Abhiram

[Anonymous]

Hi @abhiram342 ,

Based on the information you have provided, this sounds like an attempt to optimise the way data is modelled in relation to pipelines, campaigns and work orders in order to efficiently calculate failure rates and campaigns by issue type.

1. Creating bridging tables is indeed a viable solution when dealing with many-to-many relationships in the data model. You can follow the steps below:
Create a new table that contains the unique identifiers from the Pipelines/Activities table and the Tickets table. This table should also include the IssueType column.
Establish a relationship between this bridged table and the other tables in the model. The bridge table will act as a central reference point for joining related records based on IssueType.
You can refer to the following link for more details:

Model relationships in Power BI Desktop - Power BI | Microsoft Learn

2. Spreading tables into a single table can simplify the data model and may make calculations easier. However, this method may result in a significant increase in dataset size. This may affect the performance of Power BI reports and dashboards. Consider the following points before deciding on this action:
The impact on performance and whether the Power BI environment can effectively handle the increased dataset size.
The complexity of maintaining and updating a single large table.

In many cases, maintaining a normalised data model with correctly defined relationships can provide a good balance between performance and flexibility.

Both of the approaches you mentioned have their advantages and potential disadvantages. The choice between creating bridged tables or stretching tables into a single table depends on the specific requirements of the project, including performance considerations and complexity of maintenance.

If you decide to go with the bridged table approach, can you share more detailed information about the specific issues you encountered when trying to build the relationship? This information will help provide more tailored recommendations.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[abhiram342]

thank you @Anonymous  for your suggestions!

[Anonymous]

Hi @abhiram342 ,

You are welcome.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.