Using Excel as a Database
December 5, 2010 11:47 AM Subscribe
Database software that is as flexible as Excel?
So if you're using excel as a database, and shouldn't be, what's the next step up?
What database software has the same reformulate-and-recalculate-reconceptualize-on-the-fly abilities as Excel? An Excel of Databases?
So if you're using excel as a database, and shouldn't be, what's the next step up?
What database software has the same reformulate-and-recalculate-reconceptualize-on-the-fly abilities as Excel? An Excel of Databases?
What kind of problems are you trying to solve? What limitations of Excel are you running into?
posted by mmascolino at 12:37 PM on December 5, 2010
posted by mmascolino at 12:37 PM on December 5, 2010
I learned to use Access after a few too many times when an Excel spreadsheet got mis-sorted and data was irrecoverably turned into a mismatched pile of numbers. Besides easy compatibility with Excel, Access also has PivotTables and PivotCharts built in, if that's the sort of on-the-fly data analysis you like. Relational databases mean you don't have multiple copies of the same data on different spreadsheets (waiting to get messed up) and you can build very complex queries amazingly quickly once you get the hang of.
posted by hydropsyche at 5:01 PM on December 5, 2010
posted by hydropsyche at 5:01 PM on December 5, 2010
Response by poster: > What kind of problems are you trying to solve? What limitations of Excel are you running into?
OK, hypothetical example:
I have a database for all 50 US states. There's some basic info associated with each state. Population, capital, size, etc. So far, so good; this is easily maintained in Excel.
Now I want to add more information - track all these as they vary in time. Still do-able in Excel. I do statistics on these - still fine for excel.
Next I want to add subcategories that will vary with each state. Say, major tourist attractions. The number will vary for each state. Each major tourist attraction will be tracked individually. And the salient characteristics will be measured differently for each type of tourist attraction. E.g., a tourist attraction will have numbers associated with it, but the numbers will mean different things for each tourist attraction. And the number of numbers will be different for each. Tourist attractions will be divided by type, as well; there will be some general categories, e.g., ski resorts, and some unique or uncategorizable ones as well. Track all those through time. And statistical analysis on all this, both on its own, and combined with the other state information, etc.
OMG! Sea World moved to Nebraska! Have to change the database!
Oops! I want to categorize things differently. Can I go back and rearrange everything easily?
Oh no! I forgot the territories! I want to add them in, but as their own category.
Hey, ten weeks later, I want to add a different measurement. I've been using calendar time, but now I want to see variation vs..... how about cumulative tourist miles walked within each attraction.
Eleven weeks later, I want to add all kinds of import/export data for each state, and not just gross $$, but a detailed breakdown of actual imports and exports, so the categories will be different for each state, and for exporters, I want to add information on them, too, to associate with their individual export shipments. And all this information will vary with time, and some will be non-quantitative, e.g., reported problems with individual export shipments, problems discovered by inspections of facilities.
The nature of non-quantitative items will vary; they may have numbers assigned to them, and they may be assigned classification codes. Twenty weeks in, I want to change all the coding for the non-quantitative items.
(Assume data entry is not a problem!)
I want a lot of things, it seems. I guess I'm aiming for Total Data Set Flexibility.
(Excel, by itself, is too small, two-dimensional, and numerical. But for what it does, it does well, and is very flexible and easy to use.)
posted by coffeefilter at 5:30 PM on December 5, 2010
OK, hypothetical example:
I have a database for all 50 US states. There's some basic info associated with each state. Population, capital, size, etc. So far, so good; this is easily maintained in Excel.
Now I want to add more information - track all these as they vary in time. Still do-able in Excel. I do statistics on these - still fine for excel.
Next I want to add subcategories that will vary with each state. Say, major tourist attractions. The number will vary for each state. Each major tourist attraction will be tracked individually. And the salient characteristics will be measured differently for each type of tourist attraction. E.g., a tourist attraction will have numbers associated with it, but the numbers will mean different things for each tourist attraction. And the number of numbers will be different for each. Tourist attractions will be divided by type, as well; there will be some general categories, e.g., ski resorts, and some unique or uncategorizable ones as well. Track all those through time. And statistical analysis on all this, both on its own, and combined with the other state information, etc.
OMG! Sea World moved to Nebraska! Have to change the database!
Oops! I want to categorize things differently. Can I go back and rearrange everything easily?
Oh no! I forgot the territories! I want to add them in, but as their own category.
Hey, ten weeks later, I want to add a different measurement. I've been using calendar time, but now I want to see variation vs..... how about cumulative tourist miles walked within each attraction.
Eleven weeks later, I want to add all kinds of import/export data for each state, and not just gross $$, but a detailed breakdown of actual imports and exports, so the categories will be different for each state, and for exporters, I want to add information on them, too, to associate with their individual export shipments. And all this information will vary with time, and some will be non-quantitative, e.g., reported problems with individual export shipments, problems discovered by inspections of facilities.
The nature of non-quantitative items will vary; they may have numbers assigned to them, and they may be assigned classification codes. Twenty weeks in, I want to change all the coding for the non-quantitative items.
(Assume data entry is not a problem!)
I want a lot of things, it seems. I guess I'm aiming for Total Data Set Flexibility.
(Excel, by itself, is too small, two-dimensional, and numerical. But for what it does, it does well, and is very flexible and easy to use.)
posted by coffeefilter at 5:30 PM on December 5, 2010
Response by poster: Cont'd
And then we have the Circus, which moves from state to state, and I want to track that as an individual attraction, and also as part of the data of the states it travels to, when it travels to them.
And then two circuses merge. The database needs to know this, so it can be traced through time.
---
So really, there are:
1. - Data Problem - Highly Complex Data that Does Not Behave Nicely ...
2. - End User Problem - Making Stuff Up as I Go!
.
posted by coffeefilter at 5:47 PM on December 5, 2010
And then we have the Circus, which moves from state to state, and I want to track that as an individual attraction, and also as part of the data of the states it travels to, when it travels to them.
And then two circuses merge. The database needs to know this, so it can be traced through time.
---
So really, there are:
1. - Data Problem - Highly Complex Data that Does Not Behave Nicely ...
2. - End User Problem - Making Stuff Up as I Go!
.
posted by coffeefilter at 5:47 PM on December 5, 2010
Best answer: The problem I have with most Excel spreadsheets I've seen is that it's designed to combine data with layout with calculation, and very few spreadsheets need or want that. It makes it very difficult to separate them when you need to different things with the same data. As a general rule, I think if you find yourself wanting to use pivot tables, it's worth considering if you should ditch spreadsheets.
Ideally, you'd separate these concerns:
* data in a database, like Access or MySQL (or oracle, postgres, etc),
* a simple query builder (like Access, Crystal Reports, SQL or perhaps Pentaho)
* a separate formatter to handle layout, like Access or PHP.
A lot of web applications can be described with the above design. AFAIK, we don't yet have anything that's as easy to use as spreadsheets, but I should look into it some day.
There is a class of spreadsheets for which this is not appropriate. Think amortized payment schedules, where each payment's breakdown depends on the next. But given what you've described, this is not the case. It can take a bit of work, but what you can do in that is break the data you have into different tables. A population table, a tourist attractions table and so on. There is a subtle challenge here: the math theory underlying SQL does not do subcategories well (a complicated explanation here). But for the simpler stuff you should be okay.
For most business people, I'd say that MS Access is most likely to fit within your organization, but if you're adventurous, there's a number of ways forward. You could try learning a bit of SQL with an online interactive learning tool. Or find some books or enroll in a night class for the spring semester at your local community college on MS access or databases.
posted by pwnguin at 7:26 PM on December 5, 2010
Ideally, you'd separate these concerns:
* data in a database, like Access or MySQL (or oracle, postgres, etc),
* a simple query builder (like Access, Crystal Reports, SQL or perhaps Pentaho)
* a separate formatter to handle layout, like Access or PHP.
A lot of web applications can be described with the above design. AFAIK, we don't yet have anything that's as easy to use as spreadsheets, but I should look into it some day.
There is a class of spreadsheets for which this is not appropriate. Think amortized payment schedules, where each payment's breakdown depends on the next. But given what you've described, this is not the case. It can take a bit of work, but what you can do in that is break the data you have into different tables. A population table, a tourist attractions table and so on. There is a subtle challenge here: the math theory underlying SQL does not do subcategories well (a complicated explanation here). But for the simpler stuff you should be okay.
For most business people, I'd say that MS Access is most likely to fit within your organization, but if you're adventurous, there's a number of ways forward. You could try learning a bit of SQL with an online interactive learning tool. Or find some books or enroll in a night class for the spring semester at your local community college on MS access or databases.
posted by pwnguin at 7:26 PM on December 5, 2010
It seems like what you're asking for is the basic business analytics, or business intelligence, capabilities. When I worked in BI we use SQL to handle the data and then pulled it into excel via VBA as odinsdream was talking about.
posted by Aizkolari at 3:55 AM on December 6, 2010
posted by Aizkolari at 3:55 AM on December 6, 2010
Note that SSRS is available in the free SQL Server 2008 Express. Some of the features are limited, but you don't have to go the whole 'Enterprise' hog just to try it out.
posted by robertc at 3:26 PM on December 6, 2010
posted by robertc at 3:26 PM on December 6, 2010
This thread is closed to new comments.
posted by amethysts at 12:21 PM on December 5, 2010