Analysis Services service hangs cube build / timeout on process cu

We have two apparent issues with Analysis Services (AS) as outlined below:
1.We have a rebuild of the nightly cube build and quite often the first
step would hang (just run without processing). We would have to stop the
job, restart Analysis Services and then start the job again, resulting in
loss of processing time and an impact to our Production Reporting. As a
workaround for this intermittent problem, we have adjusted the nightly job to
include a stop and start of AS before the cube build begins. An outline of
the current steps:
?net stop MSSQLServerOLAPService
?net start MSSQLServerOLAPService
?dtexec /DTS "\MSDB\SSAS_Petra_Full_Dim_Load_Prod" /SERVER vbmsp19
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
?process cub files
?backup cube
We need to know why AS does not process cubes as it should from time to
time. Do we have a configuration problem? Is our Production Server not
sufficient enough for AS and the size of the cube? We have more than the
minimum requirements but possibly we need more due to our expectation of the
cubes and data.
Server Specifications -->
Production
CPU = Quad 2.8 GHz Intel Xeon (Hyper-Threaded)
Memory = 20476 MB
OS – Windows 2003. SP1
SQL Server 2005
64 bit based on the x64 platform
2.We have been unable to deploy a copy of the Production cubes to a new
server. We even tried deploying a smaller cube, to include a large
dimension (ex: Account). We continually experience time outs with the larger
dimensions.
a.Tried deploying from Visual Studio 2005
b.Attempted to Process the cube through SQL Server Management Studio
c.Attempted to Process the larger dimension only from SQL Server Management
Studio
d.Attempted Parallel processing, Sequential – One Transaction, and
Sequential – Separate Transaction.
e.Also tried changing the default timeout for the AS server.
ForceCommitTimeout property was 30000, increased to 40000 and then again to
50000
f.Attempted to change the ExternalCommandTimeout property from 3600 to
7200. This was successful in the Deisaster Recovery server which has nothing
running (no users reporting). Although it did take over 5 hours to process.
At that rate, it might take 2 days to rebuild the large cube that we have,
with several partitions.
Disaster Recovery -->
CPU = Dual 3.00 GHz Intel Xeon MP (Hyper-Threaded)
Memory = 9728 MB
OS – Windows 2003 SP 1
SQL Server 2005
32bit
Errors:
OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.
OLE DB error: OLE DB or ODBC error: Query timeout expired; HYT00.
do you have any proactive caching partitions?
I see this error when I'm using the proactive caching feature.
"Shari" <Shari@.discussions.microsoft.com> wrote in message
news:4D39C9AF-58CF-4639-BA8B-DF4E936D62F1@.microsoft.com...
> We have two apparent issues with Analysis Services (AS) as outlined below:
> 1. We have a rebuild of the nightly cube build and quite often the first
> step would hang (just run without processing). We would have to stop the
> job, restart Analysis Services and then start the job again, resulting in
> loss of processing time and an impact to our Production Reporting. As a
> workaround for this intermittent problem, we have adjusted the nightly job
> to
> include a stop and start of AS before the cube build begins. An outline
> of
> the current steps:
> ? net stop MSSQLServerOLAPService
> ? net start MSSQLServerOLAPService
> ? dtexec /DTS "\MSDB\SSAS_Petra_Full_Dim_Load_Prod" /SERVER vbmsp19
> /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
> ? process cub files
> ? backup cube
> We need to know why AS does not process cubes as it should from time to
> time. Do we have a configuration problem? Is our Production Server not
> sufficient enough for AS and the size of the cube? We have more than the
> minimum requirements but possibly we need more due to our expectation of
> the
> cubes and data.
> Server Specifications -->
> Production
> CPU = Quad 2.8 GHz Intel Xeon (Hyper-Threaded)
> Memory = 20476 MB
> OS – Windows 2003. SP1
> SQL Server 2005
> 64 bit based on the x64 platform
>
> 2. We have been unable to deploy a copy of the Production cubes to a new
> server. We even tried deploying a smaller cube, to include a large
> dimension (ex: Account). We continually experience time outs with the
> larger
> dimensions.
> a. Tried deploying from Visual Studio 2005
> b. Attempted to Process the cube through SQL Server Management Studio
> c. Attempted to Process the larger dimension only from SQL Server
> Management
> Studio
> d. Attempted Parallel processing, Sequential – One Transaction, and
> Sequential – Separate Transaction.
> e. Also tried changing the default timeout for the AS server.
> ForceCommitTimeout property was 30000, increased to 40000 and then again
> to
> 50000
> f. Attempted to change the ExternalCommandTimeout property from 3600 to
> 7200. This was successful in the Deisaster Recovery server which has
> nothing
> running (no users reporting). Although it did take over 5 hours to
> process.
> At that rate, it might take 2 days to rebuild the large cube that we
> have,
> with several partitions.
>
> Disaster Recovery -->
> CPU = Dual 3.00 GHz Intel Xeon MP (Hyper-Threaded)
> Memory = 9728 MB
> OS – Windows 2003 SP 1
> SQL Server 2005
> 32bit
> Errors:
> OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.
> OLE DB error: OLE DB or ODBC error: Query timeout expired; HYT00.
>
|||Thanks Jeje for your quick response.
I browsed the setting of the partitions and proactive caching is not
enabled. Any other ideas?
Thanks,
Shari
"Jeje" wrote:
[vbcol=seagreen]
> do you have any proactive caching partitions?
> I see this error when I'm using the proactive caching feature.
>
> "Shari" <Shari@.discussions.microsoft.com> wrote in message
> news:4D39C9AF-58CF-4639-BA8B-DF4E936D62F1@.microsoft.com...

