HOTSPOT -
You have the source data model shown in the following 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.
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.
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.
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.
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
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.
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.
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.
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
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.
If company has no other attribute 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.
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
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
Also, Company Entity may be omitted, if follow the rules literally - "no need in company".
it can have all the answers C : A new surrogate key which combines Product and Company B:Denormalized Company into product table
By denormalizing the relationships will become many to many no ?
forget what i said
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 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.
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.
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.