Ideas for new database/solution

[erniepoe]

Hi all,

So at work we have these reports that we download into Excel format from a central server, and it's usually in the neighborhood of 60k lines, and extends out to column CD, and there are many formulas in those lines. Further, we then create several different reports from pivot tables on different tabs that we simply refresh each month when a new report comes in. This works fine, in the sense that you can get the info you need, without too much work. Unfortuantely, these files are routinely in the neighborhood of 100-150 MB, which is causing many problems for slowness and crashing, and of course the inability to email them to different work partners on different continents.


So I would like to build a database or some kind of solution that could help mitigate the size problem, and I was just wondering if anybody had any advice on what/how I should do it. I was told that Access and SQL queries might be a good solution, so this morning I went and bought some books from Barnes and Noble, but of course have not opened them yet. I'm eager to learn new programs so have no problem just diving into these textbooks if that is indeed the best solution.

But if anybody has any advice on any programs or methodology that would be a great way to tackle this, I'm all ears.

Thanks so much for any advice.

 

[erniepoe]

any ideas?

 

[erniepoe]

decided to bump again. Anyone have any thoughts?

 

[AngelJ]

Going to a proper database might help, but you may have performance issues no matter what considering the amount of data you're working with. How about querying the central server you mentioned directly? Btw, learning SQL and database programs is never a bad thing. If you have the time then dive in!

 

[sous2817]

My gut tells me that there's something wonky in the way you're creating the pivot tables / reports. 60k rows of data extending to column CD isn't a terrible amount of data and really no way it should get to a 100+MB file! Have you looked in to workbook bloat?

Are you looking for an enterprise solution, an department solution, or a single user solution? You may want to consider the scope of what you're doing before going too far down a path. For instance, our company doesn't license Access at the enterprise level, so building a reporting solution w/ Access isn't a good idea. There are reporting services that sit on top of databases that makes reporting a bit easier...Sql Server Reporting Services or Tibco Spotfire are two examples, but I'm sure there are a ton more.

Perhaps if you describe the scope of what you're trying to do (like user base size, etc) a bit more, you may get more detailed information.

 

[erniepoe]

thanks for the messages, guys. The reports have several pivot tables, which is why they end up being so big. My "solution" is something that would only be used by my department (6 people), so it's not something that would be supported at a company wide level or require any real reporting services, it's just for the six of us.

I'm not married to the idea of Access/SQL, its just what was suggested to me so I'm trying to dive in and learn, even if I'm not doing a great job of describing it! But in general, I guess I just want the reassurance that building (or attempting to build) a report in Access and then using SQL queries to extract the data is a good way of going about things. My background is in finance, not IT, and it shows!

 

[AngelJ]

Considering that your background is in finance, and assuming you want to focus on finance, I wouldn't suggest leaving Excel. I got the wrong impression because you said you bought books on Access and SQL. If you start heading toward Access and learning database design, SQL, form and report programming, etc., you might get more sidetracked than you were hoping for. I would recommend that for someone who was most interested in the IT side of things (that would be me in this office).

 

[marious]

Excel 2013 64 bits will do the job without problems