May 11, 2022

Real World Data Doesn’t Fit in a 2D Relational Model

Real World Data Doesn’t Fit in a 2D Relational Model

David Zimpfer
May 11, 2022

We’ve all been users of Excel, after all, it’s been the dominate spreadsheet program for most of our careers. It’s deceptively simple to design and operate; it stores, manipulates, and calculates data in a simple two-dimensional relational format of rows and columns, and can be easily organized, distributed, and shared.

So why are so many data analysts anguishing over the amount of time they spend on spreadsheet management? There are many reasons, but one noticeable is the lack of scalability. As a business grows, so does the size and complexity of the supporting data; spreadsheets get larger, the data, format and formulae become more complex and critical, and the distribution for input grows. Excel might be a viable solution for small businesses but doesn’t scale with a growing enterprise. Imagine developing a model to forecast the performance of 20 products over 5 regions having 250 salespeople each, all providing 3 versions of financials (one each for the forecast, the actual and the variance), over 4 fiscal quarters. The business model quickly scales to 300,000 cells of data and multiple worksheets for an Excel application (20 products x 5 regions x 250 salespeople x 3 financial versions x 4 quarters).

In this example, the process of distributing and consolidating the spreadsheets becomes unwieldy for an analyst. Not only is this a time-consuming process, but once the model is built, any change in the calculating logic or format needs to be repeated throughout the multiple worksheets, hence the analyst’s angst. Furthermore, the process is prone to error; any of the formulae or formatting could be changed either intentional or unintentionally within the distributed network of users and go unnoticed. If this occurs, there is no debugging tool or test frame available for troubleshooting; this must be manually performed by an analyst. This problem could be mitigated if there was a record of a previous file, but in Excel there is no version control. In short, all these issues can consume hours of tending, versioning, and reconciling spreadsheets which vividly reveals Excel’s lack of scalability for large businesses.

Spreadsheets with multiple tabs are hard to scale with increasing customization and repeated cycles of updating.

Fortunately, multidimensional and OnLine Analytical Processing (OLAP) database platforms offer a viable alternative. These databases extend beyond the 2D relational model and into an infinite number of dimensions where the data is stored within a cube format and accessible by multiple indexes. A pivot function enables the analyst to rotate the cube to display new representations of the data, thus enabling dynamic multidimensional views of the data. This structure is optimized for OLAP and performs analysis at high speeds on large volumes of data from a data warehouse, data mart, or other unified, centralized data source. Multidimensional platforms and OLAP allow analysts to roll-up, drill-down, slice and dice data into a format that enables fast processing and insightful analysis.  

Finicast scales easily with customization and unlimited updating among 1000s of stakeholders.

Finicast offers just such a platform, but as a platform, it is fundamentally optimized because it is built for analysts by analysts. The result is a powerful, easy to use analytic platform. Model creation is done in a workspace that allows analysts to view multiple tables in one place. The tables’ configuration and manipulation are facilitated by ease-of-use pan, zoom, drag-and-drop, and resizing features. Formulas are created within the tables with familiar syntax, and when changes are made, they flow throughout the model simultaneously. Additionally, separate versioning models can be created and distributed companywide without affecting the original model, thanks to a unique and distinguishing version control feature. This enables a truly continuous planning process and companywide collaboration. Each user can make modifications and perform what-if scenarios while under version control. The versions of the main model can then be merged back through a controlled approval process with all changes documented, tracked and easily visible. All these unique features add up to a very powerful, feature rich, easy to use software platform that delivers a finely orchestrated process for business analytics.