MENU
Exhibit.
Refer to the exhibit.
A data architect is working on a Qlik Sense app the business has created to analyze the company orders and shipments.
To understand the table structure, the business has given the following summary:
* Every order creates a unique orderlD and an order date in the Orders table
* An order can contain one or more order lines one for each product ID in the order details table
* Products In the order are shipped (shipment date) as soon as they are ready and can be shipped separately
* The dates need to be analyzed separately by Year, Month, and Quarter
The data architect realizes the data model has issues that must be fixed. Which steps should the data architect perform?
In the given data model, there are several issues related to table relationships and key fields that need to be addressed to create a functional and optimized data model. Here's how each step in the chosen solution (Option C) resolves these issues:
Create a key with OrderID and ProductID in the OrderDetails table and in the Shipments table:
By creating a composite key with OrderID and ProductID, you uniquely identify each line item in both the OrderDetails and Shipments tables. This step is crucial for ensuring that each product within an order is correctly associated with its respective shipment.
Delete the ShipmentID in the Orders table:
The ShipmentID in the Orders table is redundant because the Shipments table already captures this information at a more granular level (i.e., at the product level). Removing ShipmentID avoids potential circular references or synthetic keys.
Delete the ProductID and OrderID in the Shipments table:
After creating the composite key in step 1, the individual ProductID and OrderID fields in the Shipments table are no longer necessary for joins. Removing them reduces redundancy and simplifies the table structure.
Concatenate Orders and OrderDetails:
Concatenating Orders and OrderDetails into a single table creates a unified table that contains all necessary order-related information. This helps in simplifying the model and avoiding issues related to managing separate but related tables.
Create a link table using the MasterCalendar table and create a concatenated field between OrderDate and ShipmentDate:
A link table is created to associate the combined table with the MasterCalendar. By creating a concatenated field that combines OrderDate and ShipmentDate, you ensure that both dates are properly linked to the calendar, allowing for accurate time-based analysis.