How to Model Order Items by Date, Product, and Customer?

Design and build semantic models

HOTSPOT You have the source data model shown in the following exhibit. exhibit The primary keys of the tables are indicated by a key symbol beside the columns involved in each key. You need to create a dimensional data model that will enable the analysis of order items by date, product, and customer. What should you include in the solution? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. exhibit

Answer Add a new key combining CompanyID and ProductID to relate OrderItem to Product, and denormalize the Company entity into both the Customer and Product entities.

Community Insight

It tests whether you know Power BI relationships are single-column and that partial primary-key joins are invalid; the trap is joining on ProductID alone or assuming the Company table can simply be deleted.

This DP-600 hotspot asks how to turn a snowflake-style source with a composite Product key (ProductID + CompanyID) into a dimensional model for analyzing order items by date, product, and customer. The solution is a new combined CompanyID+ProductID key for the OrderItem-to-Product relationship, with the Company entity denormalized into both the Customer and Product tables.

The most common wrong approach is relating OrderItem to Product on ProductID only, which matches one product row per company incorrectly and silently duplicates or misallocates measures, because ProductID is not unique without CompanyID.

Community Discussion (36 comments)

ViniciusPA 👍 1

A relação entre OrderItem e Product deve usar CompanyID + ProductID, porque ProductID sozinho não identifica o produto. A entidade Company deve ser desnormalizada para Product e Customer, permitindo que a tabela Company seja omitida no modelo dimensional. Logo as respostas são: Both the CompanyID and the ProductID columns Denormalized into the Customer and Product entities

snowq 👍 3

c and c Power BI relationships natively support only a single column. Since the Product table uses a composite primary key (CompanyID + ProductID), you must merge these columns into a single surrogate key to establish a valid single-column relationship.

Dusica 👍 7

1. option 2 - both, product id and company Id 2. option 3 - denormalized into both product and customer

pveiga 👍 3

B and C

PradeepikaGadai 👍 1

I think answer for this is 1: Both the CompanyID and the ProductID columns because In the data model, Product has a composite primary key: (CompanyID, ProductID) So, the relationship from OrderItem to Product must be based on both columns to uniquely identify each product. and 2:Denormalized into the Customer and Product entities because both Customer and Product reference CompanyID, and we're designing a dimensional model (which favors denormalization for simplicity and performance), it is best to embed company data into the Customer and Product dimensions.This removes the need to model Company as a separate dimension and avoids extra joins. so the answers are : Both the CompanyID and the ProductID columns , Denormalized into the Customer and Product entities

zxc01 👍 1

the question is "You need to create a dimensional data model that will enable the analysis of order items by date, product, and customer." It is new dimensional data mode and we just focus on date, product, and customer. for both production and customer dimension, we can just keep distinct value of their ID, such as production ID and Customer ID, use it to build relationship with fact table. We don't need keep CompanyID. The answer should be A and A.

5099e2d 👍 1

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.

MYPE 👍 7

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.

Sowwy1 👍 1

I think it's B and C

Ous01 👍 2

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

Pegooli 👍 3

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

6d1de25 👍 3

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.

c2834e0 👍 1

By denormalizing the relationships will become many to many no ?

c2834e0 👍 1

forget what i said

vspeter 👍 3

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?

IK247 👍 6

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.

Lion007 👍 2

Correct. C and C C. A new key that combines the CompanyID and ProductID columns In a dimensional data model, especially in a star schema, it is important to create relationships that ensure uniqueness and properly link the fact table to the dimension tables. C. Denormalized into the Customer and Product entities The Company table should be denormalized into both the Customer and Product entities. This denormalization simplifies the model by embedding company information directly into the related tables, reducing the need for additional joins and improving query performance.

282b85d 👍 2

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.

2dc6125 👍 5

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.

66d0cf7 👍 2

Because It seems more then one product will have the same ProductID if you omit the company, otherwise the current key of Product would not be ProductID and CompanyID

2dc6125 👍 4

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.

stilferx 👍 3

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

stilferx 👍 1

Also, Company Entity may be omitted, if follow the rules literally - "no need in company".

israrkhan 👍 2

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

e0f0ce6 👍 1

The Product and customer Table already contains the CompanyId Information. As long as there are no futher information in the Company Table it can be omitted. If it can be omitted there is no need for changing the relationship, because in the current situation the one to many relation already works. In my opinion it should be A + A.

zerone72 👍 25

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

scorradi 👍 6

