Pivottable 10/11 with SSAS 2005 cube 'hang' when using filter fields

Dear all,

I am running into some difficulties with an SSAS 2005 SP2 cube in combination with an OWC 11 pivottable client. Everything basically works fine.

The problem occurs when I make a selection with one or more dimensions in the 'filter' section.

For instance, if I build a pivottable which shows:

YEAR in the Colums

PRODUCT GROUP in the ROWS

CUSTOMER NAME in the ROWS

PRODUCT FAMILY in FILTER SECTION

SALES as Measure

As soon as I make a selection on YEAR (show only 2007) and PRODUCT FAMILY (show only 'bikes' and 'minibikes') , the pivottable basically hangs. On the server the memory usage of SSAS shoots up to 1.8(!) GB

After +/- 60 seconds I get a blank screen and sometimes: 'Cannot display data because of structural changes in the database'.

I tried to make the same selecion in my Excel2007 eval, which worked fine. Pivottable 10 gives the same error. The error also occurs in Visual Studio (off course also OWC11).

Altering the cube's storage settings did not help.

My cube's main facttable containts approx 2.5 mln records.

Is this a known problem, what can I do to fix it?

Many many thanks

Rex

Dear all,

After doing some research it seems that SP2 is causing the errors. When downgrading my SSAS 2005 to SP1 all problems vanished. Only problem now is the performance compared to SP2 which is significantly lower.

To really make sure it was SP2 I installed a fresh SSAS2005 machine without any servicepacks and built my cube, after intensive testing no problems.

Upgraded to SP1, no problems.

Upgrade to SP2, massive memory usage of the SSAS service and total lockup.

I also installed the cumulative 3161 post SP2 update, but this did not fix the issue.

The problem only occurs when filtering data in the pivottable's 'filter' section.

Any ideas?

|||

Problem solved!!

After installing the Cumulative Hoftfixes 2 for SP2 (build 3175), released Jun 22nd the problem has disappeared and the performance of my cubes is great!

Link to the hotfix: http://support.microsoft.com/kb/936305

Pivot table Conditional calculation field

Hello All

I am using owc pivot table as a control to display the data in cubes.

Now i have a requirement to apply the formula (calculated field) for all records except few.

How to solve this scenario.

Please Help

Thanks

I have written this query and this query returns me the same results as i expected except the Grand Total at bottom which show the same value as of old grand total.

"WITH MEMBER [Measures].[Q1] AS '[Measures].[BY] * IIf([Center].CurrentMember.Name = ""Apple Research Center"", 1.2, 1) ' SELECT {[Measures].[Q1],[Measures].[Prior],[Measures].[CY]} ON COLUMNS, [Center].[Center].Members ON ROWS FROM Demo"

Please Help

Anil

|||

I am still in need of a solution for this question

please help!!!

|||Does any body come accross this situation?|||

Hi Anil,

Do you mean, "all records", or "all members"? If it's the latter, then an MDX formula is best, using the IF function. If it's the former, then use a function in the fact table.

Could you tell me the structure of your cube, in terms of measures and dimensions, and then maybe I can help you with a MDX formula.

Cheers,

Jerome

|||

Jerome,

Thanks for your response.

I meant all records.

I am new to this analysis service.

Could you please help me out.

Thanks

|||

Hi Anil,

Sorry I took so long. If you need to apply a function to some of the records of the fact table, you can create a function on the SQL Server which applies the formula according to a condition:

CREATE FUNCTION <Function Name>

RETURNS <Data Type> AS

IF <Condition>

RETURN <X>

ELSE

RETURN <Y>

Then use this function in a view based on the fact table, and either use the view to populate a new version of the fact table, or else base the cube on the view.

I hope this helps. Now, I'll be able to help you better if you give me more specific information:

1. Structure of the Fact Table

2. Measures of the Cube

3. Dimensions

4. Condition for which you want to display rows from the fact table.

I look forward to hearing from you.

Cheers,

Jerome

|||

Jerome,

Thank you for your response.

I implemented this but the total is displaying the old value only.

Pivot table Conditional calculation field

Hello All

I am using owc pivot table as a control to display the data in cubes.

Now i have a requirement to apply the formula (calculated field) for all records except few.

How to solve this scenario.

Please Help

Thanks

I have written this query and this query returns me the same results as i expected except the Grand Total at bottom which show the same value as of old grand total.

"WITH MEMBER [Measures].[Q1] AS '[Measures].[BY] * IIf([Center].CurrentMember.Name = ""Apple Research Center"", 1.2, 1) ' SELECT {[Measures].[Q1],[Measures].[Prior],[Measures].[CY]} ON COLUMNS, [Center].[Center].Members ON ROWS FROM Demo"

Please Help

Anil

|||

I am still in need of a solution for this question

please help!!!

|||Does any body come accross this situation?|||

Hi Anil,

Do you mean, "all records", or "all members"? If it's the latter, then an MDX formula is best, using the IF function. If it's the former, then use a function in the fact table.

Could you tell me the structure of your cube, in terms of measures and dimensions, and then maybe I can help you with a MDX formula.

Cheers,

Jerome

|||

Jerome,

Thanks for your response.

I meant all records.

I am new to this analysis service.

Could you please help me out.

Thanks

|||

Hi Anil,

Sorry I took so long. If you need to apply a function to some of the records of the fact table, you can create a function on the SQL Server which applies the formula according to a condition:

CREATE FUNCTION <Function Name>

RETURNS <Data Type> AS

IF <Condition>

RETURN <X>

ELSE

RETURN <Y>

Then use this function in a view based on the fact table, and either use the view to populate a new version of the fact table, or else base the cube on the view.

I hope this helps. Now, I'll be able to help you better if you give me more specific information:

1. Structure of the Fact Table

2. Measures of the Cube

3. Dimensions

4. Condition for which you want to display rows from the fact table.

I look forward to hearing from you.

Cheers,

Jerome

|||

Jerome,

Thank you for your response.

I implemented this but the total is displaying the old value only.