Data Preparation for Analysis: An AI Expert‘s Guide to Tableau Dashboards
As artificial intelligence and machine learning revolutionize analytics, the importance of proper data preparation has never been greater. ML models and AI-powered visualizations are extremely sensitive to the quality and structure of input data. Even the most sophisticated algorithms will falter if fed with incomplete, inconsistent, or poorly formatted information.
In this in-depth guide, we‘ll explore data preparation best practices through the lens of an AI and ML expert, with a specific focus on readying data for analysis and visualization in Tableau. Whether you‘re wrangling a simple spreadsheet or a massive stream of unstructured data, these principles will help ensure your Tableau dashboards are accurate, insightful and actionable.
The State of Data (and Data Prep)
Poor data quality remains a pervasive challenge for organizations looking to become data-driven. According to a survey by Harvard Business Review[^1]:
- 47% say data quality and consistency issues are a significant barrier to analytic adoption
- 51% say they don‘t have the right data to answer business questions
- 54% lack trust in the accuracy of their data and analytics
These issues often stem from inadequate preparation of raw data prior to analysis. Data scientists notoriously spend up to 80% of their time on data cleaning and wrangling tasks[^2]. The figure below shows a breakdown of common data prep activities:
Source: CrowdFlower 2016 Data Science Report[^3]
However, this upfront effort is critical to the success of any AI/ML initiative. A survey of data scientists by Figure Eight found that those who spent more time on data prep had ML models that performed 10-15% better on average[^4]. Clearly, robust data preparation translates into analytic advantage.
Assessing Data Quality for Machine Learning
Machine learning models learn exclusively from the data they are trained on. Any defects or gaps in that training data will be reflected in the model‘s real-world performance. Therefore, it‘s essential to profile your data upfront to understand its overall quality and suitability for ML.
Key data quality dimensions to evaluate include:
Completeness: Are any critical fields missing values? Could those gaps skew the results?
- ML impact: Models may produce biased predictions if trained on records missing key features or outcomes.
Consistency: Do fields contain mixed data types, units or formats? Are there conflicting duplicate records?
- ML impact: Inconsistencies can confuse models and lead to spurious feature importance.
Accuracy: How well does the data reflect ground truth? Are there any outliers that don‘t make sense?
- ML impact: Models trained on inaccurate data will make inaccurate predictions. Outliers can significantly degrade performance.
Uniqueness: Are there any inadvertent duplicate entries? Do columns contain unique keys?
- ML impact: Duplicates can cause models to overweight certain patterns. Unique keys are needed to join tables.
Timeliness: Is the data sufficiently up-to-date for its intended use case? Is it refreshed at the necessary frequency?
- ML impact: Models can become stale if not retrained on fresh data. Old data may not reflect current realities.
Various open source and commercial data profiling tools can help assess quality across these dimensions, such as:
The results of this profiling exercise will inform what specific data preparation steps are needed before feeding data into machine learning models or Tableau visualizations.
Data Prep Techniques for Better ML & Visualizations
With a clear picture of your data quality, you can take targeted steps to clean, normalize and transform the data into optimal shape for analysis. Common data wrangling and feature engineering techniques include:
Missing Value Imputation: Filling in null values through statistical techniques like mean/median/mode imputation, regression, or k-nearest neighbors. Choosing the right fill method requires careful consideration of the mechanisms behind the missing data[^5].
Outlier Detection: Identifying and excluding anomalous values that fall outside of expected statistical distributions. Isolation forests, DBScan clustering and Median Absolute Deviation are popular unsupervised methods to flag outliers[^6].
Data Deduplication: Systematically identifying and merging records that refer to the same entity based on defined business rules. Fuzzy string matching and n-gram fingerprinting can help match records with subtle differences[^7].
Categorical Encoding: Converting text values to numeric codes for ingestion by ML models, through methods like one-hot encoding, label encoding, frequency encoding or target-based encoding[^8].
Feature Scaling: Normalizing numeric features to similar ranges (e.g. between 0-1) to avoid features on larger scales dominating model training. Standardization and min-max scaling are two common normalization techniques[^9].
Discretization: Binning continuous features into discrete buckets, either by equal-width or equal-frequency partitioning. This can boost signal for certain ML algorithms and make data more visually digestible[^10].
Derived Features: Computing new higher-level features as a function of raw input fields. Derived features capture richer interactions that may be more predictive, such as ratios, moving averages or trend flags.
These are just a sampling of the myriad data prep techniques available. The choice of methods will depend on the unique characteristics of your data and the requirements of downstream machine learning and visualization use cases. Automated feature engineering platforms like DataRobot and tpot can help identify optimal techniques for a given dataset.
Structuring Data for Tableau AI/ML
When preparing data that will feed both machine learning models and Tableau visualizations, it‘s important to structure the data in a way that will meet the needs of both destinations. A few key considerations:
Store Training Data in Tables: Machine learning typically works best with data structured as flat tables, where rows represent observations and columns are features. Avoid nested hierarchies or BLOB fields.
Keep Feature Sets Consistent: The variables used to train a model should match those used for inference. Plan ahead in Tableau for displaying ML results alongside originating features.
Aggregate Appropriately: Think critically about the level of aggregation used for ML features vs Tableau visualizations. For example, a demand forecasting model may need daily sales, but an executive dashboard may only show monthly totals. Pre-aggregate at multiple levels as needed.
Build Derivations in Tableau Prep: Calculated fields and joined tables created in Tableau Prep are easily shared across Tableau workbooks. Centralizing feature engineering here will ensure consistency between your AI/ML input data and visualization data.
Balance Cardinality: ML models and Tableau visualizations can both struggle with ultra-high cardinality fields containing millions of unique values. Aim for a sweet spot by rolling up or combining low-frequency categories where it makes sense.
By structuring your core data assets to serve the needs of both machine learning and visual analytics, you‘ll spend less time moving and reshaping data and more time gleaning insights.
Automating Data Prep at Scale
The other key to efficient data preparation is leveraging tools and platforms to automate the process at scale. This is especially critical when dealing with big data that would be impractical to wrangle manually.
For Tableau deployments, Tableau Prep is the premier tool to build repeatable data prep flows and share them across the organization. Prep‘s drag-and-drop interface lets any user visually combine, shape and clean data without needing to write code. Under the hood, Prep generates optimized Apache Spark jobs to crunch through large datasets.
Sample Tableau Prep flow for an HR analytics use case
Tableau Prep integrates seamlessly with other parts of the Tableau platform, including:
- Tableau Desktop: Publish cleaned Tableau Prep output as a data source for live analysis
- Tableau Server/Online: Schedule Tableau Prep flows to automatically refresh data
- Tableau Catalog: See data freshness, popularity and lineage behind prepped data
For data science and machine learning workflows, data prep can be accelerated through self-service tools like Trifacta and Paxata or automated feature engineering libraries like Featuretools[^11]. These technologies leverage intelligent algorithms to infer data types, suggest relevant transformations, and surface potential quality issues for remediation.
Trifacta leverages ML to suggest data transformations
Additionally, Apache Spark and cloud platforms like Databricks can greatly speed up data preparation at petabyte scale through distributed in-memory processing[^12]. By bringing compute to the data and parallelizing workloads, these solutions enable interactive data wrangling on massive datasets.
Conclusion
We‘ve seen why data preparation is so critical to the success of AI, machine learning and visual analytics initiatives, and how to approach data prep through the lens of these disciplines. By understanding the key principles of data quality, feature engineering, dataset structure and process automation, you‘ll be well-equipped to mold your raw data assets into valuable insights.
Ultimately, thoughtful data preparation enables organizations to maximize the value of their data. Accurate, well-structured data leads to more performant ML models and more truthful Tableau visualizations. It builds trust from business users and enhances data-driven decision-making.
While often tedious, data preparation doesn‘t have to be a bottleneck. With the right tools, techniques and best practices, you can streamline data prep pipelines and quickly start extracting insights. So take the time at the outset of your next Tableau or data science project to properly plan and pressure-test your data. Your future self (and your end users) will thank you!
[^1]: Harvard Business Review Pulse Survey, "Scaling the Power of Analytics Across the Organization", 2019 [^2]: Cleaning Big Data: Most Time-Consuming, Least Enjoyable Data Science Task, Survey Says [^3]: CrowdFlower 2016 Data Science Report [^4]: Data preparation for non-data scientists [^5]: Uwe Matzat. "Imputation procedures to handle missing values." Data Analysis and Graphs Using R (2014): 381-422. [^6]: Outlier detection techniques in machine learning [^7]: Peter Christen. "A comparison of personal name matching: Techniques and practical issues." Sixth IEEE International Conference on Data Mining-Workshops (ICDMW‘06). IEEE, 2006. [^8]: Smarter Ways to Encode Categorical Data for Machine Learning [^9]: About Feature Scaling and Normalization [^10]: Data Discretization: How to Convert Continuous Features to Discrete [^11]: Automated Feature Engineering for Predictive Modeling [^12]: Data Wrangling with Spark for Data Scientists Who Know Pandas
