cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RSSapre
Frequent Visitor

Data Model Question - Sales Order Line and Shipment Details

I need some help to model my order management data.

I have some dimensions such as customer (sold to, ship to, bill to), product, orgaization and Caledar with 2 fact tables Sales Order Line details and Shipment details. The two fact tables have a common column "SalesOrderLineId". I was advided to create a DimSalesOrderLines dimension so that I can put order line and shipment details together (side by side). I have surrogate key from Sale Order line Fact table added to shipment details table based on "SalesOrderLineId". I dulicated the table and renamed it as DimSalesOrderLine. Created relationship between DimSalesOrderLine and FactSalesOrderLine on the same surrogate key "One to One" and double direction. When I tried to create relationship between DimSalesOrderLine and ShipmentDetails on the surrogate key, Power BI gives me error message "Cannot create active relationship as it will introduce ambiguity between bill to customer and shipment details?

How do I model this so that I can put together the information from both the facts together such as any text information. 

 

Thanks in advnce for your help.insights.

1 ACCEPTED SOLUTION

Hi  @RSSapre ,

 

Sorry for the late reply.

How do you connect DimSalesOrderLine and ShipmentDetails?I didnt find the table DimSalesOrderLine in above screenshot.

Why you create so many dim tables?

I'm not sure whether it's the problem,but I would suggest you change the relationship I marked in red from both to single.

vkellymsft_0-1632217374157.png

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

View solution in original post

5 REPLIES 5
RSSapre
Frequent Visitor

 PBI Model.PNG

Please note that this is not a final model

Hi  @RSSapre ,

 

Sorry for the late reply.

How do you connect DimSalesOrderLine and ShipmentDetails?I didnt find the table DimSalesOrderLine in above screenshot.

Why you create so many dim tables?

I'm not sure whether it's the problem,but I would suggest you change the relationship I marked in red from both to single.

vkellymsft_0-1632217374157.png

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

View solution in original post

amitchandak
Super User
Super User

@RSSapre , First of all, what was needed from the line dimension, for which you made it dimension.

For 1-1 Join, I would prefer to merge tables 

or Make 1-M single direction from Dim to Fact, If this one does not work share the data model with me, with answer to the first question

 

Also, make sure detail dimension do not join with any other dimension

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Thanks for your suggestion. After a discussion with the team, we do not need line dimension in Power BI. I removed all the relationships from the shipment details. Kept Many to One relationship (active) between Order Lines and Shipment with direction = double. I had to keep some inactive relationships on dates in Shipment table for the measures. I think my model is working for now. I am testing the data to ensure it.

Hi  @RSSapre ,

 

Would you pls show us some screenshots of the relationship?

Remember to remove the confidential information.

 

 

Best Regards,
Kelly

Did I answer your question? Mark my raeply as a solution!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.