TABLEAU LAB#2

1. TABLEAU LAB#2 2601404 Business Reporting System

2. Agenda • Scraping data • Creating Geocode • Creating Calculated Field

3. Scraping Data • A technique to use a computer program extracting data from another program, for instance; • Screen scraping • Web scraping • Free data sources • http://www.freebase.com/ • http://www-958.ibm.com/software/analytics/manyeyes/ • http://aws.amazon.com/publicdatasets/ • http://www.lib.berkeley.edu/wikis/datalab/ • http://lib.stat.cmu.edu/DASL/

4. Exercise • Let’s find free public data – Bangkok Population • Google and you will find http://203.155.220.230/info/esp/population_Dec54.htm • Copy and clean the data

5. Formatted Data

6. Geocoding • A process of identifying geographical coordinates (latitude and longitude) from other geographical data • Geocoding Web • http://itouchmap.com/latlong.html • http://universimmedia.pagesperso-orange.fr/geo/loc.htm • http://www.findlatitudeandlongitude.com/batch-geocode/ • Reverse Geocoding : transform geocode into geographic information • https://developers.google.com/maps/documentation/javascript/examples/geocoding-reverse

7. Batch Geocoding http://www.findlatitudeandlongitude.com/batch-geocode/

8. Merging Data Paste into Excel and use Text to Column (in Data Tab)

9. Merged Data

10. Creating BKK Population Dashboard • Connect Tableau to the BKK Population Data and correct measures and dimensions

11. Creating BKK Population Dashboard • Create a calculated measure

12. Creating BKK Population Dashboard • Compare Density and Aggregate Density

13. Exercise: Create Your Own Dashboard

14. Add Google Map to a Dashboard • Drag a Web Page onto your dashboard from the left-center panel • From the Dashboard file menu, click Actions and click the “Add Action >” button and choose “URL…” • In the Add URL Action dialog box, select whatever sheet you have created thatincludes the fields LAT and LON, and choose what event you’d like to trigger navigation to the new image. In this case, select Map as Source Sheet and Select as my trigger event, but you could trigger the action from a table or other type of sheet. Here’s what the dialog box looks like: http://dataremixed.com/2013/06/how-to-embed-a-google-map-in-tableau/

15. Add Google Map to a Dashboard (Con’t) • Now comes the magic. Copy and paste the generalized url above to the URL field of the dialog box, and replace <LAT> and <LON> with the corresponding field names in your data source by clicking the small arrow to the right of the URL text entry field: https://maps.google.com/maps?q=<LAT>,+<LON>&z=17&t=h&output=embed http://dataremixed.com/2013/06/how-to-embed-a-google-map-in-tableau/

16. Google URL • “q=<LAT>,<LONG>″ – these are your coordinates. Note that if you have an address field instead of Lat/Long, you can put an address after “q=” as well • &z=17 – this specifies the zoom level. Higher numbers zoom in, lower numbers zoom out • &t=h – this specifies the type of map. (t=m is a map, t=h is a satellite view) • &output=embed – this is a key parameter that makes sure the website you embed in your viz doesn’t include the entire site – just the map itself http://dataremixed.com/2013/06/how-to-embed-a-google-map-in-tableau/

17. Final Dashboard

18. Sending a Packaged Workbook • Workbooks often reference external resources. If you want to share your workbook with someone who does not have access to the referenced resources or Tableau Server, you can save and then send them the packaged workbook instead. A packaged workbook (.twbx) contains a Tableau workbook, and may contain one or more of the  following local files: • Background images • Custom geocoding • Custom shapes • Local cube files • Microsoft Access files • Microsoft Excel files • Tableau data extract files (.tde) • Text files (.csv, .txt., etc.)

19. Creating and sending a .twbx with a file-based data source • When your workbook uses a file-based data source, such as Microsoft Excel or a text file, the data source will be included in the packaged workbook. Simply save the workbook as a packaged workbook (.twbx). • In the workbook, select File > Save As. • From the Save as type drop-down menu, select Tableau Packaged Workbook (*.twbx). Now you can send your workbook.

20. Creating and sending a .twbx with a file-based data source • If the workbook contains connections to enterprise data sources or other non-file-based data sources, such as Microsoft SQL, Oracle, or MySQL, the data must be extracted from the data sources for it to be included in a packaged workbook (.twbx). • Right-click the data source in the Data window, and select Extract Data.

21. Creating and sending a .twbx with a file-based data source • In the Extract Data dialog box, click the Extract button to extract all data from the data source.

22. Creating and sending a .twbx with a file-based data source • After the extract completes, the data source icon will change to indicate that an extract is active for that data source. Instead of a single cylinder, there will be two cylinders connected by an arrow. • Repeat steps 1-2 for each data source in the workbook

23. Creating and sending a .twbx with a file-based data source • Select File > Save As. • From the Save as type drop-down menu, select Tableau Packaged Workbook (*.twbx). • After the extracts have been created for all non-file-based data sources and the packaged workbook saved, you can now send your workbook.

24. Self-STUDY: PATH ANALYSIS ON MAP

25. Using the Path Shelf for From - To Pattern Analysis • Have you ever wondered how to create a view that shows a path between two points such as shipments or weather patterns like the views shown below? • This kind of path analysis requires a specific data structure and uses the Path shelf.

26. The Path Shelf • To create a view like this in Tableau Desktop, you need to first make sure you data source has the right data structure. • Create two rows of data for each path you want to trace-the "From" row and the "To" row. For example, when showing the path of a flight from Berlin to Chrast, you need a row for Berlin and a row for Chrast.

27. The Path Shelf • For each data pair, create a unique key that identifies them as a pair. In this example, there is a column called Path ID. For the two Berlin to Chrast rows, the Path ID is "Berlin to Chrast."

28. The Path Shelf • Create a column to define the path order: the direction in which the line is drawn. • For example, the row for Berlin has a path order of 1 and the row for Chrast has a path order of 2, which indicates that the flight was from Berlin to Chrast.

29. The Path Shelf • Now that you have your data source set up, connect to it in Tableau Desktop and build the view. • Frequently, a path analysis is geographical and is best shown as a map view. • From the Measures pane, drag Longitude to the Columns shelf and Latitude to the Rows shelf. • On the shelves, right-click Longitude and Latitude and select Dimension. • The view looks like this. You can select different color settings for the dots from the Color menu on the Marks card.

30. The Path Shelf

31. The Path Shelf • Add the marks by dragging the unique key that identifies From-To pairs to the Level of Detail shelf on the Marks card (in this example, the Path ID field is used). • On the Marks card, in the list, select Line

32. The Path Shelf • From the Dimensions pane, drag Path Order to the Path shelf. • Note: If you are trying to see a path over time (such as for weather patterns), you can put the Date field on the Path shelf instead.

33. The Path Shelf • You can further refine the analysis by adding fields to the Size, Color, and Label shelves.

34. Calculating Distance • http://stevemorse.org/nearest/distancebatch.html

35. Exercise – Distance from BKK