I'm managing a lot of sensor data and need to do charting, smoothing, stats over time on the data. Currently I import the data from flat files using VBA to do ETL into Excel.
The format of the data is:
[DataTime][SystemID][SystemParams 1][SystemParams ..n][Data 1][Data ...n]
There are 30 System Parameters and 8000 datapoints in each file. All this is fine in Excel where I can manipulate, filter and pivot off the system parameters and then chart the Data over time.
But I want to get all rows out of Excel into a database as there will be 10K files showing up every week in this format.
When I export the rows from Excel to SQL Server (using the SQL Server import/export tools I only get 200 columns of the 8000+ from my Excel row due to the column restrictions in SQL Server
What would be the correct way of structuring this data so that I can run queries on the System Parameters an retrieve the 8000 data points back into Excel for charting?
I'd like to stay in SQL Server but have read I might be better off with NoSQL. All advice gratefully accepted..