Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to build an OLAP Cube from Access 2007?

Posted on 2008-06-10
9
Medium Priority
?
7,368 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

688 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