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 with SSAS 2005

hi

i facing problem when i tring to browse cube created in SSAS 2005 from pivot table in FP appear this error

The query could not be processed:

o An error was encountered in the transport layer.

o The peer prematurely closed the connection.

any one have solutions for this problem

thanks

Ensure that you have the Microsoft OLE DB Provider for Analysis Services 9.0 installed, as well as Microsoft Core XML Services 6.0. They can be downloaded here

http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en

Once they are both installed, run regedit and look under HKEY_CLASSES_ROOT/MSOLAP - there should be a key called CLSID which should have the same value as the CLSID under HKEY_CLASSES_ROOT/MSOLAP.3

Now try to connect to the SSAS cube again...some people have found they need to specify their username as <Domain>\<Username> to connect correctly

pivot table

I'm trying to extract some data from an ssas 2005 cube to build a report with ssrs 2005.

The report should have a variable number of columns and a fixed number of rows ... so I think I cannot use a table control but I must use a matrix control ...

So I would group the column for the fiscal month and the row for the measure name or measure caption ... and put the measure value inside the matrix.

Like the following

month 1 month 2 measure 1 xxx xxx measure 2 xxx xxx measure 3 xxx xxx

To do that I should run a query to extract data in the following form ...

fiscal month mesaure name measure value month 1 measure 1 xxx month 1 measure 2 xxx month 1 measure 3 xxx month 2 measure 1 xxx month 2 measure 2 xxx month 2 measure 3 xxx

The problem is ... when running an mdx query on reporting services I need to put the meausure only on the columns ...

so any idea on how can I extract data from ssas in that form ?

Cosimo

Can you place an upper bound on the number of months you need to report? Also, how do you want your months to display from left to right -- most recent to least recent?|||

thank

I solved ... using a static group and a matrix control

Cosimo