Hi there ,
the assignment is run on SQL Sever Management Studio and then part of it also done in visual studio code. I do no need the written report but i need the programming part to be done up to task 5 .
Bachelor of Information Technology DS6504: Business Intelligence and Big Data Assignment 1: Interactive Dashboard Application Due date and time 5 pm Friday 11th September 2020 (Week 9) Submission details Please upload your zip file which includes your report and sql code into subsection Assignment Submission at section Assessment on Moodle. There is also a guideline for submitting reports by uploading files to Turnitin. Extensions and Late Submission Please refer to the Course Outline. Grading This assignment is worth 30% of the total mark of the course. The assignment will be marked out of 100. The assignment will be marked based on the assignment demonstration. If you do not come to the demonstration section, you will be marked Zero of this assignment. If you do not understand your assignment’s final product and is not able to reproduce or modify it during the demonstration, you can only get 50% of your total assignment marks. To pass this assignment, a grade of 50% must be achieved. Terms See details of terms in the Bachelor of Information Technology handbook 2020. DS6504: Business Intelligence and Big Data Assignment 1 School of Information Technology Page 2 of 10 Purpose This assignment requires you to develop an interactive dashboard reporting service that will visualise data extracted from a SQL Server database. The layout and content of this dashboard is shown in Appendix A. Data will be extracted from a database using a combination of Views and parameterised Stored Procedures that you will create according the specifications described in the Tasks section below. The dashboard will be created using the Reporting Services template within the SQL Server Data Tools environment. It will consist of a number of widgets including tables, gauges and a map, which change dynamically in response to user input. Tasks Create Views and Stored Procedures to extract data: 1. Download the database backup file called MaxMinManufacturingDW using the link in the Assignment One section in Moodle. Restore the database in SQL Server using the instructions on Moodle. 2. In SQL Server, create 3 views that will be used to provide data to stored procedures that you will define in Task 3. The content of each view is described as follows: a. Create a view called vInventoryByType. This view will display the inventory levels of products, their product type and product subtype name, and the date the inventory was taken. Each row will display the product type name, product subtype name, the inventory date and the inventory level of the product recorded on that day. b. Create a view called vAcceptedByCountry. The view will display the number of accepted products manufactured, the date of manufacture and the name of the plant and country where the product was manufactured. Each row will display the country code, country name, plant name, the date of manufacture and the number accepted products manufactured by machines at that plant on that day. c. Create a view called vRejectedProductsByType. The view will display the number of manufactured products (accepted products + rejected products) and the percentage of rejected products to this total. It will also list the date of manufacture and the product type and product subtype name. Each row will display the product type name, product subtype name, the percentage of rejected products, the manufactured products total and the date of manufacture. Refer to Appendix B to see the expected content within each of these views. You should not proceed to Task 3 until your output matches Appendix B. 3. Create 3 stored procedures that will use the views defined in Task 2. Each stored procedure will accept two input parameters; Year and Month. Additional processing performed by each stored procedures is described as follows: a. Create a stored procedure called spMaxInventoryByType. The stored procedure will use the view called vInventoryByType. It will display the highest inventory level value within each product subtype, recorded during the month of the year supplied by the input parameters. Each row returned by the procedure will list the product type name, the product subtype name and the highest inventory level value recorded out of all products within that product subtype, during the specified reporting period. DS6504: Business Intelligence and Big Data Assignment 1 School of Information Technology Page 3 of 10 b. Create a stored procedure called spAcceptedByCountry. The stored procedure will use the view called vAcceptedByCountry. It will display the total number of accepted products manufactured at a plant during the month of the year supplied by the input parameters. Each row returned by the procedure will list the country code, country name, plant name and the total number of accepted products manufactured at that plant, during the specified reporting period. In addition, the following element from each plant name should not be displayed 'Maximum Miniatures -' in the results returned by the procedure. c. Create a stored procedure called spAvgRejected. The stored procedure will use the view called vRejectedProductsByType. It will display the total number of products manufactured and the percentage of those products rejected within each product subtype, during the month of the year supplied by the input parameters. Each row returned by the procedure will list the product type name, product subtype name and the total number of products manufactured and the percentage of those products rejected within that product subtype, during the specified reporting period. Refer to Appendix C to view the output that should be generated from each of these stored procedures. You should not proceed to Task 4 until your output matches Appendix C. 4. Create a Report Server Project within SQL Data Tools that invokes the stored procedures developed in Task 3. Refer to Appendix A to see the layout and content of the required dashboard. The Report Server Project will have one Shared Data Source that will be used to connect your reporting application to the MaxMinManufacturingDM data warehouse. The project will consist of three Shared Datasets; one for each of the stored procedures created in Task 3. The project will have one report called MaxMinDashBoard that will display the data provided by these stored procedures. Each element of the report is described below. a. Create a Shared Dataset in the Solutions Explorer panel called MaxInventoryByType and bind it to the spMaxInventoryByType stored procedure created in Task 3. In the Report Data panel, create a Dataset object with the same name and set its properties so that it uses the Shared Dataset you have just created. Define a filter for this Dataset so that only rows where the inventory level exceeds a value of 10,000 are returned. Create a Table report item in the MaxMinDashBoard report and bind it to this Dataset. Refer to Appendix A for the required layout and formatting this reporting element requires (called Products Exceeding Maximum Inventory Level (10K) in the dashboard). b. Create a Shared Dataset in the Solutions Explorer panel called AcceptedByCountry and bind it to the spAcceptedByCountry stored procedure created in Task 3. In the Report Data panel, create a Dataset object with the same name and set its properties so that it uses the Shared Dataset you have just created. Table and Gauge Elements Create a Table report item in the MaxMinDashBoard report and bind it to the AcceptedByCountry Dataset. The table should display only the country name, the plant name and the number of accepted products returned by the Dataset. Add an additional column to the table and drag a Gauge reporting item into the new column. Set its display properties so that the gauge looks similar to the gauge shown Appendix A. The properties of the pointer should be set so that it behaves as follows. When the number of accepted products is: DS6504: Business Intelligence and Big Data Assignment 1 School of Information Technology Page 4 of 10 i. < 50000,="" then="" the="" pointer="" is="" coloured="" "red"="" ii.=""> 50000 and < 150000,="" then="" the="" pointer="" is="" coloured="" "orange"="" iii.=""> 150000, then the pointer is coloured “Green” Map Element Drag a Map reporting item onto the report and bind it to the AcceptedByCountry Dataset. Use the TM_WORLD_BORDERS shape file (link available on Moodle) to display the world map. The country code field in the Dataset should be used to match spatial and analytical data. The sum of the accepted products value should be the item to report on. This value should be visualised on the map by setting the colour of the country according to the following rules: When the sum of the accepted products is: i. 0 to 150000, then the country of manufacture is coloured “Red” ii. 150000 to 250000 then the country of manufacture is coloured “Orange” iii. 250000 to 350000, then the country of manufacture is coloured “Green” Refer to Appendix A for the required layout and formatting of these reporting elements (see the table titled Accepted Products By Country and Plant and the map titled Accepted Products By Country). c. Create a Shared Dataset in the Solutions Explorer panel called AvgRejected and bind it to the spAvgRejected stored procedure created in Task 3. In the Report Data panel, create a Dataset object with the same name and set its properties so that it uses the Shared Dataset you have just created. Create a Table report item in the MaxMinDashBoard report and bind it to this Dataset. Refer to Appendix A for the required layout and formatting this reporting element requires (called Manufactured Products By Type in the dashboard). When the values of 2009 and 4 are supplied to the Year and Month input parameters, the final dashboard should have a similar layout and the same data content as that shown in Appendix A. 5. Deploy your Report Server Project to the ReportServer_SSRS target server. Open Internet Explorer (run as an administrator) and navigate to the report server. Test your application using the Year and Month values of 2009 and 4. 6. Write a report that documents the design of the Report Server Project, as described in the Assignment Deliverables section overleaf. Assignment Deliverables Each group should submit the following items zipped as a single file (only one