Excel 2007 hangs after TM1 launch

[Carolyn]

I have TM1 Vers 9.4.1. I am the only user, as I am prepping for the rest of the users. Essentially, I am working in a sandbox.

I launch Excel, which auto-loads the TM1 Add-in. Everything looks great, until I try to do something on the toolbar. If I click "Bold" or anything, Excel freezes and I have to use Task Manager to bail. I can try to launch Excel multiple times, and it will launch successfully on the 8th or 10th try. If successful, it all works great. I have tried launching Excel without the add-in, but cannot.

I have been running long Excel macro processes to load data. My boss wanted those processes to automatically stop before the nightly backup. I wrote a routine in the VBA program to check the time, then stop, save the file and quit the application at 11:45 pm.

My frozen Excel upon launch problem started a few weeks after I added the 11:45 cut off script. I have since removed it, but still have the problem. My intincts tell me some kind of Excel DLL is broken, maybe from a dirty close.

My question is can I use the MS Office repair function to fix Excel, and will that NOT affect all the TM1 cubes? I don't want to lose the existing data in the TM1 cubes.

Does anyone else have a brilliant idea?

[Alan Kirk]

I have TM1 Vers 9.4.1. I am the only user, as I am prepping for the rest of the users. Essentially, I am working in a sandbox.

I launch Excel, which auto-loads the TM1 Add-in. Everything looks great, until I try to do something on the toolbar. If I click "Bold" or anything, Excel freezes and I have to use Task Manager to bail. I can try to launch Excel multiple times, and it will launch successfully on the 8th or 10th try. If successful, it all works great. I have tried launching Excel without the add-in, but cannot.

I have been running long Excel macro processes to load data. My boss wanted those processes to automatically stop before the nightly backup. I wrote a routine in the VBA program to check the time, then stop, save the file and quit the application at 11:45 pm.

You may want to consider using TurboIntegrator for this. Short of hand crafting VBA code which calls the API, TI is going to be hugely faster than any Excel-based solution would be; you may be finished long before 11:45 going that route.

My frozen Excel upon launch problem started a few weeks after I added the 11:45 cut off script. I have since removed it, but still have the problem. My intincts tell me some kind of Excel DLL is broken, maybe from a dirty close.

My question is can I use the MS Office repair function to fix Excel, and will that NOT affect all the TM1 cubes? I don't want to lose the existing data in the TM1 cubes.

It's important to understand that Excel is merely the front end for TM1... or to be more precise I should say a front end, since you can also have Architect, Web, or even a home brewed program written using the TM1 API. Consequently whatever you do to your Excel program - uninstall it and reinstall it, repair it, install a new version - can't and won't affect your cubes. However for safety's sake I would recommend shutting down your TM1 services if they're running on the same box before you make any changes.

Also, depending on what the repair does, you may end up having to reinstall the TM1 Client on your desktop just to ensure that it interacts with Excel correctly.

If the repair doesn't work I'd try uninstalling TM1, uninstalling Excel and then reinstalling in reverse order. Just copy your server data directory to a safe location before you do and you won't have to worry about losing any data.

[Carolyn]

Alan,

I actually found the problem, and it was very simple. There is a bug in the tm1 ribbon addon in Excel.

I went to c:\program files\Cognos\TM1\bin\tm1pribbonx and changed the name to tm1pribbonxx.

That fixed it, except the TM1 buttons look really crummy now. The tooltips works great, though.

You may want to consider using TurboIntegrator for this. Short of hand crafting VBA code which calls the API, TI is going to be hugely faster than any Excel-based solution would be; you may be finished long before 11:45 going that route.

I will investigate, thanks. We are moving data from vers 6 to vers 9. Don't ask me why we are still on vers 6 in 2011. It's a long, sad story.

[Alan Kirk]

Alan,

I actually found the problem, and it was very simple. There is a bug in the tm1 ribbon addon in Excel.

I went to c:\program files\Cognos\TM1\bin\tm1pribbonx and changed the name to tm1pribbonxx.

That fixed it, except the TM1 buttons look really crummy now. The tooltips works great, though.

Yes, unfortunately there has been a bunch of problems with the Ribbon since Microsloth inflicted it on us. A search of the word "Ribbon" in the forum will turn up quite a few results, but the trick is to discover that it's the ribbon that causes the problem.

You may want to consider using TurboIntegrator for this. Short of hand crafting VBA code which calls the API, TI is going to be hugely faster than any Excel-based solution would be; you may be finished long before 11:45 going that route.

I will investigate, thanks. We are moving data from vers 6 to vers 9. Don't ask me why we are still on vers 6 in 2011. It's a long, sad story.

Ah, I remember V6; no aliases, uploads via map sheets and possibly even Data Control! The horror, the horror!

A comment that I heard from a number of upgraders from V6 was that they were so stunned by the speed that they initially didn't believe that the TI processes really ran. TI is not without its frustrations, but you'll really appreciate it (compared to Map sheets) once you have a handle on how it works.

[Carolyn]

Ah, I remember V6; no aliases, uploads via map sheets and possibly even Data Control! The horror, the horror!

Yes, absolutely Data Control. I work with that every day. Tasks, Jobs, etc. And, yes, I see map sheets all the time.

I am very much looking forward to getting everything over to Vers 9 so we can dump Vers 6 once and for all. Thanks you very much for your help.

[Carolyn]

A comment that I heard from a number of upgraders from V6 was that they were so stunned by the speed that they initially didn't believe that the TI processes really ran. TI is not without its frustrations, but you'll really appreciate it (compared to Map sheets) once you have a handle on how it works.

Alan,

I want to personally thank you for the kick in the pants regarding Turbo Integrator. I spent the greater part of a day trying to figure it out, and just now had my first grand success. A Vers. 6 to Vers. 9 conversion process that usually takes days (5-dim cube) took about an hour and a half, most of which was the Vers. 6 export to a cma file.

I am thrilled.

So is my boss. He said he'd get me a big box of Valentine's Day candy if it's all done by Feb 14.

[stephen waters]

A Vers. 6 to Vers. 9 conversion process that usually takes days (5-dim cube) took about an hour and a half, most of which was the Vers. 6 export to a cma file.

Carolyn,
If you are doing big exports of n level data try setting the ViewConsolidationOptimisation paramater in the cfg file to false (ie ViewConsolidationOptimisation=F) and then re-starting the server. This stops TM1 from calculating all the consolidated values depending on the n-levels and should improve export speed even more. See the config paramaters section in the Operating Guide for more details.

We had a client who did regular exports of this type and had a massive slowdown going from Ver 7 ( where the default for the paramter was false ) to Ver 8 (where the default for the paramater chanegd to true!). Setting it explicitly to false cut the export time significantly.

[Carolyn]

If you are doing big exports of n level data try setting the ViewConsolidationOptimisation paramater in the cfg file to false (ie ViewConsolidationOptimisation=F)

Stephen,

Thanks. I'm doing it now and will look for that.

[stephen waters]

Carolyn,
As my colleague David has just pointed out, I may have misled you by not reading the full thread properly. I was thinking you were doing an export from Version 9; if you are doing the export from version 6 I don't know whether this parameter works.

[Carolyn]

if you are doing the export from version 6 I don't know whether this parameter works.

Stephen,

The export is from Vers 6. I did a bunch of 5-dim cubes this afternoon, and it went perfectly. I have a checksum system set up to QC my work.

As far as I can tell, from Vers. 6, all I can do is export. I am skipping zeros and calculated values, but don't have many other choices.