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 .
- 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 , 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 utilizes single -hour-and--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
.jarfiles) 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_6430orARMC). - 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.
- User login roles are established at the PostgreSQL server cluster level, making them globally accessible across individual databases (e.g.,
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_externalwith password authentication. - Execute explicit
GRANTstatements to provision strictly read-only access to the target reporting schemahomework4.
Database Connection Parameters:
- Server / Hostname:
localhost(resolves to local instance). - Port: Default PostgreSQL port is
. Alternative local instances may bind to portsor(verifiable via PostgreSQL server properties). - Database Name:
COE6_6430(case-sensitive identifier). - Username:
coe_external - Password:
coe_external_password
- Server / Hostname:
Data Schema Characteristics (
homework4):- Entity Tables:
departments,disease_types,diseases,encounters,medication,medication_types,patients,providers. - Patient Volume: Exactly
patient records. - Encounter Volume: Exactly
encounter records.
- Entity Tables:
Exercise 1: Provider Profiling and Basic Visualizations
Tableau PostgreSQL Connection Protocol:
- Connect via the PostgreSQL server connector using server
localhost, port, databaseCOE6_6430, usernamecoe_external, and passwordcoe_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 schemahomework4. - 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
.hyperfile, enabling offline data portability.
- Connect via the PostgreSQL server connector using server
Variable Types and Metadata Display:
- Data types are marked by icons:
#indicates numerical data;Abcindicates 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 SQLCOUNT(*)).
- Data types are marked by icons:
Worksheet 1: Discrete Status Counts (
status):- Place categorical variable
statuson Rows and system-generated measureProviders (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
; inactive providers count is(total:). - Sheet renaming: Double-clicking the worksheet tab updates both the tab name and the canvas sheet title.
- Place categorical variable
Worksheet 2: Highlight Table (
status_ht):- Duplicate worksheet
status(avoiding Duplicate as Crosstab). - Open the Show Me palette and select Highlight Table (requires exactly dimension and 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.
- Duplicate worksheet
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
genderon Rows andProviders (Count)on Columns; select horizontal bar chart. - Drag
Providers (Count)to the Label shelf (Ticon) 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.
- Place
Worksheet 5: Stratified Demographics via Side-by-Side Bar Chart (
gender_sbs):- Place
statusfollowed bygenderon Rows, withProviders (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 bars to
). - 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:
Male,Female. - Inactive Providers:
Male,Female. - Analytical Finding: Comparison indicates a narrowing gender gap among actively practicing medical providers relative to legacy/retired cohorts.
- Place
Exercise 2: Disease Profiling, Relational Joins, and Filtering
Relational Join Architecture in Tableau:
- In a new workbook (
diseases), establish connection to PostgreSQL schemahomework4. - 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 addingdisease_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_idvs.disease_id (disease_types)).
- In a new workbook (
Worksheet: Ranked ICD-9 Diagnosis Frequencies (
icd9_code):- Place
icd9_code(ICD-9-CM diagnostic identifier) on Rows andCounton 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
Countonto 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
Countto the Filters card and define a minimum numerical threshold of. - Enable dynamic parameters by right-clicking the filter pill and selecting Show Filter.
- The minimum count observed at this threshold cut-off is
.
- Place
Exercise 3: Clinical Encounters Profiling and Advanced Visualizations
Data Integration for Clinical Encounters:
- In a new workbook (
encounters), double-click tableencountersin the physical canvas and joinprovidersonprovider_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
QuarterMonthDay.
- In a new workbook (
Provider Encounter Volume and Outlier Identification:
- Worksheet
providers_ln: Place providerlast_nameon Rows,Encounters (Count)on Columns, sort descending, color bygender, and filter by a minimum count of. - Anomaly Detection: Provider last name "Fisk" accounts for
total encounters, compressing all other provider bars across the visual axis. - Worksheet
ln_fn: Addfirst_namedirectly afterlast_nameon 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.
- Worksheet
Blood Pressure Distribution and Confidence Limits (
bp):- Plot
bp_systolicon Columns andbp_diastolicon Rows. - Dimension Conversion: Tableau defaults continuous variables to sums (
SUM(bp_systolic)andSUM(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
total records with 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
; Diastolic median equals. - Confidence Interval Bounds: The confidence interval around the medians is narrow (
) due to the sample size exceeding million records.
- Plot
Encounter Volume by Patient Age Distribution (
age):- Place clinical measure
ageon Columns, convert to a Dimension, and plotEncounters (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 and , followed by a sharp volumetric peak at age driven by early childhood pediatric immunization schedules.
- Gradual dip during mid-childhood due to specialized outpatient pediatric routing.
- Re-elevation around ages 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.
- Place clinical measure
Anthropometric Modeling: Height vs. Weight Regression (
height_weight):- Place
heighton Columns andweighton 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 introduce false parabolic hooks at the distribution extremities.
- Exponential regression provides the best mathematical fit.
- Exponential Regression Formulation:
- Model Statistics:
- Coefficient of Determination: (), indicating the proportion of variance explained by the exponential model.
- -value is statistically significant () as a consequence of the large sample size.
- Clinical Data Anomalies and Unit Discordance:
- Extreme physiologically implausible outliers appear in the data:
- Height , Weight
- Height , Weight
- Height , Weight
- Height , Weight
- 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).
- Place
Exercise 4: Patient Demographic Profiling
Patient Database Connection:
- In a new workbook (
patients), establish connection to PostgreSQL schemahomework4and load tablepatients.
- In a new workbook (
Worksheet: Race by Religion Cross-Tabulation (
race_by_religion):- Place demographic dimension
religionon Columns (-axis categories). - Place demographic dimension
raceon Rows (-axis categories). - Drag system-generated measure
Patients (Count)onto the central canvas text mark to generate frequency intersections across demographic groups.
- Place demographic dimension