SQLTeam.com | Weblogs | Forums

Sales Order and Detail Issue

Greeting

I'm working with the AdventureWorks DataBase I do have 3 Tables

Table1 : OrderSales (OrderSaleslID "PK" , OrderDate , CustomerID )

Table 2 : OrderSaleReason (OrderSaleslID "PK" , SalesReason "PK")

Table 3 : OrderSalesDetail (OrderSalesDetail ID "PK" , OrderSaleslID "PK" , ProductID , QTE )

My fact table "Sales" is based on the OrderSalesDetail

FactSales (OrderSalesDetail ID "PK" , OrderSaleslID , ProductID , QTE Order , OrderDate , CustomerID)

My Question is there a possibility to integrate the SalesReason in my fact table without creating another table or having a duplicate in my fact table
PS: 1 sale order can have many sales Reason

and thanks!

one way you could it is if you had a column that would include all of the sales reasons but do you really want that? for example you could do an xml column

<OrderSaleReasons>
  <SalesReason>Expiration Approaching</SalesReason>
  <SalesReason>Last of the Mochicants</SalesReason>
  <SalesReason>Product Discontinuation</SalesReason>
</OrderSaleReasons>

Hey Yosiaz ! i can add a column the issue is that one sales order could have more than 1 sale that's the problem still can find a relation where i can store the reason to the detail w/e creating another table !

So the example Inprovided should work then. Did you understand the solution it has more than one sales reason

i guess yeah! i will see what i can do with it ! never used xml column before !
Thank you !

there are other ways to keep the data in one column but i would not recommend it. for example you could have the data pipe delimited

Expiration Approaching|Last of the Mochicants|Product Discontinuation

But my question is why do you not want to create another table or have multiple row for same sales?

im planning to create a datawarehouse and i want to follow the star Schema Architecture + i want a dynamic solution for this and it will be easy later to make reports with it

I would highly recommend

https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/

Appreciate that , thank you