Excel 2003 and nonemptycrossjoin

I am trying to utilize an Excel 2003 pivot table as a client tool to front end a project I am developing. I have created the cube in Analysis Services 2005 and it works fine when I run MDX against it directly. The problem that I am having is that Excel is not using nonemptycrossjoin in the pivot table. I have Teo Lachev's book "Applied Microsoft Analysis Services 2005' and I was looking for tips and found a note on p. 633 referring to a workaround (http://blogs.msdn.com/bi_systems/articles/162852.aspx).

To simplify testing, I reduced the query down to the most essential dimensions on rows and a single non-calculated measure on columns. The below query takes 1 second from SQL Server Management Studio.

select
nonemptycrossjoin([Product Family].[Product Families].[Product Brand].&[Family1],
[Shift Calendar].[Shift Fiscal].[Fiscal Year].&[2.006E3].&[2006-3].&[2006-08].&[2006-33],
[Failure Root Cause].[FA Failure Root Causes].[FA Pass Fail Indicator].&[Fail].&[Failure1],
[Comp1 Supplier].[Comp1 Suppliers].[Comp1 Supplier Description],
[Comp2 Supplier].[Comp2 Suppliers].[Comp2 Supplier Description]) on rows
{[Measures].[Sum Failed Qty]} on columns
from
[MyCube]

If I remove the nonempycrossjoin, keeping the () on rows to get the implicit crossjoin, the query runs but takes 114s. Now, if I start creating a similar query in Excel, the first few dimensions are pretty fast, but adding the last dimension on rows takes so long that my machine consumes all of its memory (2 GB) and CPU. I have to kill Excel to get my machine back.

So, Excel 2003 does not provide a facility to alter the Data Source, so after a bit of looking around I found the data source in "C:\Documents and Settings\username\Application Data\Microsoft\Queries". Opening my file test.oqy:

QueryType=OLEDB
Version=1
CommandType=Cube
Connection=Provider=MSOLAP.3;Cache Authentication=False;User ID="";Initial Catalog=Analysis Services Test Project;Data Source=localhost;Impersonation Level=Impersonate;Location=localhost;Mode=ReadWrite;Protection Level=Pkt Privacy;Auto Synch Period=20000;Default Isolation Mode=0;Default MDX Visual Mode=0;MDX Compatibility=0;MDX Unique Name Style=0;Non Empty Threshold=0;SQLQueryMode=Calculated;Safety Options=1;Secured Cell Value=0;SQL Compatibility=0;Compression Level=0;Real Time Olap=False;Packet Size=4096
CommandText=AS Test Cube

According to the workaround above, this query should use the "new" method to eliminate empty rows since: 1) Non Empty Threshold is set lower than the number of tuples returned. and 2) there are no calculated measures in the query. Meeting both these criteria should clear the way for the "new" method to be used. Does anyone have any other ideas that I could employ to make this pivot table any faster? Any other tools that would do this with nonemptycrossjoin? I need slice/dice capabilities which is preventing my from using Reporting Services.

Thanks,

Keehan

Hi Keehan,

A question I would have, though, is whether the "Non Empty Threshold" connection property works in the same way in AS 2005 (or is just included for backward compatibility). Unfortunately, I can't locate this info in BOL; but Appendix B of the "MDX Solutions" 2nd Edition covers connection parameters. Hopefully, someone from [MS] will be able to authoritatively answer this?

In any case, I assume that the number of rows returned in your scenario easily exceeds the default threshold. If so, is there any possibility of restructuring the dimensions to exploit the AS 2005 Auto Exists feature - such as folding some separate dimensions into hierarchies of a single dimension?

http://www.wiley.com/WileyCDA/WileyTitle/productCd-0471748080.html

>>

Appendix B: Connection Parameters That Affect MDX.

>>

|||

Deepak,

I kind of see where you are going with auto-exists but I'm not sure how it would be implemented. So, basically, for auto-exists to kick in, the attributes would have to be in the same dimension. So, how would I have say product and calendar in the same dimension. Is this the kind of work around you are thinking about?

Keehan

|||

I think I may know what you are talking about now. Let me dig into this a little more and I'll let you know how it goes.

Cheers