Refresh one OLAP Cube toupdate multiple spreadsheets / pivottables

[icowden]

Hi all,

Tried asking this in a few excel forums but got no joy so I'm assuming this is more of an OLAP question.

I am trying to get my head around 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.

I know 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 their data without each one having to refresh the OLAP cube (which is what is happening at the moment and which takes up to 2 minutes per worksheet).

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?

Cheers

Iain

 

[MDXer]

Why do it in Access? If you want to use SSAS you need to buy SQL Server anyways. 110MB data for a cube is pretty light so yuo should have no problems with a well built cube.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[icowden]

Access is the only software available to me at present within our organisation.

I was more interested in the principle rather than the application. We have a dedicated Information Team, but they seem to think it's a good idea to put all the data in 100mb spreadsheets which my PC can barely open.

I am developing a proposition for the Information Team, but want to be sure of my facts and the underlying principles before presenting this to the associate directors.

The OLAP principle seems to work well and (since working on this idea further) I am now not even sure if multiple workssheets are needed as essentially all service data could be gathered via a single pivot table with some background programming to make it user friendly.

The main problem is that analysing 120,000 lines of excel cells takes considerably longer, and a great deal more processing power than interrogating a database. And interrogating a specifically configured OLAP cube takes even less time.

I just want to be sure that If the powers that be feel it would be better for each service to have their own workbook, pre-configured if you like, that all workbooks could essentially link to a single OLAP cube which could be refreshed when the main database is updated, thus ensuring data integrity in the main database but minimising opening / updating time for the heads of service.

So... the question still stands... and alas still remains unanswered.

Iain

 

[MDXer]

Remember OLAP is the methodology and not the technology. You can build an OLAP database in Access if that is all you have and the data volume is pretty light, your main point would be the ETL. When you talk about cubes you are generally talking about MOLAP storage. MOLAP storage is where the data is pre-aggregated and stored separately from your relational database. You can base reporting on a ROLAP structure which is where all your fact data dimensional data lives only in a relational table. The main difference is speed, a MOLAP storage is going to typically give you second to subsecond response depending on the design and complexity of the cube. A small volume of data could possibly give decent query performance.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[icowden]

whether it is the methodology or the technology, it still doesn't answer the question. Not sure what an ETL is either.

I thought the question was fairly simple...

If I have a database in Access I can query it directly from Excel. I also have the option to build my query into an OLAP cube.

If I have a single OLAP cube query, I can use that to build several workbooks.

The question is, can I refresh the OLAP cube by itself so that the workbooks linked to it all have their data automatically refreshed, rather than each workbook starting a cube data refresh for every instance of every pivot table?

I was under the impression that the cube was a highly optimised, compact database, and that once refreshed, any queries would automatically reflect the new content of the cube.

I have yet to find a way of doing this and was hoping that someone could tell me whether I am barking up the wrong tree!!

Iain

 

[MDXer]

Not to be rude be yes giving the current tools you have I believe you are barking up the wrong tree. As a Business Intelligence Profesional I would even say your company trying to do BI in the current system described is barking up the wrong tree. BI need not be a costly venture but it needs to be done in a cost effective and easily maintained manner. A well built BI system is easily scalable and expandable, which giving what you have posted your companies is not.

Your best bet is to learn what comprises a good BI system and what doesn't. Compare this with your companies current system and make the case for fixing what isn't broke, YET. It also sounds to me that an individual or group of individuals have built a system to ensure their employment.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck