Design a data mart schema to satisfy leisure

Assignment Help Other Subject
Reference no: EM133463134

Database Management

Assignment - Data Marts and Business Intelligence

Demonstration Session: Part 1: LeisureAustralasia Data Mart Design 

In this section you will design and document a data mart star schema for the
LeisureAustralasia (the scenario discussed in Assignment 1).

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.

Part 2: Business Intelligence Reporting

Download and restore the WorldWideImporters (WWI) Data Warehouse sample database. Download WideWorldImportersDW-Full.bak file from

The following information about WWI are extracted from 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 
Create the following report using SQL Server Reporting Services. Save the project as A2_<student name>_SSRSReport. The report provides the monthly and yearly sales for Califormia for all years.

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 
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_<student name>_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 - Video Presentation

In addition to your written report, you will record a max 5 minute video presentation about your Business Intelligence analysis findings. Your presentation should be professional with some form of visual demonstration (i.e. slides). At the beginning of your video, please show your face and your photo id. Save your presentation document/s as A2_<student name>_Presentation and name your video file A2_<student name>_Video in mp4 format.

Demonstration Requirements

In addition to the above submission requirements, each student must attend a marking demonstration session. This session will be held on July 27th/July 28th tutorial sessions. Online students will join in the demonstration via the Zoom session on Thursday 27th July 4pm - 6pm.

Demonstrations are required for:

Part 2A: SQL Server Reporting Services Report You will open your SSRS project and show a preview of the report, showing the correct report data and formatting.

Part 2B: Data Analytics You will open and explain the Business Intelligence tools / projects you have used for your analysis. This may be SQL Server Analysis projects, Power BI or Excel BI projects. You will not deliver your presentation as this is still done as a video submission.

Reference no: EM133463134

Questions Cloud

Discuss the types of tension the show employs : Discuss the show's premise, genre, style and tone. Discuss the show's broadcast history. Discuss the types of character architecture and dynamics the show
What are some ways that qualitative research characteristics : What are some ways that qualitative research characteristics are fundamentally different from quantitative research? What is the role of gatekeepers?
What managerial and organisational changes : Advise your Clients organisation what managerial and organisational changes they must make to significantly improve their performance and also advise
Who was bartholome de las casas : Who was Bartholome de las Casas? What of significance did he do? What were two consequences of his actions? Include where he was and the date of his actions.
Design a data mart schema to satisfy leisure : INFO6002 Database Management, The University of Newcastle - Design a data mart schema to satisfy LeisureAustralasia decision makers' information needs
Basic moral dictums of prevailing culture : Anyone who disagrees with the basic moral dictums of the prevailing culture should be censored.
Discuss the ethical and legal implication : Discuss the ethical and legal implication involved in the scenario selected.
Understanding of system acquisition process : Having read System Acquisition you should have a good understanding of the system acquisition process
Global health priority dilemmas : How can international government agencies address such global health priority dilemmas?

Reviews

Write a Review

Other Subject Questions & Answers

  Cross-cultural opportunities and conflicts in canada

Short Paper on Cross-cultural Opportunities and Conflicts in Canada.

  Sociology theory questions

Sociology are very fundamental in nature. Role strain and role constraint speak about the duties and responsibilities of the roles of people in society or in a group. A short theory about Darwin and Moths is also answered.

  A book review on unfaithful angels

This review will help the reader understand the social work profession through different concepts giving the glimpse of why the social work profession might have drifted away from its original purpose of serving the poor.

  Disorder paper: schizophrenia

Schizophrenia does not really have just one single cause. It is a possibility that this disorder could be inherited but not all doctors are sure.

  Individual assignment: two models handout and rubric

Individual Assignment : Two Models Handout and Rubric,    This paper will allow you to understand and evaluate two vastly different organizational models and to effectively communicate their differences.

  Developing strategic intent for toyota

The following report includes the description about the organization, its strategies, industry analysis in which it operates and its position in the industry.

  Gasoline powered passenger vehicles

In this study, we examine how gasoline price volatility and income of the consumers impacts consumer's demand for gasoline.

  An aspect of poverty in canada

Economics thesis undergrad 4th year paper to write. it should be about 22 pages in length, literature review, economic analysis and then data or cost benefit analysis.

  Ngn customer satisfaction qos indicator for 3g services

The paper aims to highlight the global trends in countries and regions where 3G has already been introduced and propose an implementation plan to the telecom operators of developing countries.

  Prepare a power point presentation

Prepare the power point presentation for the case: Santa Fe Independent School District

  Information literacy is important in this environment

Information literacy is critically important in this contemporary environment

  Associative property of multiplication

Write a definition for associative property of multiplication.

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd