We are developing a product which can be used for developing predictive models and the slicing and dicing of the data in order to provide BI.We are having two kind of data access requirements.For predictive modeling, we need to read data on daily basis and do it row by row. In this the normal SQL Server database is sufficient and we are not getting any issues.In case of slicing and dicing data of huge sizes like 1GB of data having let us say 300 M rows. We want to pivot that data easily with minimum response time.The current SQL Database is having response time issues in this.We like our product to run on any normal client machine with 2GB RAM with Core 2 Duo processor.I would like to know how should I store this data and then how I can create a pivoting experience for each of the dimension.Ideally we will have data of let us say daily sales by sales person by region by product for a large corporation. Then we would like to slice and dice it based on any dimension and also be able to perform aggregation, unique values, maximum, minimum, average values and some other statistical functions.
Quickly Large Data Pivoting
database design