Hitachi Consulting BI Blog

Spreadsheets and Databases

One of the first projects I did as a programmer at Hewlett-Packard back in the early 80s was to build a planning tool using this cool new “PC” (with a whopping 10 MB hard drive!). In selecting a development platform, I went through a bizarre oscillation between Lotus 1-2-3 and R:Base. I would work in the spreadsheet until I couldn't stand the limitations any more. Then I switched to the database until I couldn't stand it any more. And back, and forth. Finally, I ended up building a quirky hybrid solution, frustrated at how hard it was to exploit the benefits of the two architectures. I continued living in the twilight zone suspended between spreadsheets and databases--moving from 1-2-3 to Excel, and from R:Base to Access to SQL Server--and always trying to find better ways to integrate the calculations of a spreadsheet with the scalability of a database.

Enter OLAP. It's easy to think of Analysis Services as a database architecture, with pre-stored aggregated data. But it's at least as valid to think of a cube as a hyper-spreadsheet. When you write an MDX expression, you have direct addressable access to any cell defined in the logical space of the cube. This is exactly analogous to the way a spreadsheet formula gives you direct addressable access to any cell in the workbook, and it's conceptually backwards from the typical database concept of correlated subqueries.