Hi all

I have a DTS package that manages my overall ETL process. Within this package, I fire another package that processes my dimensions, then processes all of my cubes. I have workflow steps setup so that the dimension step has to succeed before the cubes get reprocessed, but all of the cubes can run simultaneously. In reality, it seems that only one cube gets processed at a time. The DTS package step within the master package is set to run on the main package thread. I start the overall process using a SQL Agent job. The DTS packages are set to log to the msdb database, and the Agent job is logging to a file.

My problem is that when the SQL Agent runs the processing job, it hangs on one of the process cube steps. In the Package Log, it shows the step run status as 0 (zero) with no details. The SQL Agent log does not show an error. In fact, the last line of the log it typically truncated and refers to an earlier step than that which I know was last completed. During this time, the Analysis Server is hung, and no connections are allowed. The only way to free the OLAP service is to restart it

When I run the steps manually, or even run the child package that process the OLAP objects, it works fine. It seems that it is only when the full process is started by SQL Agent that it gets hung.

My frustrations are that it used to work fine a couple of weeks ago and that I have not been able to discover a way to find out what is causing the process to hang. There are no error messages being written to any of the log files

If anyone could provide a suggestion as to what is causing this or how to diagnose it, I would be very appreciative

Thanks
Asa Monsey

The DTS OLAP Processing task is a VB component written which uses
single-appartment model threading. This means that only one can be
in-progress at a time even through your workflow makes it looks like they
should be happening in parallel. To truly run it in parallel you have to
place it in a different package and invoke it via a Process Task and issue a
DTSRUN of the package from the command-line. Books-On-Line will explain how
to run a package from the command-line. When you do it this way, an entirely
new process is started. By executing it as a sub-package, the threading is
done in-process in the master package's thread pool -- and thus you run into
issues if one of the components are single-appartment model threaded (like
all VB components are).
Hope this helps.
--
Dave Wickert [MS]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Practices Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Asa Monsey" <Asa (AT) NoMoreViri (DOT) com> wrote

all of my cubes. I have workflow steps setup so that the dimension step has
to succeed before the cubes get reprocessed, but all of the cubes can run
simultaneously. In reality, it seems that only one cube gets processed at a
time. The DTS package step within the master package is set to run on the
main package thread. I start the overall process using a SQL Agent job.
The DTS packages are set to log to the msdb database, and the Agent job is
logging to a file.
status as 0 (zero) with no details. The SQL Agent log does not show an
error. In fact, the last line of the log it typically truncated and refers
to an earlier step than that which I know was last completed. During this
time, the Analysis Server is hung, and no connections are allowed. The only
way to free the OLAP service is to restart it.
process is started by SQL Agent that it gets hung.
process to hang. There are no error messages being written to any of the
log files.

Dave,

Actually, My problem was that the process was not ending, and there was no error or status to indicate why.

Through trial and error and a lot more web searching, I found that I had a private dimension that contained more than 2 million members. In fact, it had one member for every row of the fact table. The strange thing was that occasionally the cube would process, and that was throwing me off. Analysis Services finally gave up the ghost and started giving me the member with key '' could not be found error. This led me to research on the web that made me conclude that the dimension was to large and was causing memory errors (we are running Win2K server with 3GB of memory).

I have since made the dimension shared, which causes it to be processed separate from the cube, and simplified it to 1.2 million members at the cost of some precision in selecting query criteria, which can be made up by being more precise in selecting related dimensions

Your post does answer a question I had about the difference between DTS and the ParallelProcess tool you released in the newsgroup last year. Would I be correct to assume that I could install that tool on the server and run it from DTS to process more than one cube in parallel

Thanks
Asa Monse

----- Dave Wickert [MSFT] wrote: ----

The DTS OLAP Processing task is a VB component written which use
single-appartment model threading. This means that only one can b
in-progress at a time even through your workflow makes it looks like the
should be happening in parallel. To truly run it in parallel you have t
place it in a different package and invoke it via a Process Task and issue
DTSRUN of the package from the command-line. Books-On-Line will explain ho
to run a package from the command-line. When you do it this way, an entirel
new process is started. By executing it as a sub-package, the threading i
done in-process in the master package's thread pool -- and thus you run int
issues if one of the components are single-appartment model threaded (lik
all VB components are)
Hope this helps
--
Dave Wickert [MS
dwickert (AT) online (DOT) microsoft.co
Program Manage
BI Practices Tea
SQL BI Product Unit (Analysis Services
-
This posting is provided "AS IS" with no warranties, and confers no rights


"Asa Monsey" <Asa (AT) NoMoreViri (DOT) com> wrote in messag
news:78DE41D0-38D6-45C7-9BA6-A65952B3BBFF (AT) microsoft (DOT) com..
all of my cubes. I have workflow steps setup so that the dimension step ha
to succeed before the cubes get reprocessed, but all of the cubes can ru
simultaneously. In reality, it seems that only one cube gets processed at
time. The DTS package step within the master package is set to run on th
main package thread. I start the overall process using a SQL Agent job
The DTS packages are set to log to the msdb database, and the Agent job i
logging to a file
status as 0 (zero) with no details. The SQL Agent log does not show a
error. In fact, the last line of the log it typically truncated and refer
to an earlier step than that which I know was last completed. During thi
time, the Analysis Server is hung, and no connections are allowed. The onl
way to free the OLAP service is to restart it
process is started by SQL Agent that it gets hung
process to hang. There are no error messages being written to any of the
log files.

Yes. It has a command-line interface -- so you would use a DTS Process Task
to start it working.
--
Dave Wickert [MS]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Practices Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Asa Monsey" <Asa (AT) NoMoreViri (DOT) com> wrote

had one member for every row of the fact table. The strange thing was that
occasionally the cube would process, and that was throwing me off. Analysis
Services finally gave up the ghost and started giving me the member with key
'' could not be found error. This led me to research on the web that made
me conclude that the dimension was to large and was causing memory errors
(we are running Win2K server with 3GB of memory).
of some precision in selecting query criteria, which can be made up by being
more precise in selecting related dimensions.
I be correct to assume that I could install that tool on the server and run
it from DTS to process more than one cube in parallel?

Two comments from experience:
1) scheduled DTS packages run under the SQLAgent's credentials, so you
need to change the account it runs under to an id that is in the
OLAPAdministrators group on the OLAP server. If its the same box it should
be easy; otherwise you'll need a domain id with adequate privledge. When
run interactively, the DTS package runs with YOUR credentials.
2) Your private dimension sounds like it's exceeding the transaction
rollback limit. See if you can consistently process just that cube *by
itself* (single transaction) and run the other cubes separately. Even then
you may need to increase the rollback space on the database (this assumes
you've migrated the repository to SQLServer. If not, it's still in
ACCESS!!). So you may end up with separate DTS packages to get everything
processed (e.g. all shared dimensions first, then the problem cube, then
other cubes, finally virtual cubes.). If all else fails, consider promoting
the private dimension to a shared dimension so it can be processed
independant of everything else.

GW

"Dave Wickert [MSFT]" <dwickert (AT) online (DOT) microsoft.com> wrote