Solved

Access 2000: OLAP queries, Excel, and PivotTable conversions

Posted on 2004-10-01
6
197 Views
Last Modified: 2012-05-05
Experts,

I was given some cube files (.cub) and would like to import them into Access...

The OLAP query in Excel makes a PivotTable that's great...but the underlying problem is the speed.  I have one PivotTable running on a dual procesor server, and it hasn't completed in the past several hours.  I was then given a cube file 1/50 the size, tried narrowing down the fields, and ran it on a coworkers laptop and it hasn't completed in the last ~20 minutes.  

If I could get the file in Access, I feel like I could slice and dice it better than Excel is right now...

Thoughts?

-Jess
0
Comment
Question by:jesselavaca
  • 2
  • 2
6 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 12203120
if you can work the data in excel then

1. you could import the excel file to access.
2. you can create a link to the excel file.


how do you open the file in excel?
0
 
LVL 1

Author Comment

by:jesselavaca
ID: 12203208
The file opens as usual, and opens a pivot table with a lot of variables that can included.  I keep drilling down on a pivot table and adding more variables and it slows to a crawl (I have yet to drill down where I need successfully)...I think it's because a .cub is a flat file (not relational) but I'm not positive...

If I could get *all* the data to show, I would just export it to CSV or something and pick it up in Access, but so far no luck.  (I'm up to 3 computers processing this stuff in different ways)...

oh, and my new deadline is Monday @ 5.  uh oh.

-Jess

0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 12203421
you might get some idea here, not sure though, haven't got the time to browse

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/msi/setup/sample__cub_file.asp

good luck
0
 
LVL 1

Author Comment

by:jesselavaca
ID: 12218799
I let Excel try to calculate the values for 1/7 regions all weekend on my high performance desktop, and it crashed.  I don't know why it's so hard...

Anyhow, I had to learn how to use ProClarity, it worked well for analyzing the data cube (but also crashed twice).  I then exported to Excel and got the values I needed.  

Thanks,
Jess
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12898094
PAQed with points refunded (500)

modulo
Community Support Moderator
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

947 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now