asked on

How to build an OLAP Cube from Access 2007?

I have a database on my local computer that I'm running. One of the main outputs is a consolidated table which joins 10+ tables together to form one massive table with 1M lines. I have been querying the MDB using statistical software, JMP. However, the performance of the query is starting to suffer.

This master query is the one that will primarily be searched. I've read online that creating and distributing an OLAP cube from this data could speed up querying much faster. This feature seems to be disabled within Access 2007. How do I go about doing so? Or, is there a better solution?

peter57r

'This feature seems to be disabled within Access 2007'

Disabled?
Doesn't exist! There's no OLAP in Access (and now no OLAP in Excel either)

jimbofish8

ASKER

Yes, doesn't exist...

jimbofish8

ASKER

Is there another solution?

bandriese

I'm heading in this direction but have not arrived there myself yet. Even if you could, you wouldn't want to create cubes in Access. Cubes are the sort of thing that are thought about, planned, then created for everyone to use in mass. Consider it part of the database structure. Again, I haven't tried myself, but I think if you create your analysis database in SQL then you should be able to query it through ODBC (Access, JMP, Crystal, Reporting Services, etc.) and yes, you'd see a performance gain. SQL 2008 makes analysis services a little easier for the first timer. I beleive a Development version cost around $30.

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

jimbofish8

ASKER

thanks for the feedback. i need an ad-hoc solution, for which excel '07 pivots maybe the best thing...

Jeffrey Coachman

;-)

LBACIS

There is an open source solution all you need to do is attach to the access database as a source, you can then use excel as the reporting presentation layer.

http://www.jedox.com/en/enterprise-spreadsheet-server/excel-olap-server/palo-server_download.html

Jeffrey Coachman

LBACIS ,

Thanks for the link
;-)

JeffCoachman