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
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
<SalesReason>Last of the Mochicants</SalesReason>
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
Appreciate that , thank you