I was wondering if Charlie Kyd had fallen off my radar somehow, but just received his latest Excel for Business Newsletter full of ideas for using Excel as a financial mangement tool. Of interest this time is a link to another article he wrote in BPM Magazine, about using Excel to generate reports and dashboards from OLAP cubes. What’s an OLAP cube?
OLAP is the name for a type of database technology that stores information in cubes, rather than in lists. A company might keep its general ledger accounting data in a simple OLAP cube that includes three dimensions: account, division, and month. At the intersection of any particular account, division, and month you would find one number. Most cubes have more than three dimensions, and they typically contain a wide variety of business data, not merely G/L data. Users with access to an OLAP cube of corporate information can define any consolidation hierarchy for the cube’s dimensions. For example, in the “month” dimension, every month could roll up into quarters, which could roll up into years. Or months could roll up into year-to-date numbers. The individual data points (the cube’s “leaf members”) and the consolidated numbers would be equivalent sources of data. So users generating a report could choose data from a leaf member like Mar-2007 just as easily as they could choose from a consolidated member like Mar-2007-YTD.
The newsletter is great stuff, not least because anyone who has Microsoft Office has Excel.