ITP 249
HW10
Points: 100 Due: 06:00pm Friday April. 24th
Data visualization using Tableau®
Nitin Kalé, University of Southern California
Nancy Jones, San Diego State University
Objective
Use data visualization to analyze ERP simulation game data using Tableau.
Activities
- Import and prepare data
- Perform data cleansing and harmonization
- Create data visualizations
- Share results as a story
Software Prerequisites
- Tableau 2018.3.3 or newer
UCC Products Required
- None
Data Required
- ERPSIM dataset is available in ERPSIM_E7_1.xlsx
Scenario
Many courses at SAP University Alliances member schools use ERP Simulation Game (ERPSIM) to introduce students to the role of integrated business processes using a business simulation game. This game simulates a commodity market wherein teams have to plan, procure, produce and sell products in a competitive environment. The goal is to strategize and run their company for valuation maximization. The game is played in the SAP ERP system while an external simulator simulates the dynamic market and all the variables that influence the game. This exercise can be used during or at the end of the game to analyze game data. To learn more about ERPSIM, go to erpsim.hec.ca
Data visualization
The human visual system has evolved to be particularly good at recognizing patterns. Data visualization has become a standard analytical tool which capitalizes on the ability of humans to recognize patterns within massive quantities of multi-dimensional data generated by business information systems. Many scientific studies have led to the creation of visualization models that utilize human perception and cognition.
When the number of dimensions is small, we can use standard graphing techniques for visualization e.g. bar charts, line charts, histograms, pie charts and scatter plots. In this exercise, you will use basic visualization techniques to analyze ERPSim data.
What is ERPSIM?
The ERPSim game is played by teams over several rounds (up to 12 rounds of 20 virtual days each). The teams sell up to 12 products that the market consumes. The products are all muesli cereal1 in various flavors and box sizes. The teams must forecast demand, run MRP, procure, produce, price and market their products for sale.
Figure 1 shows the entire cash-to-cash cycle in the game. The transactions in bold are decision points that teams must make and execute. They are considered strategic in nature. The transactions that are gray are considered operational in nature. They are automated by the simulator. Additionally, teams can run analytical reports at various points in the game to monitor and strategize.
Figure 1: Cash-to-cash cycle2
Data from an actual game have been extracted from SAP ERP and stored in an Access database. Then queries have been written to report important findings. Those results have been exported to Excel.
Data Visualization for ERPSIM
You will now use Tableau to connect to ERPSim game data, and to visualize and discover any interesting trends.
- Install Tableau. The software can be downloaded at this link.
- Request a student access key at this link.
- Launch Tableau, when prompted click on “Activate my key” and insert the key sent to your email.
- Connect to a File, Microsoft Excel, and use the ERPSim data in the Excel file ERPSIM_E7_1. You should have 6,558 rows of data (Click on Sheet1 on the left panel, View Data).
Choosing measures and dimensions
What is a measure? A measure is a field on which calculations can be made. These are fields of business interest for analytics. e.g. revenue, profit, quantity sold. The calculations can sum, min, max, average, count etc. Measures are also called key figures or facts.
What is a dimension? A dimension is reference information about a measure. It provides context for the measures; such as, customer, time, product. Revenue by customer is an example of how you would report a measure by a dimension.
- Click on Sheet1 at the bottom of the screen.
- Since the values in Round are numeric, Tableau has incorrectly identified Round as a measure. Round should be a dimension.
- Click on the drop down list arrow to the right of the Round “pill” and choose Convert to Dimension. Notice that Round is now a dimension and the Round pill has changed from green to blue.
Figure 2: Dimensions in Tableau
- Change the aggregation of Price to Maximum by clicking on the drop down list arrow, Default Properties, Aggregation, Maximum.
Figure 3: Changing A Measure’s Aggregation Method
- Change the Number Format to currency, €.
Figure 4: Format the Number as Euros
- Change the Number Format for Revenue to currency, Euros € to match the format of Price.
-
You
are now ready to manipulate and visualize these data.
- Several charting options are available for visualization – bars, lines, pies, geographic, scatter/bubble, maps, etc. Tableau will allow you to use only certain visualizations for your data based on best practice and your data type. You can see the recommended visualizations on the Show Me tab to the right of your visualization canvas
- Using the appropriate charting technique, answer the following questions.
- You will be creating a story of all your visualizations. This story will have 11 story points to answer the 11 questions. Caption each story point with the query you are answering. For each query, you will create a duplicate of the original sheet and manipulate the data to answer the query.
-
Your
submission should include the following:
-
A
word doc with an answer to each question and a graph to support
the answer.
- Ex. Which team had the highest revenue? Team A, with a graph to support this
- The story you created with all of the story points exported as .twb file
- Drop the two elements into a folder named lastname_firstname_hw10 and zip the file.
-
A
word doc with an answer to each question and a graph to support
the answer.
Creating a Story
Go to the Story tab to create a story as shown in the following figure.
Figure 6: The Story Tab of Tableau
Tableau will allow you to choose any or all of your visualizations to include in your story. You can add pages to your story by clicking on the New story point, Blank. The new blank story page will be added to the right of your current worksheet page. You can create a slide show to view your story at any time by clicking on the Presentation mode icon in the upper right of your screen.
Create a story with 11 pages, one for each question. Use captions and/or text boxes to make sure your answers to the questions are clearly shown. Assume you are presenting to your team at work and you are on a strict deadline. In other words, create your visualization to create the biggest impact and impart your message quickly and effectively. Use background color, fonts, and other visualization tools to enhance your worksheets in your story.
Name each worksheet using the following convention:
Your Name Question X where X is 1 through 11 for each of the questions. (Look at the example in Figure 7 for a fictitious question “15”.)
Figure 7: A Sample Story
You can share your story in various formats. You can Print to pdf; Export the image; save as a Tableau packaged workbook that can be read by viewers without the full Tableau version; save as Tableau workbook, that gives the viewer full rights to your workbook; and you can share online.
Queries
Question 1: Which team had the highest revenue? What was the revenue? Sort your graph by total revenue to find these answers.
Question 2: What product had the highest revenue? What was the revenue?
Question 3: Display the trend of revenue over rounds for each team.
Hint:
Trend charts require a date field to plot the measure against. We need to convert Round to a date field. Click on the Round drop arrow and convert it to date field.
Figure 5: Change Data Type
Add Round to Columns and Revenue to Rows. Then click on the Round pill , and Choose Month option. Add Team to the Color icon in Marks. Choose the line chart option as the visualization. Be sure that team names are displayed and labeled.
Question 4: What is the market share of each team by product? (Only submit the screenshot, no need to list each team and each product)
Hint: Use a stacked bar chart. Show the market share in %. Help with this can be found at this link.
Question 5: Are there any products that don’t sell in specific distribution channels? Explain.
Hint: Use a highlight table.
Question 6: What were the highest prices for each product for each team? List any three teams and their highest priced item. Which team sold the most expensive Muesli?
Hint: Use a box and whisker plot.
Question 7: Which team sold the most quantity of muesli? What was their most sold product?
Hint: Use a treemap plot.
Question 8: Show the correlation between price and revenue by product. Describe the correlation (ex. strong, weak, positive, negative, etc.)
Hint: Use a scatter chart.
Question 9: Show the revenue for each team on each day. What is Team OO’s revenue on May 4?
Hint: Look at the raw data to understand how a singular day would be filtered. When selecting the data by date, ensure it is being selected as a dimension instead of a measure. Dimensions appear as blue while measures appear as green. Make sure Day is continuous, not discrete, and Month is chosen from the Rounds and is discrete and not continuous
Question 10: What team made the highest revenue on a single day and in which round?
Hint: Use a tree map and filter the visualization to help you find the highest revenue. In your story and screenshot, present the unfiltered tree map.
Question 11: What product on what day and round brought the highest revenue for which team?
Hint: Use a tree map and filter the visualization to help you find the highest revenue. In your story and screenshot, present the unfiltered tree map.
1 http://en.wikipedia.org/wiki/Muesli
2 Léger, P.-M., Robert, J., Babin, G., Pellerin, R. and Wagner, B. (2007), ERPsim, ERPsim Lab, HEC Montréal, Montréal, Qc.
The post Data visualization using Tableau appeared first on My Assignment Online.