Exam DP-600 topic 1 question 31 discussion

I think what they are trying to get at is that you denormalize the company information into both tables so that you do not need the CompanyID anymore, which would then make the relationship between OrderItem and Product only based on ProductID. Hence I think A and C. But honestly that whole model is just weird.

the question says : "You need to create a dimensional data model that will enable the analysis of order items by date, product, and customer." Therefore, the analysis of order item by date, product and customer doesn't need the company table (it can be omitted) ! The answer is A and A

To create a dimensional data model that will enable the analysis of order items by date, product, and company, you should consider the following approach: The relationship between OrderItem and Product: A: The ProductID column The Company entity must be: C: Denormalized into both Customer and Product entities This approach ensures that the data model is optimized for analysis by including the necessary relationships and denormalizing the Company entity to provide comprehensive insights.

For the 1st part, I would say "C. A new key that combines the CompanyID and ProductID columns" because we are in the model view and there is no possibility to create a relationship based on 2 columns (that can be done in Power Query with a "merge" operation but NOT in the Model view). For the 2nd part, C. "Denormalized into the Customer and Product entities" Answer = C and C.

I think it's B and C

If company table represents cars such as Toyota and Honda while the product table has parts like a hood, the analysis is NOT interested in the car brand. The analysis should find out how many hoods the company sold, so I believe we should use the ProductID in the relation and the Company table can be omitted. I am leaning towards A & A

A-->The product ID column A-->Omitted

Company doesn't have any more attribute. just company id and it's already on customer and product entities so it can be omitted.

I think Company table should be completely omitted as this is extra data that is not mentioned in the requirement (You need to create a dimensional data model that will enable the analysis of order items by date, product, and customer). So the answer should be The Product ID Column and Omitted.

By denormalizing the relationships will become many to many no ?

Given the Primary key for Product is CompanyID and ProductID, how do you model two (2) "active" one-to-many relationships between Product (dimension) and Orderitem (fact) objects?

C and C The company ID and Product ID combined together will make a unique Identifier. You have to denormalise to reduce the number of joins in the model.

Relationship based on CompanyID and ProductID: Why: In many real-world scenarios, products might be uniquely identified only when combined with the company context. Thus, ensuring the correct identification and linkage between OrderItem and Product necessitates using both columns. Create a composite key or use both columns to form the relationship between OrderItem and Product. Denormalizing the Company entity: Why: Denormalizing Company data into Customer and Product helps to flatten the structure, making it easier to query and reducing the complexity of joins. How: Add the relevant Company attributes directly into the Customer and Product tables. For instance, each Customer and Product entry will carry information about the Company they are associated with.

The requirement for Analysis "date, product, and customer" so we only need productId and nothing about the company so Omitted. I'm interested on why many answers go for including company in the model? would love to hear about that.

The request about creating dimensional data model don't include Company so in my opinion the model can be achieved by A (ProductID Column) and A(Omitted) so no need to include it.

IMHO, B & C C - because there is initially compound PK. It means we can't just drop one, because it may be a case that for example different companies have same product_id which is actually different product

it can have all the answers C : A new surrogate key which combines Product and Company B:Denormalized Company into product table