1 of 1 people found this helpful
You've mentioned quite a few of the options. It does sound like a rather difficult issue. I have a couple of thoughts:
1. Maybe posting the data structure would help. There are some very creative data people on the forums who might have good ideas about the structure and how it could be (easily?) modified.
2. Moving the data from Excel/txt/csv/Access (all of which use the Microsoft JET driver and thus have the 255 column limit) to a data source that does not have the same issue (SQL Server, MySQL, etc...) would get you past the limit. However, I wonder how easy analysis will be when you have 2000 dimensions and measures.
I'm going to try getting the original extract in spss and seeing if that works as I can't see any way of getting it out of excel to anywhere in full (unless my understanding of what I have read is wrong). I'm not too sure how tableau works with spss files though.
As to your last point I do agree with you here
Steve Wexler wrote a wonderful set of posts on using Tableau for survey data at http://www.datarevelations.com/category/visualizing-survey-data-and-likert-scales, and he includes instructions on using the Tableau data reshaper.
Based on your description, where I think I'd start is:
1) Get a sense of the layout of that data - what makes a question, what makes an answer, what are your question types
2) Use the data to create some dimension tables of question types, questions & answers (& anything else needed
3) Use the Tableau data reshaper as Steve describes to create two fact tables, one for respondents and one for responses. This will take those 64 columns down to 1.
4) Write some custom SQL or a query in your tool to do a 2nd level of reshaping to appropriately assign dimensions. For example, you might take the 1 answer column and break it up into 8 columns with 8 possible values.
5) use that as your data source
I've used this process succesfully with survey data that had a combination of yes/no, multiple answer, and Likert scales. It took some work to get started, however at this point my ETL process is really straightforward and when the survey changes all I have to do is add some rows to the dimension tables.