We have an SSAS 2005 cube with around 30 million rows in a single fact
table, and a dimension hierarchy that has 5 levels and contains around
5 million leaf level members.
We access the cube through client-app that uses the OWC controls. When
the app starts up, it opens a connection to the database, and walks
through and gathers up the names of all of the measures in the cube.
There are about 50 measures in the cube, and there's only one cube.
We have a scheduled job that processes the cube each night, and the
first user that opens the app and connects to the SSAS database has to
wait about 30 minutes before being presented with a list of measures
that may be used to query. That's right: 30 MINUTES.
All the while during this time, the CPU on the SSAS server jumps up and
stays right around 50%. If another unlucky user opens the app during
this time, the CPU jumps to 100% and stays there for the duration.
[It's a dual proc server].
After SSAS finishes doing whatever it has to do during this time, query
response time on the cube is just fine. Any subsequent user of the
client app can open and access the cube with no latency, for the rest
of the day. But after the cube is processed the next night, we're
back to the same problem.
It almost appears that not all of the processing is happening until the
first time someone wants to read the cube metadata, which seems odd.
Is there something I'm missing here? Some option that can ensure that
*all* the processing takes place in the middle of the night, and the
first person who accesses the cube doesn't have to wait so long?