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
Solved

How to build an OLAP Cube from Access 2007?

Posted on 2008-06-10
9
6,959 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 500 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

837 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