Pivot tables and Sparkline charts have something in common. Both can show a column transformed view of data. Sparklines have the additional advantage of using less space. However, they also have the disadvantage that they only show the outline of the data due to smaller space. When done incorrectly, sparklines lead to incorrect decision making and bad user experience. This article provides the details of how SQL Frames implemented Sparkline charts to take care of both correctness and performance.
Sparklines are a great way to show the trend outline of a metric over time in a compact format along with rest of the text, typically within a data table. Most charting engines and online examples consider an array of numbers that are rendered as a line chart or bar chart. If the requirement is to view the sparkline of some dimension such as a stock ticker in isolation or a series of metrics in a scorecard, then this may be OK. However, often users are presented with sparkline in the context of a tabular data that shows, for example, several stock tickers. In such cases, it is important to make sure that the data is plotted with proper scaling and alignment. For example, if a company went through an IPO only a few days back, the sparkline of that should start somewhere towards the end of the chart instead of at the beginning.
Aligning on x-axis
Different rows in the table can have different start and end dates. Even if they have same start and end dates, the data may be sparse. In such cases, it is important to plot the data with the correct alignment and the same x-axis min and max range of the time period.
Scaling on y-axis
Most charts are rendered by scaling the data given to them. When comparing data across multiple rows, each row can have trend data that has a different scale. For example, established territories may have more sales than newly entered territories. In such cases, scaling the data within the chart as opposed to across the entire table gives incorrect trend outline making it impossible to compare across the rows.
SQL Frames is designed to take care of both the x-axis alignment and y-axis scaling issues making it easy to compare the data at a glance.
Charts are relatively expensive to render. Even rendering a micro chart like the sparkline of the trend data is relatively expensive compared to just showing data as text. SQL Frames has a powerful UI layer that supports infinite scrolling by using viewport virtualization technology. Hence, it easily supports rendering data tables that have over a million rows. At any given time only the rows within the viewport (typically fom 5 to 500) are rendered. Hence, when rendering sparklines it is important to have fewer rows in the viewport.
Even with fewer rows in the viewport, there are some challenges to provide smooth scrolling experience when rendering sparklines. Especially when sparklines were rendered in the pivot table, the scrolling experience was horrible.
Careful examination indicated a lot of mounting and unmounting of the sparkline component. Initially it was assumed to be incorrect implementation of the component leading to the inability of the VDOM diffing logic to recognize the unchanged components. However, it turned out, scrolling results in several intermediate rendering (which actually provides smooth scrolling experience) and each of these result in newly mounted components and similarly rows that went out of the viewport result in unmounting the components.
To address the above problem, the strategy is changed to track scrolling and only at the end of the scrolling the sparklines are rendered. During the scrolling, the sparkline cells show blank and hence not consuming any resources. This brought back the smooth scrolling and I don't think anyone cares to have the sparklines render while scrolling is happening. However, there is still a problem with sparklines within wider pivot tables as SQL Frames currently only has support for viewport row virtualization and not column virtualization.
Below is a simple example of a Sparkline. More can be found in the sparkline documentation.
Sparklines are a great visualization technique when implemented correctly. Make sure you are using the right tools and/or right settings in the visualization options to ensure your visualizations are accurate and performant.