Analysis services connection

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Hoody - 07 Nov 2006 12:26 GMT

Hi!!

1. I am working with Analysis services 2005 and I would want to know
if, Is it recommendable to separate the DB server from of server AS?,
Since when I consult the cubes diminishes the performance the server of
database.

2. How I can to create news administrators OLAP in SQL 2005?

Is there one book what you can recommend on how connecting to me of
remote form?

Thanks,
Cecilia
Chile

Reply to this Message

Darren Gosbell - 09 Nov 2006 01:34 GMT

1. Yes, you can run SQL and AS on separate machines, just be aware that
each machine would require its own SQL license.

2. To create add a user to the administration group at the server level
for AS you need to open up SQL Server Management Studio, connect to your
server, right click and select "properties" and then go to the security
settings and add the user to the list there.

--
Regards
Darren Gosbell [MVP]
Blog: http://geekswithblogs.net/darrengosbell

Reply to this Message

Hoody - 09 Nov 2006 15:12 GMT

The connection of server work, thanks Darren.

Now I have other doubts:

1. if I have SQL server and AS on separate machines and I execute a MDX
query for a rolap cube, consume this query too many resources on the BD
server?

2. it really improves the performance of my server, if i separate it in
the 2 mentioned machines???

3. What percent of the load of a query takes the BD machine, (aprox)???

Thanks,
Cecilia

Reply to this Message

Darren Gosbell - 14 Nov 2006 10:14 GMT

> The connection of server work, thanks Darren.
>
[quoted text clipped - 3 lines]
> query for a rolap cube, consume this query too many resources on the BD
> server?

Sorry, but ROLAP cubes are the one exception. In the case of ROLAP, the
AS server is really just a thin semantic layer that translates MDX into
SQL and runs some pretty large queries against the DB server

> 2. it really improves the performance of my server, if i separate it in
> the 2 mentioned machines???

This really only makes a difference when AS is using MOLAP storage as
then it has its own copy of the data and may not need to query the DB
server at all after it has gone through it's processing stage

> 3. What percent of the load of a query takes the BD machine, (aprox)???

For ROLAP probably 99%
For MOLAP 0%
(after the data has been read once during the processing stage)

I assume that you are using ROLAP because you need "real-time" data. I
don't know what your exact requirements are, or if you have looked into
any of this before, but AS has a number of ways of implementing "near
real-time" systems. So that if it works with your data and your users
can accept some degree of "lag" in the update of their reports you
should be able to get an increase in the performance of your MDX queries
and reduce the load on your DB server.

If you are interested in this there are a couple of different features
you could read up on.

1) proactive caching - can automatically keep your MOLAP storage up to
date, based either on notifications or polling the source system.

2) You can use incremental processing and schedule a job to run on a
periodic basis to refresh the MOLAP store.

These options do not fit all circumstances and sometimes you may need to
use ROLAP storage, but if you can avoid ROLAP you will probably have a
much better experience.

Signature

Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

Reply to this Message