Comprehensive Tableau and PostgreSQL Data Visualization Guide

Course Context and Schedule Management

  • Curricular Progression:
    • Data Visualization Modules: Applied visualization hands-on exercises occur in Week 1212.
    • Programming Language Transitions: The subsequent instructional module introduces the Python programming language.
    • Cross-Course Curriculum Overlap: The foundational introduction to Python in this course duplicates the conceptual slide material covered in course 60606060, covering Python syntax, language fundamentals, and the variable explorer. The deep-dive programming exercises diverge significantly between the courses, but the initial language lecture is equivalent.
    • Instructional Scheduling: Course 60606060 utilizes single 22-hour-and-4040-minute instructional blocks incorporating both lecture and exercises, whereas this curriculum deliberately partitions lectures across separate class days.

Tableau Environment and Licensing Configuration

  • Software Deployment and Versioning:

    • Tableau operates reliably across older and updated releases without functional discrepancies in core features.
    • Unlike software such as Camtasia—which can restrict functionality or invalidate licensing upon updating—Tableau maintains backward compatibility and permits updates without requiring annual license renewals.
  • License Key Activation Workflow:

    • When an academic or enterprise license key expires, the application prompts with an Activate with product key dialog.
    • Navigate to Help or the initial activation splash screen and select Activate with product key.
    • Input the designated course or institutional product key and verify an active internet connection before clicking Activate.
  • Workspace Interface Components:

    • Connect to Data: Supports diverse data sources, including Tableau Server, local flat files (Microsoft Excel, CSV, text files, JSON), spatial databases, relational databases, and PDF documents.
    • Relational Database Connectors: Frequently used server connectors include PostgreSQL, Oracle, and Amazon Redshift. Connectors not pinned to the primary interface can be located by navigating to To a Server > More and searching for the driver name.
    • Driver Prerequisites: Relational database connections require corresponding Java Database Connectivity (JDBC) or Open Database Connectivity (ODBC) driver files (such as .jar files) positioned inside the local system's designated Tableau drivers directory.
    • Accelerators and Sample Workbooks: Pre-packaged templates and sample workbooks containing bundled data allow exploratory visualization prototyping.

PostgreSQL Database Architecture and Permission Provisioning

  • Role-Based Security Configuration in pgAdmin:

    • User login roles are established at the PostgreSQL server cluster level, making them globally accessible across individual databases (e.g., COE6_6430 or ARMC).
    • Principle of Least Privilege: Production and reporting environments require isolated service credentials with restricted privileges to prevent data exfiltration or unauthorized database writes if a client-side workbook is compromised.
  • SQL Permission Scripts:

    • Execute the role creation command in isolation using the pgAdmin Query Tool to avoid syntax execution conflicts if the role already exists:
    • Role creation command establishes the credential coe_external with password authentication.
    • Execute explicit GRANT statements to provision strictly read-only access to the target reporting schema homework4.
  • Database Connection Parameters:

    • Server / Hostname: localhost (resolves to local instance).
    • Port: Default PostgreSQL port is 54325432. Alternative local instances may bind to ports 54335433 or 54345434 (verifiable via PostgreSQL server properties).
    • Database Name: COE6_6430 (case-sensitive identifier).
    • Username: coe_external
    • Password: coe_external_password
  • Data Schema Characteristics (homework4):

    • Entity Tables: departments, disease_types, diseases, encounters, medication, medication_types, patients, providers.
    • Patient Volume: Exactly 100,000100{,}000 patient records.
    • Encounter Volume: Exactly 1,718,3151{,}718{,}315 encounter records.

