How do I automagically display trending of complex data?

October 26, 2009 6:41 AM

I need some suggestions for data analysis and displaying trending information. At the moment I'm doing this with a huge amount of Excel pivote tables. The goal is to highlight increasing trends and top hitters in some sort of (semi)automated way. The catch is that the data is pretty deep. For example, say I have a metric for "server rebooted for unknown reason"(which, by the way, is one of 50 things measured)...I'd like to also see the break down by server type and then by, say, location and then to see it by week.

What I'm not looking for is a pointer to Tufte or "sparklines" or a generic point to something like "R".

posted by Spumante to computers & internet (6 comments total)

I'm not clear what you are looking for.

Are you looking for a streamlined way to analyze this data in Excel? Excel's pivot tables are a powerful data analysis tool, however, it of course has its limits, as it sounds like you've discovered.

So what, exactly, do you want to do that you can't seem to be able to do with Excel? It's pretty easy to break down data in pivot tables by the way you suggest ("by server type and then by, say, location and then to see it by week"), but only if the raw data are formatted properly.
posted by dfriedman at 6:55 AM on October 26, 2009

Thanks for asking for clarification....

The problem I'm having with the pivot tables is that they've become unwieldy. There's so much data that I've got a 100 tables in 10s of tab and upkeep has become to hard. Additionally, it's gotten too complex for other people to read it easily.

The pivot table examples I've found are all pretty simple.

So, either some suggestions to do this better with pivot tables(or some other Excel methods) or some other software that will suck up the data and spit out the trending.

I'm not wed to Excel. The data is on a read-only SQL DB.
posted by Spumante at 7:33 AM on October 26, 2009

Yeah, that's a point where Excel's usefulness begins to come to a screeching halt. I'd put the data into, say, an Access database and allow your users to run reports as needed. That way you can separate the reports from the raw data and update the data as needed.
posted by dfriedman at 7:37 AM on October 26, 2009

Yes, you want to get out of the GUI and move to something scriptable. Put thet data in a SQL database and then use something like R to pull out subsets of data and create reports on demand.
posted by chrisamiller at 7:46 AM on October 26, 2009

By "so much data" do you mean "lots of different outcomes stratified by lots of variables"? Assuming that the analysis you're doing is correct (knowing that would require lots of details), it sounds like your basic problem is just the software engine. You're going to have to learn how to create auto-updating reports out of Access or something harder. Here and lots of other posts have similar questions.

If you're willing to spend money for something easy, Tableau has amazing capacities and is allegedly dead easy.
posted by a robot made out of meat at 8:34 AM on October 26, 2009 [2 favorites]

I suppose that I mean capabilities. It's actually complete overkill for just the application you want (basic dashboards), but maybe you want more options.
posted by a robot made out of meat at 8:43 AM on October 26, 2009