TM1 Cube Viewer will not slice into Excel

Ideas and tips for enhancing your TM1 application
Post Reply
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:

TM1 Cube Viewer will not slice into Excel

Post by Alan Kirk »

Symptom
You create a view in Server Explorer's Cube Viewer and attempt to slice that view into Excel. Excel will not create a new worksheet for the slice, nor will it receive any data. Typically you will get an hourglass (or equivalent) cursor icon, and the Cube Viewer window's title bar will often display "Not Responding".

To put it another way (for the search engine's benefit), you "can't slice into Excel".

Cause
Excel has been set to ignore other applications for dynamic data exchange.

Remedy
Save whatever you can in Excel since you'll need to kill that session via Task Manager, or by accepting a Windows dialog asking you whether you want to end the session because it's not responding.

As soon as you restart Excel, follow the menu path shown below:
Excel 97 to 2003: Tools -> Options... -> General tab, UNCHECK the "Ignore other applications" box.
Excel 2007: Click on the Office Button (a.k.a. the doofusy looking round button at the top left of the screen), then select the [Excel Options] button at the bottom right of the resulting dialog. Select the Advanced grouping, and find the General heading (which is the third heading from the bottom). UNCHECK the "Ignore other applications that use Dynamic Data Exchange (DDE)" box.

Pathology
Ignore Other Applications is used when an Excel session is being manipulated (often while hidden) by automation code; that is, where VB or VBA is running the session in the background and outside of direct user control, typically to process data.

A problem that developers face is that actions by other applications which call on an Excel session can interfere with their automated session. This includes actions from Windows itself via the user interface. For example if automation code is running an Excel session, and a user double clicks on an icon which would normally launch Excel, that icon will usually expose the hidden session that the VB/VBA code is using.

The solution to this is for the automation session to have Ignore Other Applications set to True via the Application.IgnoreRemoteRequests property. In that way any other application (including Windows) which requires a session of Excel will create a new one and leave the automated session alone.

Unfortunately if the automated session fails to clean up after itself, or is somehow killed before it can reset the Application.IgnoreRemoteRequests property to False, the next NON-Automated session of Excel will retain the setting. That means that when Server Explorer / Cube Viewer attempts to call upon the related Excel session to create a new sheet for a slice, it's unable to do so; Excel will simply ignore the call, leaving Cube Viewer hanging.

Original Thread
http://forums.olapforums.com/viewtopic.php?f=3&t=1169
"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.
Post Reply