How to approach a data model when Dimension table has "many"

[TitanicTips23]

Hi everyone,

Not at all new to Power BI, but fairly new to working with more complex data models. I am struggling to understand how I should approach a dimension table that contains many records.

I have a fact table that contains financial information that needs to be reported, and also change ticket numbers. The change tickets have tasks assigned to them, and these change tasks live in a different table that relates to the change ticket number in my fact table. So in my change task table, I will have multiple tasks per change ticket, but in my "sales" or fact table, I will have only one change ticket per record.

I have read a bit about denormalized fact tables and bridge tables, but I wanted to spell out my problem and have some conversations with the community on how I might should approach this issue.

[Anonymous · Accepted solution]

Hi, @TitanicTips23 

Based on your description, you wish to seek advice on processing information from the dimension table, which contains multiple records related to a single record in the factual data table, and you can try the following methods to solve the problem.

Method 1, Bridging Tables
Since each change order has multiple tasks, you can use bridge tables to manage the many-to-many relationship between fact tables and change tasks. The following is a brief plan of action:
1. Create a bridging table that will associate each change order number in the fact table with multiple related tasks in the dimension table. The bridging table will contain the keys from both tables, but not the metric values.
2. create a relationship, in Power BI, between the bridging table and the fact and dimension tables. A bridged table has a one-to-many relationship with the dimension table and a many-to-one relationship with the fact table.
3. use in analysis, use bridging tables in reports to filter and analyze data in a many-to-many relationship.

Method 2: Role-Playing Dimensions
If the task is directly related to financial information, consider using a role-playing dimension. This involves creating multiple instances of the dimension table, each representing a different aspect of the task related to the change ticket.

1. create role-playing dimensions that replicate the dimension table for each role associated with the fact table.
2. define relationships, creating relationships between the dimension table and each instance of the fact table, ensuring that the most commonly used filter paths have active relationships.
3. use in reports, use these role-playing dimensions in reports to filter and slice and dice data as needed.

https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[TitanicTips23]

Hi @Daniel29195 for some reason your comment was deleted.

The fact table will ALSO have change ticket numbers.

To elaborate further, hopefully without getting too confusing, I actually have a few different tables that need to be combined in order to report on the status of tickets and the cost associated with that change management item. So what I am having to do is combine multiple tables to create one fact table, and then connect my various dimensions to it.

The confusion arises when I have my "new" fact table that contains the record with a unique change request per record, but that change task table as my dimension has multiple records of the same change request, because there are multiple tasks per change request.

Hope that clears it up a bit.