Analysis Services service hangs cube build / timeout on process cu

We have two apparent issues with Analysis Services (AS) as outlined below:
1. We have a rebuild of the nightly cube build and quite often the first
step would hang (just run without processing). We would have to stop the
job, restart Analysis Services and then start the job again, resulting in
loss of processing time and an impact to our Production Reporting. As a
workaround for this intermittent problem, we have adjusted the nightly job t
o
include a stop and start of AS before the cube build begins. An outline of
the current steps:
? net stop MSSQLServerOLAPService
? net start MSSQLServerOLAPService
? dtexec /DTS "\MSDB\SSAS_Petra_Full_Dim_Load_Prod" /SERVER vbmsp19
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
? process cub files
? backup cube
We need to know why AS does not process cubes as it should from time to
time. Do we have a configuration problem? Is our Production Server not
sufficient enough for AS and the size of the cube? We have more than the
minimum requirements but possibly we need more due to our expectation of the
cubes and data.
Server Specifications -->
Production
CPU = Quad 2.8 GHz Intel Xeon (Hyper-Threaded)
Memory = 20476 MB
OS – Windows 2003. SP1
SQL Server 2005
64 bit based on the x64 platform
2. We have been unable to deploy a copy of the Production cubes to a new
server. We even tried deploying a smaller cube, to include a large
dimension (ex: Account). We continually experience time outs with the larger
dimensions.
a. Tried deploying from Visual Studio 2005
b. Attempted to Process the cube through SQL Server Management Studio
c. Attempted to Process the larger dimension only from SQL Server Management
Studio
d. Attempted Parallel processing, Sequential – One Transaction, and
Sequential – Separate Transaction.
e. Also tried changing the default timeout for the AS server.
ForceCommitTimeout property was 30000, increased to 40000 and then again to
50000
f. Attempted to change the ExternalCommandTimeout property from 3600 to
7200. This was successful in the Deisaster Recovery server which has nothin
g
running (no users reporting). Although it did take over 5 hours to process.
At that rate, it might take 2 days to rebuild the large cube that we have,
with several partitions.
Disaster Recovery -->
CPU = Dual 3.00 GHz Intel Xeon MP (Hyper-Threaded)
Memory = 9728 MB
OS – Windows 2003 SP 1
SQL Server 2005
32bit
Errors:
OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.
OLE DB error: OLE DB or ODBC error: Query timeout expired; HYT00.do you have any proactive caching partitions?
I see this error when I'm using the proactive caching feature.
"Shari" <Shari@.discussions.microsoft.com> wrote in message
news:4D39C9AF-58CF-4639-BA8B-DF4E936D62F1@.microsoft.com...
> We have two apparent issues with Analysis Services (AS) as outlined below:
> 1. We have a rebuild of the nightly cube build and quite often the first
> step would hang (just run without processing). We would have to stop the
> job, restart Analysis Services and then start the job again, resulting in
> loss of processing time and an impact to our Production Reporting. As a
> workaround for this intermittent problem, we have adjusted the nightly job
> to
> include a stop and start of AS before the cube build begins. An outline
> of
> the current steps:
> ? net stop MSSQLServerOLAPService
> ? net start MSSQLServerOLAPService
> ? dtexec /DTS "\MSDB\SSAS_Petra_Full_Dim_Load_Prod" /SERVER vbmsp19
> /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
> ? process cub files
> ? backup cube
> We need to know why AS does not process cubes as it should from time to
> time. Do we have a configuration problem? Is our Production Server not
> sufficient enough for AS and the size of the cube? We have more than the
> minimum requirements but possibly we need more due to our expectation of
> the
> cubes and data.
> Server Specifications -->
> Production
> CPU = Quad 2.8 GHz Intel Xeon (Hyper-Threaded)
> Memory = 20476 MB
> OS – Windows 2003. SP1
> SQL Server 2005
> 64 bit based on the x64 platform
>
> 2. We have been unable to deploy a copy of the Production cubes to a new
> server. We even tried deploying a smaller cube, to include a large
> dimension (ex: Account). We continually experience time outs with the
> larger
> dimensions.
> a. Tried deploying from Visual Studio 2005
> b. Attempted to Process the cube through SQL Server Management Studio
> c. Attempted to Process the larger dimension only from SQL Server
> Management
> Studio
> d. Attempted Parallel processing, Sequential – One Transaction, and
> Sequential – Separate Transaction.
> e. Also tried changing the default timeout for the AS server.
> ForceCommitTimeout property was 30000, increased to 40000 and then again
> to
> 50000
> f. Attempted to change the ExternalCommandTimeout property from 3600 to
> 7200. This was successful in the Deisaster Recovery server which has
> nothing
> running (no users reporting). Although it did take over 5 hours to
> process.
> At that rate, it might take 2 days to rebuild the large cube that we
> have,
> with several partitions.
>
> Disaster Recovery -->
> CPU = Dual 3.00 GHz Intel Xeon MP (Hyper-Threaded)
> Memory = 9728 MB
> OS – Windows 2003 SP 1
> SQL Server 2005
> 32bit
> Errors:
> OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.
> OLE DB error: OLE DB or ODBC error: Query timeout expired; HYT00.
>

