Improve company productivity with a Business Account.Sign Up

x
?
Solved

How to build an OLAP Cube from Access 2007?

Posted on 2008-06-10
9
Medium Priority
?
7,860 Views
Last Modified: 2016-02-10
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?
0
Comment
Question by:jimbofish8
9 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 21752329
'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)
0
 

Author Comment

by:jimbofish8
ID: 21752359
Yes, doesn't exist...
0
 

Author Comment

by:jimbofish8
ID: 21753321
Is there another solution?
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
LVL 3

Expert Comment

by:bandriese
ID: 21756065
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.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 21757331
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
0
 

Author Closing Comment

by:jimbofish8
ID: 31465775
thanks for the feedback. i need an ad-hoc solution, for which excel '07 pivots maybe the best thing...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21817633
;-)
0
 
LVL 4

Expert Comment

by:LBACIS
ID: 21960096
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

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21961006
LBACIS ,

Thanks for the link
;-)

JeffCoachman
0

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
This following write-up describes a different way to copy Lotus Notes Calendar to Outlook. Along with this, we will also learn the reason behind this NSF to PST migration. Users can prefer different procedures as per their convenience.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

585 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question