Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to build an OLAP Cube from Access 2007?

Posted on 2008-06-10
9
Medium Priority
?
7,486 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

916 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