"#Value!" error for calculated cells

I have designed an AS 2000 cube with quite complex calculated cells, which work fine in cube browser or with MDX. The moment I use Excel 2007 Pivot table (both in default or compatibility mode), it shows #Value! instead of the calculations. I thought GetPivotData simply returned values from the cube and did not do any calculations of its own... I spent a lot of time to design the cube, but now it cannot be used from Excel, which it needs to be. Any help would be greatly appreciated.

Best regards.

Have you tried creating a copy of your cube, removing all the calculated cells and then connecting to it?

This might help isolate if it is just an Excel connectivity issue or an problem to do with the calculations. Unfortunately you cannot use profiler to trace AS 2000 like you can with AS 2005 otherwise we could trace the MDX that Excel is sending to the server.

|||

Thank you Darren,

It is definitely not an Excel connectivity issue, since everything else shows on the cube. It seems to fall over when it comes across calculated cells which are defined on the cube. And it does seem to be specific to Excel only. I remember the was an issue with pivot tables when the was a limitation on the cell definition MDX length, but it is unlikely to be that (I used the very minimun number of dimensions). Maybe there is known issue with the display of certain calculated cells in Excel ...

|||I think I managed to locat ethe actual problem area - those caclulated cells use a User Defined Function. When I remove the function, the "#Value!" message disappears . It is strange, because the function is not called by excel, it should be resolved on the cube where it works 100%...|||

The problem in fact looks similar to one described in

http://support.microsoft.com/kb/238306

But that was valid for much older versions of AS and was supposed to be fixed a while ago. (I am using up to date version of AS 2000).

|||

it should be resolved on the cube where it works 100%...

I am just wondering if this is not the case. AS2k used to do a fair bit of the query resolution on the client. If you run ProcessMonitor while Excel is trying to execute the query you might be able to see if it is trying to execute the UDF on the client (although it might be hard as ProcessMonitor catches a lot of information)|||Actually an easier way might be to try installing your UDF on a client machine and see if that fixes the problem.|||

I tried that and it di dnot work. One thing I noticed Excel looked in C:\Documents and Settings\f2978326\Application Data\Microsoft\Template for UserDefined.UDF file and UserDefined is the name of the library while UDF is the name of the class that calculations use ..... Does anyone actually have the information on how PivotTable service handles cells which reference UDFs from analysis server? So far I have not been able to get it anywhere...

#temp table vs. @temp table variable in stored proc?

I have a complex stored procedure (it's the strip-packing one, Dan) that
uses several #temp tables along the way. There are typically 1000 to 50000
rows inserted into them during use. At the end of the procedure the final
results end up inserted into permanent tables. Concurrency is not a factor
here because the procedure is typically run only once a month.
My question is, could any performance improvement be gained by changing
them from #temp tables to @.temp table variables?http://www.aspfaq.com/2475
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Ross Presser" <rpresser@.imtek.com> wrote in message
news:jy8ydt3440yb$.dlg@.rpresser.invalid...
> I have a complex stored procedure (it's the strip-packing one, Dan) that
> uses several #temp tables along the way. There are typically 1000 to
50000
> rows inserted into them during use. At the end of the procedure the final
> results end up inserted into permanent tables. Concurrency is not a
factor
> here because the procedure is typically run only once a month.
> My question is, could any performance improvement be gained by changing
> them from #temp tables to @.temp table variables?|||Ross,
It is hard to tell you without knowing what kind of operations are you doing
with the temporary tables. SQL Server does not create statistics for table
variables, so for small number of rows this is a good option. The best way o
f
knowing this, is giving it a try and comparing performance results.
INF: Frequently Asked Questions - SQL Server 2000 - Table Variables
http://support.microsoft.com/defaul...7&Product=sql2k
AMB
"Ross Presser" wrote:

> I have a complex stored procedure (it's the strip-packing one, Dan) that
> uses several #temp tables along the way. There are typically 1000 to 5000
0
> rows inserted into them during use. At the end of the procedure the final
> results end up inserted into permanent tables. Concurrency is not a facto
r
> here because the procedure is typically run only once a month.
> My question is, could any performance improvement be gained by changing
> them from #temp tables to @.temp table variables?
>|||Thank you both (Aaron, Alejandro) for the links. Given the large amount of
data, I think I'll stick with my #temp tables in this instance.|||yes, My experience is that with Small rowsets, the Table Variables are the
way to go.
TempTables, you can add indexes to, etc.
For large Rowsets, Temp Tables performed better in our situations.
Greg Jackson
Portland, OR