So I work for a smaller company and currently we need to do our data processing with a desktop solution. We're running into memory limitations and the overall solution in most people's eyes (non-technical staff) is that setting up a database for the data and doing our work on a database instead of the massive flat files (SPS files mostly or the lesser known Triple-S) we currently work on.

I would likely be the one that would be working within this database to do all the data cleaning, validation and variable creation and so I'd like to make sure what I imagine is possible and to also see other possible options.

The data we are using is respondent level survey data. We will be updating the database every quarter with about 40,000 respondents and we have about 40,000 vectors of data per respondent (largely sparse data).

One of the more advanced calculations and operations I would need to do is, I would need to determine the standard deviation of a vector of data and then reduce a respondent's response to no more then 2 standard deviations away from the mean.

Also, creating new vectors of data. (When grouping respondents we will often mark them as apart of 1 of many groups and I would need a vector containing this data to work off of and this would most likely become its own table with various other data.)

I guess I'm just looking for some guidance, I have no problem learning SQL or using MySQL or whatever but I don't know if those are the correct solutions or if I should be looking at something else all together. I know what I need to do to the data but I don't know the best platform to do it on.

I apologize if this is too broad a subject but any guidance is appreciated.


My initial reaction is that you should have everything you need in a standard Microsoft SQL Server installation. Integration Services to do the ETL from your flat files into SQL DB engine. Then the T-SQL language and for your calculations and operations, .NET for more advanced operations. Analysis Services and Report Server engines for the online analysis/drilldowns and for producing reports for clients and staff. Couple that with MS Office, Excel and PowerPivot, and you'll have a complete suite of Business Intelligence tools for all your needs and more.

Just my 2c

Good luck!

  • Thanks for the advice! That sounds like the correct solution to me as were already writing the calculations in VB.Net and have a smaller MS SQL Server running a few other functions!
    – Zifendale
    Aug 28 '13 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.