Processing Large Amount of Data for Cube

[Rosendo]

For Analysis Services, what is the most effective method
or strategy for processing a cube with a large amount of
data such as my Web Logs taken from IIS? For the fact
table, with the grain specified as per click, this equates
to approximately 100 million rows per month or 1 billion
rows per year.

Note: I am exploring several options that could
effectively partition the data (i.e. virtual cubes.)

 

[MDXer]

Developing a partitioning strategy can be quite complex because what may help in processing time may affect query performance. But it is possible to develop an effective strategy that aids in both processing and querying. The main key is how is the data structured in DW and how will users be querying it. a good place to start though would be to Partion the cube at a monthly maybe even weekly level will probably give you the best results in build times and depending on what level most queries get ran at it may even give a boos in query times as long as you apply slicing to your partitions. You may even want to explore Other avenues of partitioning in conjunction with time. It is possible to get both acceptable build and query performace on large cubes you just have to make sure that the cube is structured for optimum preformace.

Adding lots of partitions can be cumbersome so you may want to look into writing dso application that will automatically add new partitions and process the most current partition, you may even look into doing it in .NET to allow you to multithread it for partition processing.

HTH
PAul


Paul