COMP3350 Business Intelligence Assignment

Assignment Specifications

This assignment has 3 sections.

Section 1: Data warehouse Design (5 marks)

In this section you will design a data warehouse schema for the Leisure Australasia (the scenario discussed in Assignment 1).

Design a data warehouse schema to satisfy Leisure Australasia’s decision makers’ information needs. You need to only design the data warehouse schema only. You do not need to implement it.

You need to write a short report explaining subject-area/s covered by your data warehouse, illustrate the documented schema and discuss how the data ware house satisfies the information analysis needs of the University. Give examples of analysis queries that your design supports.

Save your document as DatawarehouseDesign_LeisureAustralasia_.docx.

Section 2: ETL Exercise (3 marks)

In this section, you will create an ETL task to load Customer Data to a table. Your group is already provided with sample text data of Customer data.

• Customer data: Data on customers (CustomerData.txt)

Your group is asked to load this data into SQL Server database called Staging_Area by creating a Server Integration Services (SSIS) project, called Assignment3__ETLExercise

Next, create an SSIS Package called LoadingCustomerData to load Customer data.

You have been informed that there have been errors in your SSIS package when loading Customer data. That is, in some records, the following fields have incorrect data:

• City
• StateProvinceCode
• StateProvinceName
• Country Code
• Country Name
• PostCode

You need to create a SSIS package called LoadingCustomersCorrected which loads the data correctly for all records in the CustomerData.txt file

Hints:

• In CustomerData.txt file, the StreetAddress field in some instances contains a “,” (comma) which is also the value used to denote the end of a field value.
• You may need to explore constructs for data cleaning and transformation in SSIS such as Conditional Splits and Derived Columns

Section 3: Business Intelligence Report (12 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 A: Reporting (2 marks)

You are asked to create a report using SQL Server’s Reporting Services. Save the project as Assignment2__SQLReports. The report provides the monthly and yearly sales for Califormia for all years.

The format of the report is given below:

Note that the data is sample data and does not pertain to correct values in the actual database.

Part B: Data Analytics (10 marks)

Explore the data warehouse schema and data. Select subject area(s) that your group would like to analyse in WWI. Create data mart(s) either using SQL Server Analysis Server or directly import the data to Power BI data model to analyse. Save the project/file as Assignment2__DataMarts.

…Analyse the data and write a Business Intelligence report based on World Wide. Use PowerBI’s visuals, dashboards etc. in your analysis, data visualisation and presentation.

Note that your BI report is presented to the business management team of World Wide Importers such as CEO and senior management, so your BI report should be understood by business decision makers of WWI.

Write a report detailing data analysis, information discovered and present helpful insights and actions items from your data analysis. Use appropriate tables, charts, graphs etc. to present your findings. In addition to the written report, you need to present your BI report to class on Apr 25th -26 th tutorial session. Your group’s presentation should not exceed 10 minutes.

Save your report as BusinessIntelligenceReport_.docx.

Are you struggling to keep up with the demands of your academic journey? Don't worry, we've got your back! Exam Question Bank is your trusted partner in achieving academic excellence for all kind of technical and non-technical subjects.

Our comprehensive range of academic services is designed to cater to students at every level. Whether you're a high school student, a college undergraduate, or pursuing advanced studies, we have the expertise and resources to support you.

To connect with expert and ask your query click here Exam Question Bank