Business Intelligence and Data Visualization Summer Semester 2017
Core Tasks – Group Assignment BI Analysis Tools Reports
PRICE 135 AUD TIME NEEDED: 24 HR
PART A: Assignment Information
Due Date: 11:59pm Friday 18 Jan, 2017
(Teams must be created by Wednesday 8 January 2017)
Assignment Structure: Written and computed reports for data analysis on John Steed's Online Emporium
Assignment Length: Task A (N/A); Task B (N/A); Task C (N/A); Task D (800-1000).
Assignment Groups: This assignment will be completed in groups of 3-4 students. If you are looking for a team to join, ask your convenor/tutor for assistance.
GroupData:Onceyourteamhasformed,pleaseinformyourconvenor.Youwillgeta team number from your convenor and use the team number to download the correct database file from Blackboard. It is important that you use the correct data for your team,otherwiseyouranalysisresultswillbeconsideredasincorrectduringthemarking process.Forexample,ProductFivemaybethebestperformingproductforTeamOne, while Product Five may be the worst performing product for TeamTwo.
Assignment Submission Procedure and Related Information
- ThefirstpageofyourwordorPDFsubmissionfortheassignmentshouldbeacover sheet (see Blackboard), which includes your team members’ names and theirIDs.
- Penalties for late submissionsapply.
John Steed's Online Emporium (JSOE) is an online retailer that sells over 50 products to customers mainly based in Australia. Data has been obtained from JSOE's transaction system and placed into a Microsoft Access database file. The database contains: Product, Customer and Sales data.
Task A (20%)
AnswerthefollowingveryspecificquestionsbyanalysingJSOEdatausingPowerPivot. For this task you only need to submit one word or PDF document. Cut and paste the table below into a word or PDF document, which should be saved under the name: Ass1_TeamNN_TaskA (where NN is your teamnumber).
|1. What is your teamnumber?|
|2. How many orders exist in your orderstable?|
|3. Which product(s) appears in the largest number|
of sales orders?
|4. Which product(s) has the largest total quantity|
|5. Which product has the largest number of orders|
made by females?
|6. What percentage of total female orders isthis?(Up to 2 decimalplaces)?|
|7. Which product(s) have the largest total quantity|
of sales in 2012?
|8. In which month of the year are sales largest for|
product 21, 22, 23?
(Underline the most appropriate)
|21: Jan Feb Mar Apr May Jun Jul Aug Sep|
Oct Nov Dec All-Same
22: Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec All-Same
23: Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec All-Same
|9. Which customer category generated the largest|
number of orders?
|10. How many orders generated the largest net|
profit? Net Profit is (selling price – cost price) * quantitysold
|11. List the product names of the top 10 products that|
produce the largest total net profit.
Task B (20%)
Provide basic data analysis using PowerPivot. For this task you only need to submit onewordorPDFdocument.CutandpastethedataorscreenimagesfromPivotSheets a word or PDF document, which should be saved under the name: Ass1_TeamNN_TaskB (where NN is your team number). Discussions should be supplied, which are presented and labelled in a format that is easy to read and understand.
- TaraKing,aproductionmanageratJSOEhassuggestedthataround10%ofJSOE product range should no longer be sold (dropped) byJSOE.
- Which products would you suggest that Tara King consider dropping? Justify yoursuggestions.
- Do you recommend that all/some of these products be dropped? Justify your recommendation (based on your limited knowledge of thebusiness).
- A few months ago, a column named CustValueRating was added to the customer table. The rating values are intended to reflect the 'value' of the customer to the business based on sales history. A score of 1 indicates that the customer is highly valued while a score of 4 indicates that the customer is in the group that should be least valued by the JSOE. Emma Peel, JSOE's leading sales person has doubted the validity of the values in thedatabase.
- Is Emma correct in doubting the CustValueRating values?Why?
- IfEmma’s doubting is correct, create a new CustValueRating value and show the top 20 (or thereabouts) customers. Describe the method used to calculate newvalues.
TheanalysismustbevisuallyappealingusingeasytounderstandPivotSheetsandany data visualizations that areappropriate.
- Each of the trends or observations must appear in a newworksheet.
- Graphs or charts must be clearlylabelled
- Your Excel Workbook must have a menu that assists the user to navigate between sheets. This can be as simple as 10 buttons on a Home Sheet and a Home button on each sheet. Or it can be some other type of menu navigationif youwish.
- Female customers never order on aFriday;
- Male customers have halved in NSW over the past 3years;
- You must use slicers & filters that will make it easy for a user to use, read and modify;
- You must include several paragraphs (in a text box or similar) describing the analysis. This mightinclude:
- Describing what you havedone;
- Describe why you thought the analysis would be of use toJSOE;
- Findings you haveobserved;
- Recommendations or actions that JSOE might consider based on findings.
The presentation of data is important:
Task D (20%)
Prepare an 800-1000 word report for Louie that outlines 1) importance and need for the ETL process; (2) potential problems that may be encountered performing ETL within the Sunshine Group; and (3) role of data stewardship in the data warehouse environment. For this task you only need to submit one word or PDF document, which should be saved under the name: Ass1_TeamNN_TaskD (where NN is your team number).
JSOE is only one of a number of businesses owned by The Sunshine Group. The businessesaregenerallyoperatedintheAustralia,NewZealandandArgentina.Some business are totally online, some are partially online with physical stores in retail shopping centres, while others have no online presence atall.
Louie De Palma, CEO of The Sunshine Group, wants to combine data from these businesses and put it into a Data Warehouse. Louie then wants to use BI tools to perform data analysis.