I’m a new learner to ssis and trying to build the data warehouse based on adventureworks database to find the most profitable product. Right now I have several dimension tables such as product dim sales dim date dim etc
But when I was selecting productID from the product as a PK by joining productsubcategory and category, I will get duplicate productIDs since there are different components using same productID, where they belong to different subcategories or categories resulting repeated PKs. I believe duplicated PK is not allowed?
And also for sales dim table, I was trying to extract Line Total from Salesorderdetail, so that I can calculate profit in fact table. My first thought is to join with saleorderheader then join again with product, but it seems like the PK saleorderID still has repeated values… So how can I fix this?
One last question is: I use sql command in oledb source to select my attributes then connecting it with destination directly. Can I use lookup transformation to do this? I was trying to use lookup before it failed, not many tutorials are helpful with the question I’m facing, really appreciate if someone can clarify this for me.
