hi,

can i would like to inquire something on MSSQL2005 Processing?

currently our MSSQL2005 Analysis Services is pulling data from UNIX Oracle
(data marts) to process the data.

however, when the process starts, the CPU at UNIX Oracle will hit 100%!!!

as we currently shares the UNIX Oracle Server with other applications, we
will slow down the whole system. This is unacceptable to the user.

is there anyway to restrict the processing not be be so resource intensive?

thanks.

-

Hiong Yee

check the queries SSAS execute against the source.
if the queries executed use too many joins, try to create views in the
database which do these joins at this level using your own optimization
tips. and then use these views instead of the SSAS "auto created" views.
also optimize the source tables by creating good indexes.
SQL Server allow the user to create queries with an option to reduce the
number of CPUs used to execute this query (maxdop), maybe Oracle has the
same option.

From SSAS point of view there is no options to reduce the CPU usage on the
database server.
You have to verify if you can change something in the connectionstring
himself or at the database server level.

Another option is to extract the data from the Oracle database using simple
queries (select * from table) and dump this into another server. the impact
for the users will be smaller then queries with joins, but this required an
extra step in your process.

also, reduce the number of concurrent partitions processed in parallel.

and finally... try to not use the same hardware for your datamarts and
operational databases, the hardware & software requirements of each DB is
completely different.

"leonghio" <leonghio (AT) discussions (DOT) microsoft.com> wrote

You can also limit how many processing threads are happening in parallel on
SSAS. Unconfirmed number is 4 threads per CPU. So if you have 2 CPU machine
that has hyperthreading enabled (4 CPUs visible to OS), then SSAS might
execute up to 16 threads in parallel. That does not mean that all 16 will
submit queries to your relationship database. Some of them will calculate
internal indexes, aggregates, etc. So you can try to limit value to 1, see
impact on your relationship database server, then increase number and etc.

I am using XMLA to start processing. My XMLA is something like that (for max
2 parallel queries):
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel MaxParallel="2">
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Object>
<DatabaseID>MyDBName</DatabaseID>
<CubeID>MyCubeName</CubeID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>

If you are using SQL Management STudio to start processing, you can change
value for this parameter interactivly.

Regards,


"leonghio" <leonghio (AT) discussions (DOT) microsoft.com> wrote