help! question about hardware request for SQL Server and Analysis Services

I built a database with a huge table which has 18 billion lines for 6(or 7) columns.

And in the Analysis Services the table is used to create a dimention and a measure.

One of the other two dimentions is made form a 4000 line table,and the other is

made from a 3 million line table(time dimension table).

The specification of my Server is Windows Server 2003 with Xeon Intel Cpu 5160 @.3.00GHz,

2.99GHz,4.00G RAM.

The problem comes out when i start the processing of Analysis Services.

the "time out" error comes out after 1 hour, however, the performance i need

is completing the Processing for one cube within 10 munites.

I want to know the hardware request to reach my needs and how to speed the

processing of Analysis Services.

Thank you

So, to be clear, you have an 18 billion row fact table, you have built a cube on top of it and you want a full process to complete in 10 minutes?

Chris

|||

Dear Mr

yes! I wanna a full process within 10 minutes.

Can it be done?

thanks in advance

tomigisi

|||

The short answer is no, not if you're using MOLAP storage for your measure group. The best throughput I've ever seen for cube processing with MOLAP storage is around 200000 rows per second, and that was a maximum rather than something that could be consistently achieved throughout the whole process; you should be happy with 60-70000 rows per second if you've got a top-end server and a properly-tuned relational datasource.

I would experiment with using ROLAP or HOLAP storage, or with pre-aggregating your fact table in some way to reduce the overall number of rows.

Sorry,

Chris

|||

We have a monster server: 8 - 3.2GHZ processors, a disk frame that reads/writes 400 MB/sec, 32 GB of ram, etc and we couldn't even process 18 Billion rows that fast. The slowdown is reading rows from the DB, and most likely not the server (unless you have slow disks).

sql