Excel crashing - save worksheet slice

Post Reply
shinymcshires
Posts: 58
Joined: Wed Nov 26, 2008 10:21 pm
OLAP Product: OlapObjects Publisher 5.0
Version: 9.5.1
Excel Version: 2003

Excel crashing - save worksheet slice

Post by shinymcshires »

I just started to get this error- haven't seen this before today. I'm simply trying to slice data from our Finance cube into Excel and save it. I get all the way up to the point of the name and location of the file, click on save, and Excel crashes. Here is the cube view that I am trying to slice:
excelcrashcubeview.JPG
excelcrashcubeview.JPG (97.17 KiB) Viewed 5106 times
Here is the Save As window:
ExcelSaveAs.JPG
ExcelSaveAs.JPG (34.67 KiB) Viewed 5105 times
This is the error message that is displayed:
excelcrash.JPG
excelcrash.JPG (51.04 KiB) Viewed 5105 times
Has anybody else had a problem like this? I've already left a message with my VAR, but haven't heard back from them yet. Thanks for your help!
Richard Lee
Financial Systems Analyst
City of Millbrae
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Excel crashing - save worksheet slice

Post by Alan Kirk »

I'm assuming that you've already tried shutting down and restarting Excel?

I doubt that it has much to do with the fact that it's a TM1 slice; at that point you're saving an Excel workbook which just happens to have TM1 formulas in it. (I'm also assuming that you have your options set to manual calculation, and do NOT calculate before save. If you do, you've taken TM1 entirely out of the equation.)

This is a long shot, but about a billion years ago when I was a regular in the Excel Usenet forums we found that Excel would often misbehave and crash if your Temp directory was over-cluttered with junk. Try the following:
- Open Windows Explorer;
- In the address bar, type %Temp% to take you to your own Temp folder;
- Clear out as much junk as you can (you won't be able to delete everything since some of the files will still be in use);
- Try saving a slice again and see whether that makes a difference.

As I said, a bit of a long shot but it can't do any harm.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
shinymcshires
Posts: 58
Joined: Wed Nov 26, 2008 10:21 pm
OLAP Product: OlapObjects Publisher 5.0
Version: 9.5.1
Excel Version: 2003

Re: Excel crashing - save worksheet slice

Post by shinymcshires »

Alan-

Thanks for the suggestions. I changed the calculation setting to manual, and unchecked "Recalculate before Save". I also deleted as many temp files as I could. No dice. Funny enough, when I snapshot the data out, instead of slicing it, the worksheet will save without crashing Excel. I also tried copy-paste special-values for all of the TM1 fomulas (DBRW & SUBNM), but that didn't help. Next step is to wait for my VAR and open up a trouble ticket with IBM/Cognos.
Richard Lee
Financial Systems Analyst
City of Millbrae
shinymcshires
Posts: 58
Joined: Wed Nov 26, 2008 10:21 pm
OLAP Product: OlapObjects Publisher 5.0
Version: 9.5.1
Excel Version: 2003

Re: Excel crashing - save worksheet slice

Post by shinymcshires »

Worthy of note: This seems to only occur with one cube. I tried the same with another cube, and the file saved fine without crashing Excel. Maybe the .cub file has been corrupted somehow? My VAR is going to open up a trouble ticket- we'll see what happens. If the .cub file has been corrupted, I wonder if the problem would be fixed if I used a backup copy...
Richard Lee
Financial Systems Analyst
City of Millbrae
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Excel crashing - save worksheet slice

Post by Steve Rowe »

Just noticed that you have a program name of "0000" in your dimension. It's possible that excel interprets this as a blank cell at some point and then the slice fails. Do you get the same results with this cube with a different project number?

In general terms its bad practice to have numeric only element names since Excel can sometomes decode to convert them into dates and so on.

Just a thoguht!
Cheers
Technical Director
www.infocat.co.uk
User avatar
Steve Vincent
Site Admin
Posts: 1054
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Re: Excel crashing - save worksheet slice

Post by Steve Vincent »

Hmm might be right there. TM1 would see that (if it was a data point) as empty. Excel would always see that as a blank cell. If the slice is trying to calculate values in Excel based on an "empty" cell as an element reference there is more than a slight chance it would get its knickers in a knot and die horribly. Steve's suggestion will prove or disprove that, try the same slice but without that element in and see what happens. If its fine then you'll have to get your users to agree to a different element naming convention :)
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
User avatar
mattgoff
MVP
Posts: 516
Joined: Fri May 16, 2008 1:37 pm
OLAP Product: TM1
Version: 10.2.2.6
Excel Version: O365
Location: Florida, USA

Re: Excel crashing - save worksheet slice

Post by mattgoff »

Sometimes the naming convention can't be changed. A bunch of our elements come from Oracle GL and are numeric-only. Every dimension has an all-zeros element, and all elements are zero-padded too. I've found that it's best to plan for formatting issues and now build TEXT() into the DBRWs for "real" templates that I send around.

That being said, those have never caused crashes, and I'd be surprised if a corrupted cube, server-side, is crashing the client. I've been able to solve most Excel crash issues by installing all of the Excel patches at office.microsoft.com. After that, as Alan recommended, ensuring that automatic calculation disabled helps a lot too.

Matt
shinymcshires
Posts: 58
Joined: Wed Nov 26, 2008 10:21 pm
OLAP Product: OlapObjects Publisher 5.0
Version: 9.5.1
Excel Version: 2003

Re: Excel crashing - save worksheet slice

Post by shinymcshires »

It's gone. The IT guy came over and took a peek at the Process Monitor that was filtered just for Excel processes. Seems like the point of crashing was when it was trying to write to 'desktop.ini'. When we tried to locate the file via windows explorer, it was nowhere to be found, even with hidden files and folders being displayed. We searched for the file, and the OS "found" it in the docs&settings directory under my username, but we couldn't visibly see it. He decided to run a chkdsk /f to see if any indexes had been corrupted, and I also remapped the shortcut to tm1p.xla on my desktop, just in case. I also downloaded/installed Office updates (although most of them were generic 'security updates' for Office; only one of them was a specific 'security' update for Excel). Now I can save the worksheet without crashing the app. Thanks for your suggestions and comments!
Richard Lee
Financial Systems Analyst
City of Millbrae
Post Reply