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
others
Description
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
