SQL and ... I?

[Syt]

Much of my work tasks revolves around creating reports/analysis about our regions' performance.

The problem is that the software we use to store/collect sucks at providing data. You can run reports to depict the situation at one level of operations at one point in time, but that's about it.

What I do on a monthly basis is take snapshots and store the data in a big Excel workbook that has one sheet for each data point (each sheet has our regions - ca. 50 - on the Y axis, the monthly figures on X axis - currently going back to 2008 for some data points).

Since the workbook has 60+ sheets at this point (and I would like to add more) it's becoming a tad unwieldy.

My reports/analyis are based off this data. The various monthly reports are an Excel template where I select reporting period and it fills/ranks the rest through INDEX/MATCH functions.

Additionally I have an Excel data file for annual data.

The problem comes when wanting to do a bit more in depth analysis - which was the month of strongest agent growth? Did any region hit a milestone/record this month? How do the quarterly numbers compare to each other? How does region X compare to similarly developed/populated regions? Etc.

Because Excel is a bit limited in that regard and I have to build a lot manually I'm considering looking at a database setup for our data which might give me more tools/flexibility. Yes, it'll take time to set up, optimize and program the required interfaces/queries, but I figure once that crunch job's done, it should be easier to maintain/expand than tacking on more Excels to the Excels.



Secondly, I would love to build a "ground up" database for our numbers. Currently you can only run one report on one level. So you can get an overview for one point in time for all regions. But if you want to drill down to a region's offices and that office's agents you have to run separate reports on each level and then cobble together comparisons and the likes.

I would much rather take the monthly agents' data into a separate database and then build our internal reports from there, and offering the chance to do long term comparisons and deeper analysis than what our ancient software allows.

In Utopia, I would set up a web interface for common reports for management that they can check on their iPads on the fly (currently I have to produce PDFs or hard copy printouts in 99% of the time for them).



Questions:
- would an SQL set up help?
- what would be a good SQL environment for someone like me who hasn't worked with it before?
- what would be feasible alternatives to an SQL database?

[Berkut]

"SQL" is not a database - SQL stands for Standard Query Language, and it is just the name of the language that is used to query a SQL compliant database (which is most databases out there). Each implemtnation tends to use SQL, but often many of them add in their own proprietary tweaks, so Oracle SQL might be a little different than MySQL, but both meet the standards of basic SQL.

Think of "SQL" the same way you think of "JAVA" or "C++". It is just the language used to query a database.

So your question is really more about what is an appropriate database to use for your needs/cost.

The gold standard is of course Oracle, and the price of it reflects that. My own experience in development is largely with Oracle databases.

There are a lot of other options though. The most important consideration for yourself is probably not how hard it is to teach yourself enough SQL to do what you want, but rather how much work it is to setup and maintain a database server for your needs. It sounds like this is for work, so what kind of budget do you have? Does it need to be highly available? Do you have a DBA to help you set it up and maintain it?

You might consider using something like Amazon cloud services. I think for setting up a database and maintaining it for you, that kind of option really cannot be beat.

[Darth Wagtaros]

Berkut is right. AWS would be a good way to learn cheeply without having to do any support on your side.  Oracle is the gold standard, and charges platinum prices for it.  There are a number of version of SQL databases, each has use cases.  You could even just use MS Access. Do a search of the Interwebs for existing products that already do what you need and try some of them out.

[Berkut]

Berkut is right.

Adding to sig.

[Zanza]

Questions:
- would an SQL set up help?
- what would be a good SQL environment for someone like me who hasn't worked with it before?
- what would be feasible alternatives to an SQL database?

Your requirement describes a business intelligence system, not a transactional database.

No idea what BI solutions are available without considerable IT support from your organisation. My company uses SAP Business Warehouse with a QlikView frontend.

Secondly, I would love to build a "ground up" database for our numbers. Currently you can only run one report on one level. So you can get an overview for one point in time for all regions. But if you want to drill down to a region's offices and that office's agents you have to run separate reports on each level and then cobble together comparisons and the likes.

That part is typically very hard to do in a transactional ("SQL") database and very simple in a business intelligence solution.

[Syt]

Thanks for the feedback, guys. AWS looks interesting, but I'm sure management wouldn't want the data on servers not managed by us in some form (it's more of an emotional barrier than a rational one).

[DontSayBanana]

To approach this from a slightly different perspective, how interconnected are those 60 Excel sheets?  Are they split apart for increased human readability, or is the information truly unique to each sheet?  The newbie way of looking at would be to consider each sheet a table, but if there's redundant information, they could be more along the lines of views- in which case, an RDBMS (relational database management system) would be a really useful alternative.

Another thing to think about is how much of the tables are data derived or computed from other fields?  If there's a lot more raw data than computed data, a database would streamline your storage, but if there's a lot of calculation going on, Zanza would be right that you would want something more along the lines of a business intelligence system, a la SAP.

[Vricklund]

The short answer is YES, a database is what you want. Forget about "bi" solutions, they're just a fancy front-end on some sort of database. Don't start there, start looking at how you structure your data.


If you've done nothing of the sort yourself before it might be a bit of a stretch but it's not rocket science. I would still recommend you work out what you want then get an IT consultant to set it up.

DSB raises some valid points. To get a first idea about how this could be set up I suggest you start using pivot tables in excel. Pivot tables in themselves work almost like a "real" database in that they use "raw" data. Instead of using 50 sheets of data is it possible to get all the data into one sheet and display what you want in a pivot table?

Maybe something as simple as the excel plugin power pivot is all you need right now? With it you can set up different workbooks as sources and compile your data in power pivot. The whole setup is pretty similar to how you would set up a relational database in SQL.

F

[Caliga]

Is your IT group helpful at all?  I don't think you should be trying to figure this out on your own if you can avoid it, and if you somehow do and don't involve them, they may not help support it.

[Darth Wagtaros]

Cal's right. The Cloud will handle this.

[Syt]

Is your IT group helpful at all?  I don't think you should be trying to figure this out on your own if you can avoid it, and if you somehow do and don't involve them, they may not help support it.

That's the next step. I will talk to our Canindians (who are super helpful) about what we're looking for, ask what we already have and what our options are.

[Caliga]

Cal's right. The Cloud will handle this.

That is NOT what I meant and you know it.

[Baron von Schtinkenbutt]

If you want to try an SQL database for this task, start with SQLite.   SQLite Studio is a nice front-end option for it.  You can easily export your existing Excel sheets as CSV and suck them in to the database.

If I understand your data, though, it sounds like you have "data points" that are composed of two columns of data: a region name and a monthly sales snapshot.  I'm not sure a relational database is the best data structure for this task.  An ever-expanding CSV file would be a better way to store the data.  I don't know how comfortable with programming you are or wish to become, but Python has a really nice package that makes dealing with CSV easy, and iPython is an excellent environment for using the language interactively for the types of analysis you want to do.

[Darth Wagtaros]

Code Academy.