Analysis services query timeout from SSIS

Hi,

I've just developed a simple integration services package which picks up a list of MDX queries from a table then runs them against our Cube using a .NET OleDb connection and saves the results to a csv file.

Unfortunately the queries frequently timeout, despite the fact that the Timeout property on the Connection Manager is set to 100000 and the Connect Timeout property is set to 600.

I have some logging on the packages and this revels that the queries will often timeout after less than 60 seconds with the error below:
<<Query>> failed with the following error: "XML for Analysis parser: The XML for Analysis request timed out before it was completed.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Looking at the properties of the connection manager, I can see that the ConnectionString which has been generated is:
Data Source=Server;Initial Catalog=CubeName;Provider=MSOLAP.3;Integrated Security=SSPI;Connect Timeout=600;Auto Synch Period=1000;Timeout=100000;

Subsequent re-running of the package will often succeed, I'm guessing this is because the Cube has cached the query result and is able to return it fast enough.

Is there an additional timeout property that I'm missing somewhere?

Your help is much appreciated!

-Stuart

Perhaps try ExternalCommandTimeout? (http://www.microsoft.com/technet/prodtechnol/sql/2005/ssasproperties.mspx)|||Chris Webb built a "cache warming" prototype using SSIS and I think he found a way around this issue, you can find the post on his blog about this at http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!1062.entry|||

Hi,

Sorry for the delay in replying and thanks for your suggestions. It seems that Chris Webb's solution was to ignore the timeout, which was fine given that he was building a cache warmer and didn't actually need to get the results. Given that I did need the results I decided to try and build some retry logic into my SSIS package.

I had a very clear and simple idea in my mind of how this would work. I would simply add an additional flow connector to the Execute SQL task with a Failure & Expression constraint where @.RetryCount < @.MaxRetries, then I would flow into a script task to increment @.RetryCount then back into the same Execute SQL task. Unfortunately though SSIS will not allow this as this would result in looping dependency.

The somewhat contrived solution I have now come up with is to extract my MDX Query list into a .NET Dataset pointed to by an SSIS Object variable, onto which I add an additional column to keep track of the number of retires. I then loop over the dataset and in the event of a query failing, I copy the row in the dataset and add it to the bottom, incrementing RetryCount.

It seems like a crazy solution to the problem of a query timing out. Is anyone at Microsoft aware that the Execute SQL task will timeout after 30 seconds when using an ADO .NET OleDb Connection with the Analysis Services provider, regardless of what you set the timeouts to be?

-Stuart