B - The union between the tables must take into account the company ID and the product ID. It is not possible to have only the Product ID because the company ID is part of the composite key. C - Regarding the entity, it is not possible to remove it. It is possible to remove the table only after denormalization. Some people make a relationship between the entity and the table itself, in this case it would be answer A, but I do not think that is the case here.

eeeliiisaaa 👍 2

I don't understand why not the first one is C. I think we need more information but for example in power bi models you ca nnot do relationships based in two columns, you need a combined key so I think all Fabric datamodels should work the same way.

okamal 👍 3

C And C

Sd1988 👍 5

If company has no other attribute it can be omitted

metiii 👍 9

The given answer is correct. The company entity should be denormalized into both product and customer tables. Company ID is part of the customer table and you cannot just denormalize it into the product table. and for the relationship between OrderItem and Product, you cannot just reference a part of the primary key. This is not how referencing works, your relationship would be wrong, if you only use product id and a product id is repeated for two different company IDs your relationship would consider both of them as the same product.

thuss 👍 39

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.

d47320d 👍 18

Agree up to the denormalization part. But that does not imply that the relationship between OrderItem and Product should only be based on ProductID. The Product table has two columns as a primary key, ProductID and CompanyID. After denormalization, a two columns join should be performed in order to establish the proper relationships and proper analysis. So correct answers are B, C i.e. the given one.

sraakesh95 👍 1

B - Since company and product both are keys within the Product table B - Since, an analysis is being performed on the date, product and customers, we need to denormalize the company with either the customer or product. But, denormalizing the company with the Customer would create unnecessary redundancy, hence, we need to consider only the Product for denormalization.

XiltroX 👍 4

Here are my two cents on what should be the right answer: 1. Dependent on ProductID col only - If you look at the question, its asking what the relationship betwen OrderItem and Product should be. The answer is simply the ProductID col as the OrderITEM table is a fact table and having ProductID as a secondary key is the best and only solutoin. 2. The Company entity (table) must be denormalized to Product table - Again, its already convenient that we have CompanyID in Product table. So its only logical that we denormalize it in Product table only.

Momoanwar 👍 3

Correct

Comments & Corrections

No comments yet — spotted an error or have a note? Share it below.

Expert Analysis

Why the Answer Is Correct

The Product table's primary key is the composite of ProductID and CompanyID, so ProductID alone does not uniquely identify a row. Because the Power BI/Analysis Services model view only permits single-column relationships, the correct fix is to add a calculated key column that concatenates CompanyID and ProductID in both OrderItem and Product and use that column for the relationship. Company attributes are then denormalized into both the Customer and Product entities, which preserves company information while allowing the Company table to be removed from the star schema. This yields clean, single-hop dimensions for date, product, and customer analysis.

Why Other Candidate Answers Are Wrong

Selecting only ProductID for the relationship fails because ProductID repeats across companies, so the join would fan out or misdirect rows and produce wrong order-item aggregates. Choosing to join on both the ProductID and CompanyID columns directly is not implementable in the Model view, since multi-column relationships are unsupported there (a Power Query merge would be needed instead, which is not this answer area). Ignoring or merely omitting the Company entity without denormalizing it discards company attributes and leaves the composite key problem unresolved, breaking the dimensional grain.

Community Comment Notes

MYPE observed that "there is no possibility to create a relationship based on 2 columns" and that this must be done in Power Query instead, supporting the combined-key choice. metiii argued that "you cannot just reference a part of the primary key," warning that a ProductID-only relationship would be wrong. thuss and IK247 both favored denormalizing company data so the CompanyID no longer drives the join, while d47320d countered that a two-column join was still required. zerone72 and 2dc6125 pushed the opposite view that the company table could simply be omitted, but that ignores the composite key and the attributes the Company table carries.

Exam Strategy

For hotspot items, first identify the key grain of every table and check whether any primary key is composite; then remember the Model view only supports single-column relationships, so composite keys must be flattened into a surrogate/combined column before relating tables.

Frequently Asked Questions

Why can't I relate OrderItem to Product using only ProductID?

ProductID is only part of Product's composite primary key, so it repeats across companies and would not uniquely match each row, producing incorrect order-item analysis.

Why does the Company table need to be denormalized into Customer and Product?

Denormalizing company attributes into both tables preserves the data while letting you remove the Company table, keeping a simple star schema for analysis by date, product, and customer.

More DP-600 FAQ →

Related Analysis

Practice All DP-600 Questions

Access 115 questions with complete answers and detailed explanations.

View Full DP-600 Practice Test →

← Back to DP-600 Study Guide