Opportunity Estimate Sales Order Data Modelling Question

[KunalKawale]

I am working on a data model where the business requirements is to deep dive into the Opportunities, Estimates and Sales Order.

Then conversion rates the turnaround time. Price Levels performance etc. We have netsuite as the source system.

Here I am having a problem in designing the power BI or data ware house model.

Problem :-

We have Opportunity, Estimates and Sales order in a transaction table segregated by the transaction type.

I was thinking to make three fact tables
1. Opportunity fact table.
2. Estimate Fact Table.
3. Sales Order Fact Table.

but the issue is coming when we are trying to link them. Here is the data get converted.

1. Opportunity can get directly converted into sales order. one or many sales Order.

2. Opportunity can get converted into an estimate which could be linked to another estimate est1, and then to a sales order.
Opp--> Est1--> Est2--> SO

3. Estimate can directly get converted to SO without having an opportunity. it could have an intermediate estimates.

Est1--> Est2--> SO
One estimate will have only one sales Order.


The linking is described as above. So i was thinking to create a bridge table which would link all the tree tables together.

But i am having problem is how should decide the bridge table.
should it be on cosolidated fact table linked to all the three fact tables.
or should there be three bridge tables linking each fact table to other.
Also how should the dimension be linked?

Appreciate the help.

Please let me know if you could help me on this.

Thanks.

[v-menakakota]

Hi @KunalKawale  , 

Thanks for reaching out to the Microsoft fabric community forum. 

I would also take a moment to thank @lbendlin , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference. 

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Best Regards, 
Community Support Team

[v-menakakota]

Hi @KunalKawale ,

I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Thank you.

[v-menakakota]

Hi @KunalKawale ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Thank you.

[lbendlin · Accepted solution]

Power BI likes its data organized in facts and dimensions. It's not a hard requirement, but often leads to better performance.  In your case you will have to accept that the pristine 1:* relationships are not possible, and you have to carefully curate the direction of the *:* relationships to emulate a dimension -> fact hierarchy.

Bridge tables are dimension tables that weren't invited to the party.  They are usually a design red flag. Instead, use TREATAS