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?
jimbofish8Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
'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)
jimbofish8Author Commented:
Yes, doesn't exist...
jimbofish8Author Commented:
Is there another solution?
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

bandrieseCommented:
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.
Jeffrey CoachmanMIS LiasonCommented:
jimbofish8,

My company is in the process of evaluating OLAP cubes fro Our Great Pains Software.

From what little I know about it, it relies heavily on SQL server 2005 reporting services.
So if you don't have it, you may be out of luck.
SQL server is not cheap.  And the prices vary emormously depending on the version and the number of users. ($500USD to $25,000USD)
So you had better make sure it is worth the cost!

Again from what I know about them, they are high-end extensions of Pivot tables.

So my question is, ...are you sure a Pivot table can't do what you need?

See here:
http://technet.microsoft.com/en-us/library/aa964121.aspx

Here are a few more links from google we researched:
http://technet.microsoft.com/en-us/library/cc263194(TechNet.10).aspx#section3
http://blogs.msdn.com/excel/archive/2006/02/02/523815.aspx

JeffCoachman

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jimbofish8Author Commented:
thanks for the feedback. i need an ad-hoc solution, for which excel '07 pivots maybe the best thing...
Jeffrey CoachmanMIS LiasonCommented:
;-)
LBACISCommented:
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 CoachmanMIS LiasonCommented:
LBACIS ,

Thanks for the link
;-)

JeffCoachman
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.