Max connections to Analysis Services

[MegM]

Can Analysis Services max out on connections?

We are receiving an error message from our web application, "The connection cannot be used to erform thi soperation. It is either closed or invalid in this context."

We started receiving the error after our site usage increased. We have found a few places where we were not destroying our active catalog. The problem gets worse the more the site is hit, and better when it is left idle for a period of time.

Is there a way to see how many connections analysis services has open?

Any help appreciated!

Thanks,
Meg

 

[MDXer]

You can perfmon the machine As is running on. One of the Counter groups is Analysis Services Connections. How many concurrent users approximately?

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

 

[MegM]

We've checked the perfmon connections - thanks - everything there seems okay though, so we're trying a different route. We know a database server reboot temporarily fixes the issue. We are now looking into our component interaction with COM+, and COM+ interaction with OLAP. Our query works fine when run through debug or through the MDX sample application.

We might have 200+ users in a day, guessing 10+ concurrent users at a time, although this is expected to increase to a max of around 100.

We are also currently working on another issue where our incremental processing of our OLAP database stopped working and gives us a "Connection to the server is lost" error. We have been completely processing the OLAP database since this issue arose. We are working with Microsoft to try to resolve the incremental processing problem.

These issues may be related . . . but we're not sure.

 

[MDXer]

For you current number of concurrent users Analysis Services as an application is able to easliy handle this type of load. if the query can be executed through the MDX sample app then the likely hood of it being Analysis Services as an application I would say is remote. I would continue to look and you COM commponents and see if there is an issue there. Another area to look would be the System configuration. Is the actual machine able to handle the load. If you have basic cubes that aren't to large then again I would say probably isn't the problem but if you have cubes that are GB in size then Resources do become an issue.

As for the processing error I have experienced this as well and can't remember what I did to overcome it, being one of those latenights where I was trying everything I could think of.

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

 

[MegM]

Member properties . . .

We are having a problem with one of our dimensions. Our dimension has ~360,000 members. This is a parent/child dimension with 18 different member properties. This dimension is used in all of our cubes and queries.

We cannot process our OLAP database incrementally after this dimension reaches a certain number of members. The number varies depending out which machine we are testing. We can process the database if we do so completely, but even then we receive cannot find dimension errors during queries asking for lowest leaf members directly. If we add a prequery requesting all the member children (the lowest leaf members), then our queries work.

One question we have is do we have too many member properties? Can these negatively impact performance? How do you know how many is too many?

Any help appreciated!
Meg

 

[MDXer]

Meg,

Yes you can have to many member properties. When alanalysis services is running it loads the Metadata and dimensions definitions of all cubes and all databases into memory. Fortunately shared dimensions are only loaded into memory once per database. Since the member properties are part of the dimension definition they to are read into memory. The only member properties recommended are those absolutley necessary for the dimension and cube to function properly. the type and size of the data contained in the member property is also of issue if you have 360,000 members with a single propery that is a 50 byte value you use almost 18 megs of memory if you have 16 of properties at 50 bytes each you use approx 288 megs of memory. Dimensions and memory usage are also affected by custom security. If you have custom security enabled on a dimension then when a used assigned to that security role logins in a copy of the dimension is read into memory with the members the security role is allowed to access. THis dimension stays in memory until the server or services is restarted. An easy way to tell if it is a memory issue is to see how much memory the msmdsrv.exe process is using. Analysis Services is capable of addressing 3GB of memory if the server is configured with enough memory and the the 3GB PAE/AWE switches are set for the OS. You can configure how much memory AS can address through the server properties or the registry setting (If configuring for 3GB it must be done in the registry). Make sure you have alloted enough memory for Analysis services.

Things that affect MS AS memory usage are:

1) Dimensions
2) Member Properties
3) Distinct Count Measures (Affects memory only during
processing BUT has a huge affect while processing)
4) Custom Security placed on DImensions.



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

 

[MegM]

Hi -

Just a follow-up - Many of our issue were resolved by removing Microsoft Analysis Services\Bin\msmdvldm.exe. This is the executable that deals with large dimensions and there have been some problems with it - especially in connection to incremental processing. After removing the exe our incremental and dimension errors went away.

Also, one of our components was not allowing apartment threading. This helped our consecutive user issues.

Thanks,
Meg