Exercise 1: Provider Profiling and Basic Visualizations

  • Tableau PostgreSQL Connection Protocol:

    • Connect via the PostgreSQL server connector using server localhost, port 54325432, database COE6_6430, username coe_external, and password coe_external_password.
    • Schema Filtering: The direct PostgreSQL connector lacks a schema drop-down menu. Access tables by clicking the search/magnifying glass icon in the left-hand panel and querying HW4. to isolate tables within schema homework4.
    • Live Connection vs. Extract: A live connection queries the database directly in real-time. An extract aggregates and serializes the dataset locally into a proprietary Tableau .hyper file, enabling offline data portability.
  • Variable Types and Metadata Display:

    • Data types are marked by icons: # indicates numerical data; Abc indicates text/string variables; calendar-clock icons denote date and datetime fields.
    • Blue Pills: Represent discrete variables, utilized primarily as dimensions or categorical row/column headers.
    • Green Pills: Represent continuous variables, utilized primarily as quantitative measures or aggregated calculations.
    • Italicized Fields: System-generated metrics created automatically by Tableau (e.g., Providers (Count), equivalent to SQL COUNT(*)).
  • Worksheet 1: Discrete Status Counts (status):

    • Place categorical variable status on Rows and system-generated measure Providers (Count) into the table text/canvas.
    • SQL Relational Equivalent:
    • SELECT status, COUNT(*) FROM homework4.providers GROUP BY status ORDER BY status;
    • Data Results: Active providers count is 2,1972{,}197; inactive providers count is 3,0213{,}021 (total: 5,2185{,}218).
    • Sheet renaming: Double-clicking the worksheet tab updates both the tab name and the canvas sheet title.
  • Worksheet 2: Highlight Table (status_ht):

    • Duplicate worksheet status (avoiding Duplicate as Crosstab).
    • Open the Show Me palette and select Highlight Table (requires exactly 11 dimension and 11 measure).
    • Palette Formatting: Under the Marks card, select Color > Edit Colors to apply uniform continuous palettes (such as monochromatic blues) across related reports. Hexadecimal codes can be imported directly to match organizational brand guidelines.
  • Worksheet 3: Stacked Bar Chart Evaluation (status_sbc):

    • Selecting a stacked bar chart via Show Me illustrates that single-variable dimensional distributions often yield visually uninformative stacked blocks, serving as a negative design indicator.
  • Worksheet 4: Provider Gender Distributions (gender):

    • Place gender on Rows and Providers (Count) on Columns; select horizontal bar chart.
    • Drag Providers (Count) to the Label shelf (T icon) on the Marks card.
    • Transform the measure into a relative proportion by right-clicking the measure pill on the Label shelf and choosing Quick Table Calculation > Percent of Total.
    • Preserves raw counts along the horizontal quantitative axis while displaying normalized percentage values directly on individual bar headers.
  • Worksheet 5: Stratified Demographics via Side-by-Side Bar Chart (gender_sbs):

    • Place status followed by gender on Rows, with Providers (Count) on Columns.
    • Select Side-by-Side Bars via Show Me. Use Swap Rows and Columns (Ctrl+W) if axes invert during generation.
    • Add Providers (Count) to the Label shelf and set to Quick Table Calculation > Percent of Total.
    • Table Calculation Partitioning:
    • By default, percentages compute across the entire table (summing all 44 bars to 100.00%100.00\%).
    • To compute relative distributions within each administrative partition, right-click the label measure pill, select Compute Using, and choose Pane (down).
    • Demographic Results within Status Panes:
    • Active Providers: 59.31%59.31\% Male, 40.69%40.69\% Female.
    • Inactive Providers: 72.29%72.29\% Male, 27.71%27.71\% Female.
    • Analytical Finding: Comparison indicates a narrowing gender gap among actively practicing medical providers relative to legacy/retired cohorts.

Exercise 2: Disease Profiling, Relational Joins, and Filtering

  • Relational Join Architecture in Tableau:

    • In a new workbook (diseases), establish connection to PostgreSQL schema homework4.
    • Physical vs. Logical Layers: Dragging tables directly into the canvas defaults to the logical relationship canvas (the noodle connection). To enforce a physical SQL join, double-click the root table (diseases) to open the physical canvas before adding disease_types.
    • Establishing a physical inner join produces a Venn diagram icon linking disease_id = disease_id.
    • Join Precedence and Field Naming: The root table dragged in first defines field naming precedence. Duplicate variable names across joined tables receive qualifying suffixes designating source tables (e.g., disease_id vs. disease_id (disease_types)).
  • Worksheet: Ranked ICD-9 Diagnosis Frequencies (icd9_code):

    • Place icd9_code (ICD-9-CM diagnostic identifier) on Rows and Count on Columns to construct a horizontal bar chart.
    • Hover over the column axis header and click the sort icon to order frequencies descending (largest to smallest).
    • Double Encoding (Color by Measure): Drag Count onto the Color shelf. While bar length and color gradient encode the same metric, the gradient provides pre-attentive visual emphasis that highlights high-density diagnostic categories.
    • Quantitative Filtering:
    • Drag Count to the Filters card and define a minimum numerical threshold of 7,5007{,}500.
    • Enable dynamic parameters by right-clicking the filter pill and selecting Show Filter.
    • The minimum count observed at this threshold cut-off is 7,5557{,}555.

