3

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.

|improve this question
2

Does this product have a dedicated database server or is the database being hosted on the limited workstation that you described?

To pivot data for analysis like this you need to first flatten the data structure, and you can't flatten the data structure without doing a LOT of joins. The performance hit becomes more apparent when joining tables with millions of records.

Once the data becomes flattened the actual pivoting of the data is less intensive.

There are many ways to do this but trying to do this directly from a normalized transactional RDBMS is not good. This will put too much load on your database server and detract from typical day-to-day application transactions (CRUD).

One way you can do this is to create a seperate denormalized database for analyitical reporting and routinely stream data to update this repository. A DataMart is a good example of this.

Further, database vendors such as Oracle have Streams that allow for constant scheduled updates of the data mart from the transactional database.

The advantages are clear, the load generated from running reports and analytics is offloading improving application performance, the streams can run during off-peak hours as well.

The cons are that the Data Mart will never have REAL-TIME data.

|improve this answer
1

It sounds like a solid case for using MongoDB instead of an relational DB store.

|improve this answer
  • Thanks for the suggestion, we are trying out MongoDB if any further assistance required will get back to you. – Kuntal Shah Jun 30 '11 at 8:05
  • Hey Denis, We have tried with MongoDB. I wanted to know whether Mongo is a columnar database or not. I have also found that we created one database of half a million rows surprisingly the mongo db database was larger in the size then SQL server. I want to know how exactly should we use Mongo DB? How do we query the data efficiently inside Mongo DB. The objective is to work on 50M rows and then pivot that data. – Kuntal Shah Jul 4 '11 at 9:15
  • To be very frank, I'm the wrong person to ask. I'm a Postgres junky, myself. It's just that your particular use case (which sounds like an enormous EAV table) would have gotten me to look into MongoDB. I'd advise to hop over to SO for the gory details. – Denis de Bernardy Jul 4 '11 at 10:35
0

Wouldn't you just use SQL Server Datamart/Data Warehousing? Maybe clients don't want to pay for this just to use your product.

|improve this answer

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.