Hi all,

I am trying to get my head around the benefits of OLAP! At present for our metrics analysis, our Information team populate huge unwieldy excel spreadsheets. The overall spreadsheet for the year to date (5 months worth of data) is already 110mb in size (due to the several pivottables and their associated caches) and hugely difficult to do anything with without excel dying from lack of memory

It appears to me that it would be better to put the data in an Access DB and then to create pivottables from an OLAP cube.

It appears to me that I can create a single pivottable in a workbook and using MSQuery make an OLAP cube. I can then make more pivottables as needed based on that OLAP cube.

The big question I have is whether I can update that cube when data is added to the access database and have the worksheets refresh the data without each one having to refresh the OLAP cube (which is what is happening at the moment). Presumably once the cube is refreshed, the other workbooks should be able to look at it and show the up to date data.

Or have I just got the wrong end of the stick?