代写LeisureAustralasia Data Mart Design代写数据库编程

Part 1: LeisureAustralasia Data Mart Design (5 marks)

In this section you will design and document a data mart star schema for LeisureAustralasia. The scenario was discussed in Assignment 1 Instructions.

Design a data mart schema to satisfy LeisureAustralasia decision makers’ information needs. You will create a single star schema diagram which focuses on one area of the business. You do not need to implement it.

Write a short report (approx. 200 words excluding diagram & queries) which includes:

A.  Documented schema (star schema diagram)

B.  Subject-area covered by your data mart

C. A discussion of how the data mart satisfies the information analysis needs of LeisureAustralasia

D. Give 5 examples of analysis queries that your design supports. These will be a description of the queries, not actual T-SQL queries.

Save your document as A2__ LeisureAustralasia.

Part 2: Business Intelligence Reporting (15 marks)

Download and restore the WorldWideImporters (WWI) Data Warehouse sample database.   Download    WideWorldImportersDW-Full.bak   file   from   https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0.

The following information about WWI are extracted from

https://docs.microsoft.com/en-us/sql/samples/wide-world-importers-what-is?view=sql-server-2017

Wide World Importers (WWI) is a wholesale novelty goods importer and distributor operating from the San Francisco bay area.

As a wholesaler, WWI's customers are mostly companies who resell to individuals. WWI sells to retail customers across the United States including specialty stores, supermarkets, computing stores, tourist attraction shops, and some individuals. WWI also sells to other wholesalers via a network of agents who promote the products on WWI's behalf. While all of WWI's customers are currently based in the United States, the company is intending to push for expansion into other countries.

WWI buys goods from suppliers including novelty and toy manufacturers, and other novelty wholesalers. They stock the goods in their WWI warehouse and reorder from suppliers as needed to fulfil customer orders. They also purchase large volumes of packaging materials, and sell these in smaller quantities as a convenience for the customers.

Recently WWI started to sell a variety of edible novelties such as chilli chocolates. The company previously did not have to handle chilled items. Now, to meet food handling requirements, they must monitor the temperature in their chiller room and any of their trucks that have chiller sections.

Workflow for warehouse stock items

The typical flow for how items are stocked and distributed is as follows:

• WWI creates purchase orders and submits the orders to the suppliers.

• Suppliers  send  the  items,  WWI  receives them, and stocks them in their warehouse.

• Customers order items from WWI.

• WWI fills the customer order with stock items in the warehouse, and when they do not have sufficient stock, they order the additional stock from the suppliers.

• Some customers do not want to wait for items that are not in stock. If they order say five different stock items, and four are available, they want to receive the four items and backorder the remaining item. The item would them be sent later in a separate shipment.

• WWI invoices customers for the stock items, typically by converting the order to an invoice.

• Customers might order  items   that   are not  in   stock.   These   items are backordered.

• WWI delivers stock items to customers either via their own delivery vans, or via other couriers or freight methods.

• Customers pay invoices to WWI.

• Periodically, WWI pays suppliers for items that were on purchase orders. This is often sometime after they have received the goods.

Additional workflows

These are additional workflows:

• WWI issues credit notes when a customer does not receive the good for some reason, or when the goods are faulty. These are treated as negative invoices.

• WWI periodically counts the on-hand quantities of stock items to ensure that the stock quantities shown as available on their system are accurate. (The process of doing this is called a stocktake).

• Cold room temperatures. Perishable goods are stored in refrigerated rooms. Sensor data from these rooms is ingested into the database for monitoring and analytics purposes.

• Vehicle  location  tracking.   Vehicles that  transport  goods for WWI include sensors that track the location. This location is again ingested into the database for monitoring and further analytics.

Part 2A: SQL Server Reporting Services Report (5 marks)

Create the following report using SQL Server Reporting Services. Save the project as A2__SSRSReport. The report provides the monthly and yearly sales for California for all years.

The format of the report is given below:

Monthly-Yearly Sales Report

(California)

Year	Month	Monthly Sales Amount
2001	January	$2,345.98
	February	$2,532.99
…
	Yearly Sales                  $1,232,322.99
2002	…

Note that the data is sample data and does not contain the exact values from the actual database. You do not need to deploy the report to a Reporting Server.

Part 2B: Data Analytics – Written Report (5 marks)

Explore the data warehouse schema and data. Select subject area(s) that you would like to analyse in WWI. Create data mart(s) either using either: SQL Server Analysis Server, or Power BI. Save the project/file as A2__AWorksDataMart.

Analyse the data and write a  Business  Intelligence  report  based  on  World  Wide Importers data using:

•    Power BI’s visuals, dashboards etc. in your analysis, data visualisation and presentation and/or

•    Excel’s BI features such as pivot tables, charts and graphs.

Your report will include data analysis, information discovered and helpful insights and actions items from your data analysis. Include appropriate tables, charts, graphs etc.  in your report to present your findings.

Note that your target audience for the BI report is the business management team of World Wide Importers such as the CEO and senior management, so your BI report should be understood by business managers.

Save your report as A2_< student name >_BIReport.docx

Part 2C: Data Analytics – Presentation (5 marks)

In addition to your written report, you will prepare a max 3-minute video presentation about your Business Intelligence analysis findings (overtimed presentations will be penalised).  Your  presentation  should  be  professional  with  some  form  of  visual demonstration   (i.e.    slides).   Save    your   presentation    slides   as A2_ name>_Presentation in pdf format.

Submission Requirements

All submission links are found on Canvas in Assignments / ASSIGNMENT 2. Part 1:

Submission link (Turnitin)	Assignment 2 Canvas Submission Part 1(Data Mart Design)
Component	Part 1: Data Mart report document
File name	A2_<student name>_ LeisureAustralasia
Format	PDF or MS Word (Must not be a .zip file)

Part 2A, 2B & 2C:

Submission link	Assignment 2 Canvas Submission Part 2
Components	Part 2A: Files for your SSRS project Part 2B: Project files for your WWI Data Mart Part 2C: Presentation file
File name	A2_<student name>.zip
Format	.zip folder containing the following sub-folders:
	Folder name	Components & file name
	SSRS	Part 2A: SSRS Project files A2_<student name>_SSRSReport

	DataMart	Part 2B: Project files for your WWI Data Mart A2__ WWIDataMart
	Presentation	Part 2C: Presentation file A2_< student name >_Presentation.