Hi,

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?

Thanks!

after some tests I have found this:
when you open the cube the first time, a query like:
select filter([Measures].members,

[Measures].currentmember.member_caption =

[Measures].currentmember.member_caption) on columns from [Attendance]

is executed on the server!

this cause AS to execute a lot of queries to retrieve the "all members"
value of each measure.
so more measures you have longer the access is!!!

in my case I have 10 measures, but one of these measures is overhidden by a
scope where I execute a complex formula, this cause a delay of 40sec to open
the cube!!!

"Dog" <todd (AT) solishine (DOT) com> wrote

I believe the problem is Adomd's Metadata cache being refreshed, which
includes all the dimension members... alot of data. At present am
trying the following approach:

DataSet ds = conn.GetSchemaDataSet(AdomdSchemaGuid.Measures, null);

Rather than my initial approach which was using CubeDef.Measures.