Answer & Explanation:All instructions in the atachment
Unformatted Attachment Preview
This assessment item addresses the following OBJECTIVES:
Objective 1: Identify corporate data resources and their value for modern business.
Objective 2: Explain database technologies for managing and supporting corporate data
Objective 3: To be able to evaluate advantages and disadvantages of database technologies and
their applicability in different business situations;
Objective 4: To develop an understanding of data exploration methods and tools and their role in
discovering knowledge locked in historical corporate data;
Objective 5: To improve your ability to communicate in the form of technical reports and
• Design a dimensional model schema that meets the CEO’s requirements.
• Provide sample queries which run against your designed model, which can extract reports
needed by the CEO.
• Provide the data dictionary to clearly show the transformation at the ETL layer.
Design of a Dimensional Model Schema – 25 Marks.
• Provide correct measures by providing relevant sales ratios, attributes and dimensions in your
new Dimensional Model Diagram. (15 Marks)
• Please provide an explanation for your design model. (5 Marks)
• Provide the data dictionary for your transformation at the ETL layer (2.5 Marks)
Query – 2.5 Marks
• Produce a query which can be executed in an Adhoc manner. Please provide explanation to
your query, regarding its execution and of the query you have written. Hint ( Number of
scenarios this Adhoc query can be executed.) (2.5 Marks)
Description of CEO’s Requirements: Sony wants to dominate the DSLR market. The companies’
executives are on an ambitious plan to revive sales of their brand. Traditionally Canon and Nikon
are the two serious competitors these brands have established themselves by selling proprietary
lenses which have a specific mount, for example Canon has a canon mount and Nikon has a
Nikon mount. In the current market 1 out of five cameras sold are Sony Dslr. The ceo wants
sales for the Sony brand sales ratio to be at least 4 out of every 5 cameras. After market analysis
by Sony the company found out that they could tap into this market by persuading Canon and
Nikon users to use their glass on a Sony DSLR. Provide correct measures by providing attributes
and dimensions in your new Dimensional schema. How do you think 4:5 (4 out of 5) sales target
could be achieved. The CEO wants to know the reason why people prefer brands. What do they
offer. If the customer chooses to buy any other brand including Sony, the ceo wants to know why
the customer chose to purchase that camera. The ceo also likes to know positive feedback and
negative feedback from the customers, if it can be captured or modeled.
Please follow this link and do the question as per the instructions.
Purchase answer to see full