Veeerrrry slow rebuild of cube aggregations 2

[dean12]

Using SQL2000 / AS with SP4 applied. I've got a cube with a fact table of about 1.5 million rows. Running on 2003 Server with 4 CPU machine, 2Gig RAM. Without getting into a lot of details, this MOLAP cube takes up to 30 hours to rebuild. After 6 hours it's done only 36 of the 300+ aggregations.

Time to build the fact table and supporting child tables takes 115 minutes.

I know not a lot of details but this thing just doesn't seem to be very complex - just a decent size fact table. Any thoughts on where to focus attention on resolving this speed problem?

 

[dean12]

Some follow-up information.

The cube processing finally finished after 10 hours. There were not other users operating on the server.
There are 815,499 rows in the fact table.
There are 19 supporting tables linked to the fact table.
The largest supporting table has 214,910 rows. All other supporting tables are below 2,000 records, most all below 100 records.
There are 41 shared dimensions.
There are 0 private dimensions.
There are 41 measures.
There are 21 calculated members.
Cube storage mode is MOLAP.

 

[MDXer]

Are any of the 41 measures a distinct count?

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[dean12]

Knew your were going to ask that - yes 1.

 

[MDXer]

LOL.


That is the root of your issue.

What is happening is you aren't applying you 300+ aggregations to the cube, but rather applying you 300+ aggregations to each distinct value.

Here is how you fix it.

Copy your cube and then paste it back into the DB Name it [CurrentName]Dist. Open the cube in the cube editor and delete all measures except your Distinct count measure.

Now open the original cube in the cube editor and delete the distinct count measure.

You should now have 2 cubes.

CurrentCube and CurrentCubeDist.

Now creat a virtual cube with these 2 cubes.

You will need to edit the aggregations on the Distinct count cubes to be something more within your process window. Also you need to move and Calcs that use the Distinct count measure to the virtual cube. Personally I carry all calcs in the virtual cube.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[dean12]

I knew you were going there. I found an excellent article discussing distinctcount on Microsoft's site no less and it tells the same tale. They didn't mention the calculated values issue however so very good tip. Super job - thanks.

 

[MDXer]

Glad I could Help.


Shoot Me! Shoot Me NOW!!!
- Daffy Duck