Polaris A System for Query Analysis and Visualization

Polaris: A System for Query, Analysis and Visualization of Multi-dimensional Relational Databases Chris Stolte and Pat Hanrahan Computer Science Department Stanford University

Motivation l Large multi-dimensional databases have become very common – corporate data warehouses l Amazon, Walmart, … – scientific projects: l Human Genome Project l Sloan Digital Sky Survey l Need tools for exploration and analysis of these databases

The Pivot Table Interface – common interface to data warehouses – simple interface based on drag-and-drop – generate text tables from databases:

Polaris: Extending the Pivot Table Interface – generate rich table-based graphical displays rather than tables of text – single conceptual model for both graphs and tables – preserve ability to rapidly construct displays

Polaris Design Goals l Interactive analysis and exploration versus static visualization l Simple, consistent interface

Design Goal: Analysis & Exploration l Want to extract meaning from data l Process of hypothesis, experiment, and discovery l Path of exploration is unpredictable

Requirements on UI for Analysis and Exploration – Data dense displays: display both many tuples & many dimensions – Multiple display types: different displays suited to different tasks – Exploratory interfaces: rapidly change data transformations and views

Design Goal: Simple, Consistent Interface l Excel Pivot tables provide a simple interface for building text-based tables l Graphs require multiple steps: different interfaces and conceptual models l Want to unify tables, graphs, and database queries in one interface

Polaris Demo

Design Decision: Use a Formalism l Why a formalism? – unification: unify tables and graphs – expressiveness: build visualizations designers did not think of – interface simplicity: clearly defined semantics and operations – code simplicity: composable language versus monolithic objects

Polaris Formalism l Interface interpreted as visual specification in formal language that defines: – table configuration – type of graphic in each pane – encoding of data as visual properties of marks l Specification compiled into data & graphical transformations to generate display

Formalism Example: Specifying Table Configurations l Interface: define table configuration by dropping fields on shelves l Formalism: shelf content interpreted as expressions in table algebra l Can express extremely wide range of table configurations

Formalism Example: Specifying Table Configurations l Operands are the database fields – each operand interpreted as a set {…} – quantitative and ordinal fields interpreted differently l Three operators: – concatenation (+), cross (X), nest (/)

Table Algebra: Operands l Ordinal fields - interpret domain as a set that partitions table into rows and columns: QUARTER = {Quarter 1, Quarter 2, Quarter 3, Quarter 4} Quarter 1 Quarter 2 31, 400 35, 600 Quarter 3 Quarter 4 37, 120 30, 900 l Quantitative fields – treat domain as single element set and encode spatially as axes: PROFIT = {P[0 - 65, 000]} Profit (in thousands) 10 20 30 40 50 60

Table Algebra: Concatenation (+) Operator l Ordered union of set interpretations: QUARTER + PRODUCT_TYPE = {QTR 1, QTR 2, QTR 3, QTR 4} + {Coffee, Tea} = {QTR 1, QTR 2, QTR 3, QTR 4, Coffee, Tea} Quarter 1 Quarter 2 31, 400 35, 600 Quarter 3 Quarter 4 Coffee Tea 37, 120 30, 900 PROFIT + SALES = {P[0 -65, 000], S[0 -125, 000]} Profit (in thousands) 10 20 30 40 50 Sales (in thousands) 60 20 40 60 80 100 120

Table Algebra: Cross (X) Operator l Cross-product of set interpretations: QUARTER X PRODUCT_TYPE = {(Qtr 1, Coffee), (Qtr 1, Tea), (Qtr 2, Coffee), (Qtr 2, Tea), Quarter 1 Quarter 2 Quarter 3 Quarter 4 (Qtr 3, Coffee), (Qtr 3, Tea), Coffee (Qtr 4, Coffee), (Qtr 4, Tea)} Coffee Tea PRODUCT_TYPE X PROFIT = Coffee Tea Profit (in thousands) 10 20 30 40 50 Profit (in thousands) 60 10 20 30 40 50 60

Table Algebra: Nest (/) Operator l QUARTER l would X MONTH create entry twelve entries for each quarter i. e. (Qtr 1, December) l QUARTER / MONTH l would only create three entries per quarter

Formalism l Remainder of formalism defined in paper: – specification of different graph types – encoding of data as retinal properties of marks in graphs – translation of visual specification into SQL queries

Related Work l Formalisms for Graphics – Wilkinson’s Grammar of Graphics – Bertin’s Semiology of Graphics – Mackinlay’s APT l Visual Queries – Trellis display, De. Vise, Visage l Table-based Visualizations – Table lens, Spreadsheet for Visualization

Wilkinson’s Grammar of Graphics l Describes formalism for statistical graphics l Different choices in the design of formalism: – non-relational data model – different operators in table algebra l Further experience necessary to fairly evaluate differences between our formalisms

Conclusions l Novel interface for rapidly constructing table -based graphical displays from multidimensional relational databases l A formalism for specifying complex graphics and tables l Interpretation of visual specifications as relational (SQL) queries and drawing operations.