Sql Server 2000 and Excel OLAP question 1

[Rob999]

Hi everyone! I tried this in the MS Office forum, but it is OLAP specific, so here's my (IT related) problem:

I'm importing data from a SQL Server 2000 database (an OLAP cube file, specifically) into Excel 2000 that was created using the 'Get External Data' / 'New Database Query' selection off of the 'Data' menu heading. Excel 2k has 65,536 rows available, but my Fact Table - which has 15,954 records - is producing the following dialog box error message when I attempt to drill down in the resultant Pivot Table to the unique account number:

"Microsoft Excel cannot make this change because there are too many row or column items."

It concludes with "Drag at least one row or column field off the PivotTable or to the Page position. Alternatively, right click a field, and then click Hide or Hide Levels on the shortcut menu." This is just telling me to reduce the returned recordset - but I should have about 50,000 rows to spare!!! In fact, since the resultset should only have about 8,000 records - it shouldn't be even that close.

I've also tried using the Tables in SQL Server 2000 as only the Data source and using Microsoft Query to make the connection and then construct an Excel Cube file - but with the same result. I've drastically curtailed the number of records (and this works correctly with the same drilldown on smaller datasets), but that will negate the purpose of the application.

I've looked in Books on Line and the MSDN Website - in addition to 2 of my books ("Unlocking OLAP with Microsoft SQL Server" - pretty good intro; and "The Definitive Guide to Excel VBA" - EXCELLENT), but to no avail. Apparently they work with much smaller datasets, or aren't giving up the 'secret'. Am I missing something?

I'd sure appreciate any help or suggestions. Thanks in Advance!

 

[Rob999]

Thanks everyone, Xlhelp was able to solve this for me over on the Office Forum. Turns out that the Excel Pivot Table has some severe limitations with regard to the amount of data that can be presented - including, in my case, a limit of 8,000 distinct values in a column.

 

[mshen703]

Rob999, I am also experiencing Excel limitation. I like to know mre about it. Could you share your findings?

 

[Rob999]

Sure, though all of this is Pivot Table specific. Here's the link:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q211517

I am going to use Excel 2002, which goes up to - with certain limitations, as explained on the microsoft website - 32,500 rows of unique values, versus 8,000 for Excel 2000. Undoubtedly some users will try to view a million records at one time - but I will explain the scope of the limitation to them up front. And I'm not sure they need a report listing over 32,500 customers anyways. Our president and coo is who ordered this app, and he's far more interested in totals and trends than seeing specific names and addresses.

And once again, this info came from xlhelp - or I'd probably still be trying to figure it out.

 

[padelg]

This can help you anyway when you are using large dimensions with more than 64k members.
You can make a hierarchy over the same 'id'.
Refer to the Grouping property in Analysis Services, that should be automatic or refer to the book OLAP Train Reed Jacobson page 110.

 

[Rob999]

Thanks padelg,
I don't have that particular book, so I'll swing by and check out your cited reference. The book seemed very similar to the intro type reference I picked up already (Unlocking OLAP w/Excel 2000 - which was more specific to my front end), but if it's got other stuff I'll certainly pick it up.

 

[skrishnaP]

I also have the same problem. But in fact a more severe case. Iam not even able to add the dimension itself. However iam able to add it in the page fields list...
Any suggestions on weather i should continue to use Excel or look for some other alternatives ?

 

[Bygbobbo]

I have run into the 64k problem also with unique IDs. Im not sure about your data integrity, but what I have done to make this work is categorize these IDs on a higher level using a view (i.e. active IDS and deactive IDs). I refer to the view in my cube when I create my dimension and now it lets me process the information. Another alternative is to partition the IDs in the fact table according to some other grouping factor..

Let me know if you come up with an alternate solution.

Bygs

 

[drkrell]

What can you do about it?

(1) You can group the dimension - see your help as padelg mentioned, in my case I couldn't do this. As we wanted to analyze the Top 100 etc
(2) Office Web Components sorts this problem out. This overcomes the limit problem. Although its not particularly nice to use. I have deployed the spreadsheet onto an interactive web page very simply. You can use Excel or FrontPage to do this. The good thing is when you make a change to the cube you won't have to redistribute a spreadsheet just amend one page.
(3) Upgrade to Office XP. This problem does not exist with XP. You get more functionality for Pivot Tables in XP and with the Web Components XP office comes with.
(4) Look at a 3rd party solution. I found ProClarity wasn't very user friendly. ChartFX for OLAP is new and looks excellent. Don't think it can cope with Big Dimensions at the moment though.
(5) Deploy the spreadsheet and a Web Page based on Office Web Components. The users can look at the larger dimensions in the web page and the smaller ones in Excel.

I encountered this problem last week and it is hideous. If anybody knows any more about this or has anymore solutions please let me know.