Core Architectural Overview
Optimizing data structures early in the development lifecycle is the single most critical factor in dashboard performance. Tableau Prep is the university’s approved enterprise tool for heavy data cleaning, structural transformations, and ETL (Extract, Transform, Load) processes. A Tableau Prep license is included in the purchase of a Tableau Desktop license.
The university supports two integrated Prep products:
- Tableau Prep Builder: a desktop application downloaded to the user’s computer that allows Tableau Creators to clean, reshape, pivot, and merge disparate datasets, outputting optimized data sources (.hyper or .csv) for downstream visual modeling.
- Tableau Prep Conductor: a feature of the university’s Tableau Server environment that allows Project Leaders and Creators to schedule, automate, and monitor the refresh of Prep flows.
The primary goal of Tableau Prep is to structure, clean, and secure records, transforming them into tidy analytical data sources. Tableau Desktop is reserved for data modeling, visual exploration, and dashboard development. Forcing Tableau Desktop to perform complex data preparation degrades performance and increases technical debt.
Tool Selection: Prep vs. Desktop
To ensure technical efficiency, developers must choose between Tableau Prep and Desktop based on the specific data modeling tasks.
Task/Capability | Tableau Prep (ETL and Clean) | Tableau Desktop (Visual Modeling) |
|---|---|---|
| Data Pivoting | Preferred. Pivots rows-to-columns or columns-to-rows. Far faster and more scalable. | Supported but slow. Best reserved for minor adjustments on small, highly structured datasets. |
| Date Scaffolding | Highly recommended. Fills timeline gaps, missing historical records, or expands multi-date ranges. | Extremely complex. Often requires writing highly convoluted, slow visual table calculations. |
| Heavy Joins and Unions | Preferred. Performs physical multi-table joins, structural database-level unions, and early data blending. | Supported |
| Advanced Cleaning | Preferred. Built for regex parsing, string splitting, null masking, and complex conversions. | Limited. Best for minor visual aliases, grouping values, and creating categories. |
| LOD Expressions | Best for static row-level logic, especially if the final dashboard requires 10+ complex LOD calculations. | Best for visual dependent aggregations, table calculations, and dynamic user-interactive filters. |
| Live Database Connections | Not supported. | Preferred |
Technical Note on Sampling: to ensure high design speed, Tableau Prep samples data by default during the interactive flow editing process, meaning it does not load the entire dataset. However, when the flow is executed (run), Prep Conductor processes the entire dataset, ensuring that all operations are fully applied to the final product.
Developer Flow Optimization: Do’s and Don’ts
To prevent server outages, flow refresh failures, and redundant extracts, developers must adhere to the following optimization standards:
Tableau Prep Do’s
- Filter out irrelevant data early: to avoid unnecessary CPU processing, filter out irrelevant rows and exclude columns at the very beginning of your flow. This can be accomplished directly in the initial SQL WHERE clause or immediately following the connection node.
- Understand SQL and join structures: having a solid foundation in SQL, primary keys, and join/union behaviors is essential to streamline data transformations, prevent record duplication and minimize structural flow errors.
- Document flows with comments: to ensure long-term maintainability, add comments to describe the logic of key steps. Comments help inheriting developers troubleshoot flow breaks quickly, though they are strictly limited to 200 characters per step.
- Spot-check: utilize Prep’s built-in visual feedback panes to spot-check transformations, aggregate counts, and null distributions after every join or split step. Do not wait until the final output to review data integrity.
Tableau Prep Don’ts
- Don’t overload datasets redundantly: avoid importing the same massive dataset (e.g., millions of journal lines) multiple times within separate branches of a single flow. This leads to severe bottlenecks and unnecessary server storage inflation.
- Don’t expect real-time visual querying: Tableau Prep is designed primarily for data preparation and cleaning, not for real-time visual exploration or fast live-data feeds.
- Don’t assume direct desktop relationship compatibility: Prep does not natively support Tableau Desktop’s relationship model. For complex data models, developers must establish physical connections or query directly from the RAE (Reporting and Analytics Environment).
- Avoid multi-brand complex flows: Prep can struggle to compile and execute convoluted flows. Flows with more than a dozen (12+ steps) frequently break, experience scheduling delays, or fail on the server. Developers should simplify flows – splitting heavy pipelines into smaller, modular flows.
- Don’t over-rely on Prep for uninterrupted performance: Prep is a highly robust application, but its server-side integration is subject to schedule queues and patch updates. Developers must build flows with error-tolerant steps and expect periodic maintenance.
University Infrastructure & Support FAQs
Q: What is the correct network path to connect Tableau Prep to the K Drive?
A: To connect securely, the flow’s file input must reference the absolute UNC network path. It must always begin with the \\bcd.it,osu.edu\. For example, a standard production path should be formatted as: \\bcd.it.osu.edu\files\WORKDAY\BAR\prd.
Note: Network file access is restricted. This is only available to MITS customers.
Q: What are the approved output destinations for university-published flows?
A: Tableau Prep allows developers to choose several secure destinations depending on their audience: 1) Standalone, governed published data sources on Tableau Server; 2) relational database tables (via write-back connections); or 3) tidy flat csv/hyper files saved to secure network drives.
Q: How are Prep flows managed when a developer leaves the university?
A: Personal accounts are deactivated upon departure, which automatically deletes all embedded credentials and breaks scheduled refreshes. To prevent disruption, departing Creators must formally transfer ownership of all Prep flows to an active Creator, and edit the connection details. It is recommended to use a departmental service account instead of personal credentials.