Looking for 'upsizing' from Access/PivotCharts

Hi All!

I'm doing analysis of system performance metrics recorded from a web application performance test. I have approximately 15 tables, each with unique system and test metrics, ranging in size from 6 thousand to 6 million rows. E.g:

iostats: time, host, disk, read_bps, write_bps, ...
netstats: time, host, active_conns, xmit_errs, resets, ...
teststats: time, test_name, virtual_users, ...
timerstats: time, script_name, timer_name, elapsed_time, ...

Last year i used MS Access + pivot tables to do the analysis of this information, but Access isn't cutting it this year. It frequently goes out to lunch and never returns whenever I try to build the charts i'm looking for (most of which use PivotChart views)

I've spent a few days trying to get my head around using SSAS for this purpose, but have run into a few stumbling blocks

1 - The information in the 15 tables is loosely correlated on time of day, some have one sample per second, others one sample every thirty seconds, and still others are whenever an update decides to roll in. I'm having a very difficult time figuring out how to make a usable 'time' dimension that i can use to chart the information.

2 - I seem to constantly run into 'missing attribute value' errors when trying to build and deploy my analysis services projects that contain cubes. Frequently this seems to be due to cases where i don't have specific information on a specific dimension (e.g. host / cpu / etc) for a given time period.

3 - I'm not sure how to visualize some of the output from the cube interactions that do work. I can get a table working in the cube browser, but how do i chart that? Is the Excel AS plugin the only approach available? I tried to view the tables via access linked tables, but a) Access (2002) complains it can't modify tables against SQL 2005, and b) Access doesn't pull the entire set of data back, just the first 50k records or so.

4 - If i want to calculate disk utilization on a per disk per host basis, would that be a valid use of a dimension hierarchy?

I'm kind of running out of time. My game plan for today is to recalculate all tables to exactly one minute sample intervals and try again, hoping that the reduced row count will make things a little more smooth.

Thanks for any advice!!!

Bob

Several ideas for you:

Try to see if Office 2007 beta is going to work for you. Install Office2007 beta and try if you can fit your data sizes there.

Second. If you are using Analysis Services Excel add-in is not the only choice. You can use Excel Pivot Tables to connect to Analysis Services. You can use many other client applications to diplay Analysis Services data.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Hi Edward!

Thanks for the response. I may take a look at office 2007 today to see how it goes.