How should we manage large database

Question

We currently manage a large volume of economic data in Excel in my organisation. All of the data is downloaded from different online databases into Excel spreadsheets (one for each data frequency including annual, monthly, quarterly) - and then one main spreadsheet organises everything and creates tables that we need regularly. By organise, I mean that many of the things we need are simply identities ($Z=X+Y$ where we would have only downloaded data on $X$ and $Y$)

My view is that this could be done much more efficiently in R - where we'd automate the updating of the data and then spit out the tables that we need. But I am not trained at all in data management.

Would you all recommend a better way of doing this, or are there pitfalls to using R that I am not considering.

Answer 1

As mentioned in another answer, you should consider using a database. In the long run it will make your life easier.

But, R can help you automate things. You can store excel files in specific folders and/or with specific names (or patterns of names ex. file1_day1, file1_day2 etc) and then create R scripts that process them and produce the report you want.

Since you are using excel files (nothing personal with excel, it is a great program) I am inclined to think that your data will fit in a decent computer running R. In any case, if you end up using R, you should check out data.table package.

Furthermore, R can help you create more complicate reports. Plus, its learning path is not too steep.

Answer 2

Excel is not a good home for data. There's no rollback, there's no audit trail, it's hard to automate and its ease of use makes it particularly vulnerable to human error.

R has good facilities for interactive data and can compactly serialize data objects, but those are only convenience features.

Running your own Oracle, MySQL/MariaDB/Postgres server is fine if you can afford a full-time DB administrator and have the back-up, redundancy and off-site recovery that you need for mission critical infrastructure.

AWS will be better at infrastructure than any ordinary business will ever be. Even Apple uses them. They have a variety of data warehousing options, all of which will take you from small to huge for less than you would spend.

But no. Desktop solutions are not the way to go.

Answer 3

If it is not that big, use PostgreSQL/MySQL/SQL Server. Don't use R. R is suited for complicated computations and visualizations.

One way or another, you and your colleagues will search and update your data and databases are good on that. See this post for more details: Do modern R and/or Python libraries make SQL obsolete?

Hire someone who could do this.

Answer 4

You can easily import your data into R by dumping your data as a CSV file (and SQL as well). R will easily perform the operations you want. You can then access an SQL program (SQLite is easy) from within R and use R as your frontend (interface). The other issue is that R is free and you can probably get this solution working without any outside support.

The point the replies make here is that SQL queries will easily perform the numerical operations you want, whilst R can perform advanced stats/maths, which SQL can't. In addition,

1. Why use a complicated frontend?
2. R has data volume limitations
3. R is not well suited for multiple user access

Point 1 Advantages of R, I would simply mention that, besides a databases interface, if you ever wanted to do more complicated stuff - e.g. graph your data - then R is really good. ggplot2 is an industrial standard graphing package within R, its a bit complicated to use (but not really) and there at least 2 books dedicated to it. If want to perform data munging for example a time series analysis, which economists do all the time, R is really good.

Point 2 However, R has limitations on the amount of data it will handle at any one time, the heavy duty databases mentioned here will not.

Point 3 This is a very good point and one you need to consider carefully. It is really restricted for an individual user who knows how to use R, which is not ideal for accessing the database. For example the R specialist might be on sick-leave that day and how to you access your database?

I'm not from the commercial sector (you can probably tell), a compromise maybe to have a direct heavy duty SQL style database, which R can access once it is setup via a DBI (database interface).

Word of warning The days of Microsoft Access are over, when a database could easily be setup and queried. The databases spoken about here are not free (Access was effectively free with the old MS packages) and will require external support to set them up. Personally, I would do the R thing, because without timeseries and graphs what is modern economics? (We do the same thing in academic medical).