After we have finished all the preparations for our Dashboard in the previous Blog post of this Dashboards’ Guide series, we’re now ready to start implementing our Dashboard in Excel.
A good data staging serving our Dashboards must be very efficient, to quickly refresh the Dashboards. This implies that we need to carefully strike a balance across all resources required to calculate the data.
Multiple options and techniques are at our disposal: are we writing complex Worksheet formulas? are we splitting the calculation across multiple intermediary tables? should we add a helper column(s) to a data table to replace costly functions with faster ones? should we use a Pivot Table for staging? are we writing SQL queries over our Worksheet data tables for high performance? are we staging the Dashboards’ data feed in a helper (hidden) Worksheet, or are we populating the dashboards’ elements directly with the calculated data? can a single staging table serve more than one Dashboard element (by adding more columns to it)?
These are some of the considerations and trade-offs we need to account for. They are affected by several factors, including:
Consider the following situations:
First, you want to make sure your architecture and overall solution design are the best possible to serve the requirements. Be sure to remain with only delays that are beyond your control.
In such cases, we should consider separating the data sourcing (into “cold” staging tables) from the visual painting of the Dashboards’ elements. The “cold” staging tables are refreshed from the source in the appropriate frequency, either by a timer cue or by another trigger identified by the application. Since these tables are “cold” (Not attached to the visual Dashboards elements), their refresh has no further impact on any other element in the Workbook.
When the Dashboards are called for re-painting, we quickly copy the data from the “cold” staging tables into the “plugged” staging tables – for Dashboards calculation only.
The first part, refreshing the “cold” staging tables, can even be performed outside of the Dashboards’ Workbook. Another program can be tasked with refreshing the data from the source every so and so hours, storing the data in a text (csv) file that is very quickly read into the Dashboards’ Workbook when needed.
While considering all of the specific circumstances you are facing, I offer the following guidelines:
In closing this post, keep in mind that the main goal of the data staging phase is performance. The whole design, architecture and procedures need to be geared towards servicing the Dashboards view fast!
As hidden from the eye as data staging is, the visual effect of Dashboards will be the focus of our next (and last) post on Dashboard – next week.
The in-depth course, Beyond Excel Boundaries with Databases & SQL, is now open.
Its pre-launch price of $97 is still valid for few more days only, until the last 5 missing sessions are added and the full course is published.
This price will double in a few days, so if you are serious about your career, or about understanding Databases, SQL and how to develop Excel VBA business-grade programs with Databases - take advantage of this opportunity today