Export Excel connection to Access - .ODC info to .ODBC

I have lots of data to wrangle and I need some help.

I have been using an excel file that has two worksheets of interest to me. They each produce a OLAP pivot table with the data I need to work with. What I would like to do is move those (.odc) connections to access queries so I don't have to hand paste all of this info out and manipulate it and then go through the whole process several more times.

One table is Throughput (number of parts through an operation(s)) by Part Number and by Date. The other is Hours Logged at the operation(s) by Part Number and by Date. I also have a master list of all part numbers with some more data that I have to mix in.

Biggest problem: Each chart is producing its own subset of dates and part numbers so I have to take care to match up the data to run the calculations. I've tried:

• By hand. Got tired with that real quick.
• Using LOOKU开发者_StackOverflow社区P, VLOOKUP, MATCH with INDIRECT and all sorts of tricks.

It's a mess. But I'm confident that if I can put the original pivot tables into Access I can add a few joins and write up a couple queries and it will turn out beautifully.

Worst comes to worse I can copy/paste the pivot table data into access by hand, but what if I want to change or expand the data set? I'd rather work with the raw data.

EDIT:

The data is held on SQL Server and I cannot change that.

The excel pivot tables use a .ODC file for the connection. They gives the following connection string:

Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=[MyCatalog];Data Source=[MySource];MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=[MyCatalog];Data Source=[MySource];MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

(I replaced the actual catalog and source)

Can I use the .odc file information to create a pass through query in Access?

Have you consider using a proper OLAP server?

Comparison of OLAP Servers

Once setup you'll be able to connect your Excel's Pivot Table to the server (as well as other reporting tools).

Talked to our IT dept. The guy who built the Cubes is working on querying the same info into MS Access for me.

Thanks everyone.