Operation Timed Out

Hi All,

I am getting below error when I am selecting all members of the dimension. It is the largest dimension among others. My query is very simple. An example is given below :

With Member [MyDimension].[SelectedDimension] as 'AGGREGATE({[MyDimension].[Dimension].MEMBERS })'
Select NON EMPTY ( {[MEASURES].[Sales Value]} ) ON COLUMNS ,
NON EMPTY { Distinct( { [Customer].[Customer].Members
From myCube
Where ([MyDimension].[SelectedDimension])

This query is throwing below exceptions:

Source: Microsoft? OLE DB Provider for Analysis Services
Message: The operation requested failed due to timeout
Stack Trace:
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForMultpleResults(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.ExecuteReader()

1. What will be the reason for this exception?
2. This is not connection time out issue. I wanted confirm that since there is lot of data being fetched from olap database the system is not able to complete the operation. Hence the exception thrown.
3. This is dynamic query. Meaning query will be generated at runtime with selected measures and dimension and applying filters selected. Is there any other way of writing query so that performance is improved?

First, if you remove () from NON EMPTY ( {[MEASURES].[Sales Value]} ) . NON EMPTY is not a function, by definition.

Next, what are you trying to achieve by this, With Member [MyDimension].[SelectedDimension] as 'AGGREGATE({[MyDimension].[Dimension].MEMBERS })?

In SSAS 2005 you will have to point to [dimension].[hierarchy] not [dimension].[dimension]

With hierarchy i mean user hierarchies that you have defined.

Are you using SSAS 2000 or 2005?

Regards

Thomas Ivarsson

|||

Thanks for your reply Thomas.

I am using SSAS 2000.

With Member [MyDimension].[SelectedDimension] as 'AGGREGATE({[MyDimension].[Dimension].MEMBERS }) is used to make set of tuples to use it in Where clause.

I have web application to show reports. User will select dimensions he/she wants in report and applies filters on them.

Therefore Aggregate is used for filter purposes.

But when user selects dimension Customer (which is having huge records) the above mentioned exception is thrown. Even if filters are applied on it the excpetions occurs.

I wanted to know under what conditions the exception occurs.

Will it occurs if OLAP database is taking too much time to return all records for the selected dimension?

Regards,

Raghu Deshpande