Auto-Prep: Holistic Prediction of Data Preparation Steps for Self-Service Business Intelligence

TL;DR

This work targets the data-preparation pain point in self-service BI by proposing Auto-Prep, a holistic approach that predicts both data transformations and table joins across multi-table BI projects. It introduces a graph-based formulation MPBP-G that jointly optimizes transformation paths and joins, and solves it with a Steiner-tree–inspired algorithm that comes with provable guarantees. The offline component learns transformation and join predictors, enhanced by global signals across all tables, while the online phase builds a global search graph and derives high-quality predictions with an approximation factor of $2-\frac{2}{n}$. Empirical results on 1837 real BI projects show Auto-Prep achieves over $70\%$ coverage of data-prep steps, significantly outperforming prior transform-only, join-only, and GPT-based baselines, and it remains practical in terms of latency for typical multi-table BI workloads. This holistic approach has practical impact for improving user workflows in Power BI and Tableau, enabling more accurate and automatic data preparation in self-service BI tools.

Abstract

Business Intelligence (BI) plays a critical role in empowering modern enterprises to make informed data-driven decisions, and has grown into a billion-dollar business. Self-service BI tools like Power BI and Tableau have democratized the ``dashboarding'' phase of BI, by offering user-friendly, drag-and-drop interfaces that are tailored to non-technical enterprise users. However, despite these advances, we observe that the ``data preparation'' phase of BI continues to be a key pain point for BI users today. In this work, we systematically study around 2K real BI projects harvested from public sources, focusing on the data-preparation phase of the BI workflows. We observe that users often have to program both (1) data transformation steps and (2) table joins steps, before their raw data can be ready for dashboarding and analysis. A careful study of the BI workflows reveals that transformation and join steps are often intertwined in the same BI project, such that considering both holistically is crucial to accurately predict these steps. Leveraging this observation, we develop an Auto-Prep system to holistically predict transformations and joins, using a principled graph-based algorithm inspired by Steiner-tree, with provable quality guarantees. Extensive evaluations using real BI projects suggest that Auto-Prep can correctly predict over 70\% transformation and join steps, significantly more accurate than existing algorithms as well as language-models such as GPT-4.

Figures (12)

• Figure 1: Typical workflows of end-to-end BI. (S1) Raw data is first transformed; (S2) the transformed tables are then joined to create a BI model; (S3) finally, users use the BI model to create dashboards. We in this work focus on (S1) "transform" and (S2) "join", in the "data preparation" phase of BI.
• Figure 2: Architecture overview of Auto-Prep.
• Figure 3: Transformation tree $G(T_1)$ to represent all possible transformations on the Fertility table $T_1$ (Table \ref{['tab:example-fertility']}). Edge weights represent transformation probabilities (from $M_T$).
• Figure 4: Global search graph for Example \ref{['ex:search-graph']}. Each dashed-circle corresponds to a transformation tree $G(T_i)$, whose black edges represent possible transformations for table $T_i$. Join candidates are represented as green edges between transformation trees, where joinable tables are represented by solid edges (with $w(e) > 0.5$), and non-joinable "placeholder edges" are represented as dashed green edges (with $w(e) = 0.5$).
• Figure 5: An optimal solution for the search graph in Figure \ref{['fig:ex-graph']}, that corresponds to the solution $\mathcal{S}^*$ in Example \ref{['ex:def1']}.

Theorems & Definitions (22)

• Example 1
• Example 2
• Definition 1
• Example 3
• Example 4
• Definition 2
• Example 5
• Definition 3
• Example 6
• Definition 4