Tableau HW 2: Data Exploration and Chart Creation
Objectives of assignment: We will discuss how Tableau works with data and how it creates various charts in Part I and Part II. By the end of this assignment, you will understand
o Data uploading
o Data exploration: check the distribution of measures (outlier check)
o Chart creation
Deliverables: HW 2 document & .twbx file including four worksheets for Q8-Q11)
Part I. Understanding Data with Tableau Desktop
· HW data: Hospital Revenue Data (Excel)
· Deliverable: HW 2 Word Documents with your answers
Q1. First, go to connect pane and open an excel file named “revenue data.xlsx.” How many tables do you see on your left pane?
1) 3 sheets 2) 4 sheets 3) 5 sheets 4) 6 sheets
Next, let’s connect all worksheets in the excel file. Drag a fact sheet to canvas and then
· Merge with Fact table with fact.date key = dimDate.date key1 (*attention: you need to manually pick this PK-FK pair on Tableau)
· Merge with dimEmployee with fact.emp key=dimEmployee.emp key
· Merge with dimFacility with fact.fac key= dimFacility.fac key
· Merge with dimPatient with fact.pat key= dimPatient.pat key
· Merge with dimProcedure with fact.proc key= dimProcedure.proc key.
This is the reason why a fact table has a composite key with pairs of PK-FK from different tables to link numeric variables to textual (descriptive) variable in dimensions tables. Make sure you click “data interpreter” before data join.
Q2. To connect to multiple tables in a single data source at one time, what must be specified?
1) A blend
2) A calculation
3) A join
4) A hierarchy
Q3. (Circle one) “total charge”, “procedure standard price”, and “owed from patient” are dimensions ( True or False)
Q4. When you connect to a data source, Tableau automatically separates date fields into hierarchies so you can easily break down the visualization. A hierarchy (aka tree structure) is a structure made up of two or more levels of related dimensions. For example, nation-state-county-city-zip code is an example of a hierarchy (reference: https://help.tableau.com/current/pro/desktop/en-us/qs_hierarchies.htm).
1) Time hierarchy: Year- quarter- month- week
2) Location hierarchy: USA-Florida-County-City
3) Sport gear: Gym – Kickboxing – Cycle- Tennis
4) Shopping: Clothing-Men’s-Outer wear- Shirts
Q5. You notice that Tableau automatically select summation as aggregation for any measure. Now we want to change such aggregation from SUM to AVG (average value of procedure standard price). Since average is sensitive to outliers, we first check distribution of measure before averaging out. Typically, a box and whisker plot is used to check distribution of measures and detect outliers, using a five-number summary (Min-1st quartile (Q1)-2nd quartile (Q2, median) -3rd quartile (Q3)- Max ). Outliers are less than Q1- 1.5IQR or greater than Q3+1.5 IQR, where Interquartile range (IQR) = Q3-Q1 as