I am testing Analysis Service installed on it's own server.

I have installed Analysis Services with SP3a on a Windows 2000
Advanced Server with SP4. I have SQL Server 2000 running on an XP
Professional SP2 workstation.

I can connect from the Analysis Manager on Win2K and retrieve the
tables/views on XP Pro. However when I process a dimension I receive
the following error:

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied.;08001;

The XP Pro has Norton Internet Security installed, and I suspected the
Firewall may be blocking, but the error occurs when Norton is
disabled.

Any ideas? All other network comms seem ok, drive mapping and
connection through Enterprise Manager.

The service account that the MSSQLServerOLAPService (msmdsrv.exe) is running
under must be domain account with access to the data source. The default is
to use Local System which is fine for local databases, but the Local System
account does not have any network access. Use the Services applet to change
the service account to a domain account that has netowrk access to the data
soruce.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Mark" <one (AT) one (DOT) com> wrote

Thanks for that, changing the service account sorted things.

The reason for the test is on going issue.

I have a client with SQL Server 2000 SP3a on a dual Xeon 2.4Ghz box
with 2GB ram on Windows Server 2003. This is hosting a Datawarehouse
solution. The client is increasing their requirement for OLAP cubes
and reporting. The server has both the database and Analysis Services
installed. There are currently 7 cubes on the AS server. The Server
memory is probably operating at the limit with the Page File reaching
3.5GB. I have experienced an issue when building these
cubes/dimensions where the query reads the rows, produces a completed
processing time but then locks up just before the end, and seemingly
the only solution is to stop the olap service. Restarting results in
the following error:

Error 1053: The service did not respond to the start or control
request in a timely fashion.

The service however continues to start, consuming memory, and will
eventually show a started status.

I have set up a test with Analysis Services on it's own server to see
if this is a better solution. I have a copy of the database feeding
the olap cubes to see if the system behaves in the same way. The
server I have is a PIII 733Mhz with 512MB ram and 2x20GB drives.
So far AS has locked up in the same way as above when processing the
last of 12 dimensions. Stopping the olap service and restarting
produces the same error as above. When the service has restarted it
is consuming 65mb of physical ram and 1.1GB of virtual memory, when it
locks it has 117mb of ram and 1.29GB of virtual memory.

Is the lock up issue due to reaching a resource limit?

The cubes are not huge the biggest being 43mb and the smallest 11mb.
I can set up queries to build cub files through msquery with the same
data without a problem.

Any suggestions appreciated, am I experiencing an error condition that
can be rectified or is it a case of building a server with a
particular configuration to cope.


On Wed, 27 Jul 2005 09:03:34 -0700, "Dave Wickert [MSFT]"
<dwickert (AT) online (DOT) microsoft.com> wrote:

On further investigation regarding the lock up problem. I have left
the dimension processing running and although the database rows are
processed in 2 minustes AS Manager appears to hang (not responding in
Task Manager). However leaving it in this state the next phase
(committing transaction in database occurs sometime after, perhaps an
hour) so the dimension build may eventually complete, but why so long?


On Wed, 27 Jul 2005 19:53:35 +0100, Mark <one (AT) one (DOT) com> wrote:

In all probability there was an outstanding read lock (an outstanding query)
or soemthing which was stopping the transaction commit from happening.
What does the virtual memory look like using either Perfmon or Task Manager
for the msmdsrv process? I am worried that using that much page file that AS
might be running out of memory.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Mark" <one (AT) one (DOT) com> wrote

The quoted memory figures are from Task Manager. I was thinking that
memory usage is an issue. Is there any way of calculating memory
consumption based on dimension data, ie how much memory is used by all
level members in a dimension? Would this be dependent on numeric or
character data? If AS is reaching a memory limit how is this
overcome? AS uses a max of 2GB does it not? In my test set up the
ram is 512MB and vram upto 3GB. Is Windows stalling at vram 3 x ram,
ie 1.5GB?

If these thoughts explain what is happening then what is the best way
to size a server to run AS with contingency?

On Wed, 27 Jul 2005 22:16:25 -0700, "Dave Wickert [MSFT]"
<dwickert (AT) online (DOT) microsoft.com> wrote: