Business Intelligence and Data Visualization

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.

PART B: Case Study Description and Assignment Tasks

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

sold figure?
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.

Assignment help

  1. 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).

  1. 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.

Task C (40%) is to provide further data analysis using PowerPivot. These must be of between8&10trendsornoteworthyobservationsthatyounoticewithinthedata.This must appear in a single Excel workbook namedAss1_TeamNN_TaskC

TheanalysismustbevisuallyappealingusingeasytounderstandPivotSheetsandany data visualizations that areappropriate.
  • Each of the trends or observations must appear in a newworksheet.

  • Eachworksheetmusthaveanappropriatelynamedtab(notSheet1,Sheet2…).

  • 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.

Examples of observations could be:
  • Female customers never order on aFriday;

  • Male customers have halved in NSW over the past 3years;


    • Products4,23,31and47haveallhadsuddendropsinsalesatexactlythe

    same times over 3 years.

    The presentation of data is important:
    • 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.


    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.

    [recent_posts limit="12"]