Exercise 3: Clinical Encounters Profiling and Advanced Visualizations

  • Data Integration for Clinical Encounters:

    • In a new workbook (encounters), double-click table encounters in the physical canvas and join providers on provider_id = provider_id.
    • Automated Data Role Categorization:
    • Continuous clinical measurements (age, bp_diastolic, bp_systolic, height, pulse, temperature, weight) are categorized as aggregated measures.
    • Relational foreign keys and primary keys (department_id, encounter_id, patient_id, provider_id) are classified as discrete dimensions to prevent incorrect aggregations.
    • Temporal date fields automatically instantiate hierarchical roll-ups and drill-downs: Year →\rightarrow Quarter →\rightarrow Month →\rightarrow Day.
  • Provider Encounter Volume and Outlier Identification:

    • Worksheet providers_ln: Place provider last_name on Rows, Encounters (Count) on Columns, sort descending, color by gender, and filter by a minimum count of 5,0005{,}000.
    • Anomaly Detection: Provider last name "Fisk" accounts for 405,597405{,}597 total encounters, compressing all other provider bars across the visual axis.
    • Worksheet ln_fn: Add first_name directly after last_name on Rows to disambiguate shared surnames (e.g., isolating Donlin Ford from Steven Ford). Verification confirms there is only one provider with the surname Fisk (Zane Fisk).
    • Exclusion Filter Construction (ln_fn_no_fisk): Right-click the outlier data row in the visualization and select Exclude. Tableau generates an exclusion filter. Increase the encounter frequency filter threshold to 10,00010{,}000.
  • Blood Pressure Distribution and Confidence Limits (bp):

    • Plot bp_systolic on Columns and bp_diastolic on Rows.
    • Dimension Conversion: Tableau defaults continuous variables to sums (SUM(bp_systolic) and SUM(bp_diastolic)), creating values in the millions. Right-click both pills in the shelves and select Dimension to plot all individual coordinate pairs.
    • Encounter sample contains 1,718,3151{,}718{,}315 total records with 66 null entries. Blue marks represent female providers; orange marks represent male providers.
    • Analytical Reference Bands:
    • From the Analytics sidebar, drag Median with 95% Confidence Interval onto the canvas at the Table level.
    • Clinical Median Output: Systolic median equals 135 mmHg135\,\text{mmHg}; Diastolic median equals 74 mmHg74\,\text{mmHg}.
    • Confidence Interval Bounds: The confidence interval around the medians is narrow (±1.1 mmHg\pm 1.1\,\text{mmHg}) due to the sample size exceeding 1.71.7 million records.
  • Encounter Volume by Patient Age Distribution (age):

    • Place clinical measure age on Columns, convert to a Dimension, and plot Encounters (Count) on Rows.
    • Change the mark type from Automatic to Area via the Marks drop-down menu.
    • Advantages of Area Visualizations: Continuous shaded geometry provides interactive hit targets across the entire coordinate volume beneath the curve, enabling seamless tooltips compared to tracing single-pixel lines.
    • Clinical Age Patterns:
    • Minimal encounter activity between ages 00 and 11, followed by a sharp volumetric peak at age 22 driven by early childhood pediatric immunization schedules.
    • Gradual dip during mid-childhood due to specialized outpatient pediatric routing.
    • Re-elevation around ages 18–2118\text{--}21 as young adults transition out of pediatric practices into general adult medicine.
    • Gradual taper throughout older age brackets, reflecting regional catchment population volume and demographic attrition.
  • Anthropometric Modeling: Height vs. Weight Regression (height_weight):

    • Place height on Columns and weight on Rows, converting both measures to Dimensions.
    • Trend Line Mathematical Evaluation:
    • From the Analytics pane, test trend models across data distributions: Linear, Logarithmic, Power, Polynomial, and Exponential.
    • Polynomial models of degree 22 introduce false parabolic hooks at the distribution extremities.
    • Exponential regression provides the best mathematical fit.
    • Exponential Regression Formulation:
    • Weight=4.68631×e0.053965×Height\text{Weight} = 4.68631 \times e^{0.053965 \times \text{Height}}
    • Model Statistics:
    • Coefficient of Determination: R2=77.6%R^2 = 77.6\% (0.7760.776), indicating the proportion of variance explained by the exponential model.
    • pp-value is statistically significant (p<0.05p < 0.05) as a consequence of the large sample size.
    • Clinical Data Anomalies and Unit Discordance:
    • Extreme physiologically implausible outliers appear in the data:
      • Height 0.2 in0.2\,\text{in}, Weight 206 lbs206\,\text{lbs}
      • Height 2.5 in2.5\,\text{in}, Weight 540 lbs540\,\text{lbs}
      • Height 79.0 in79.0\,\text{in}, Weight 1.1 lbs1.1\,\text{lbs}
      • Height 46.0 in46.0\,\text{in}, Weight 423 lbs423\,\text{lbs}
    • These data points reflect unit mismatch errors originating from specific medical departments (e.g., neonatal or Labor & Delivery records logged in metric grams or ounces without standard conversion to imperial pounds, or metric centimeters recorded without conversion to inches).

Exercise 4: Patient Demographic Profiling

  • Patient Database Connection:

    • In a new workbook (patients), establish connection to PostgreSQL schema homework4 and load table patients.
  • Worksheet: Race by Religion Cross-Tabulation (race_by_religion):

    • Place demographic dimension religion on Columns (xx-axis categories).
    • Place demographic dimension race on Rows (yy-axis categories).
    • Drag system-generated measure Patients (Count) onto the central canvas text mark to generate frequency intersections across demographic groups.