Online orders data schema

[Anonymous]

Hi all,

I'm trying to understand how to develop an effective data model to support my business needs. I understand that from research the star schema is one of the best methods, however I am unable to understand how to set up a model in this way.

Our database contains three key tables as outlined below:

1. Order table (all orders with OrderNo as PK) this includes total order price, total tax, total discounts, total shipping price etc.

2. OrderDetail table (all line items within each order) this includes the productID of each product, productTitle, orderno etc. 

3. Product table (all products in our store) including productID, product retail price, product cost, colour etc.

We would like to be able to choose an orderno in a slicer and then be able to see the line items within that order, including the total cost of the order (as looked up from order table > orderdetail > product), the total price of the order and all other relevant details. 

I have tried creating a relationship between Order table and OrderDetail table using OrderNo and OrderDetail table to Product table using ProductID however the product prices are not totalling correctly in a table visualisation.

Can you please suggest an appropriate schema or any modelling that I need to do in order to get this working correctly?

[Anonymous]

Hi @Anonymous 

Could you share a sample without senstive data by your Onedrive for Business with me?

I need to know what kind of relationship between OrderDetail table and Order table/ Product table.

(One to One or one to many?)

And you can show me the result you want, this may make it easier for me to understand your requirement.

Best Regards,

Rico Zhou

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

[amitchandak]

@Anonymous , If product to order details is 1 to many

A new measure like

sumx(OrderDetail, related(Product[retail price]))

or
sumx(OrderDetail, related(Product[retail price])* OrderDetail[quantity])

or

sumx(relatedtable(Product),Product[retail price])

[AllisonKennedy]

You need to bring the product price into the fact table, either with measure or calculated column. You can try adding a new column to the Order Detail table; 

Price = RELATED(Product[Price]) 

Then you can use that in the values in your visuals.