Dimension Open, Excel 365 (2016) 64 bit and 10.2.2 Automation Error Crash

[Alan Kirk]

I haven't submitted this at this point because I need to check whether this is just a "my machine" thing or something that applies to everyone.

Environment: Windows 10 professional 64 bit.
TM1 10.2.2 FP 7
Excel 2016 (64 bit) from Office 365. Note particularly the "64 bit" part which is where I suspect this issue is coming from.

If I select "Dimension Open" in Perspectives to update an .xdi, then:
(a) If the data directory specified is empty, then an empty list will be shown as expected; but
(b) If the data directory has even a single file (.xdi) in it, Excel will generate an "Automation Error" warning, then crash.

If the data directory is empty I can create a new dimension and save it correctly. (Although Excel will shove the ever-accursed "Book1" in my face after it creates the new .xdi and I have to manually change to the new .xdi file.) However once the file is saved, I can't re-open it through Dimension Open because... Automation Error, crash.

(Workaround: It can be opened as a regular file, updated and saved as a dimension without any major problem. other than the irritating Extension Hardening warning which of course I immediately turned off.)

I'm not encountering this with PA2 (the bits that actually work, that is; Perspectives and Web but not PAX because, well, Docker), but in the PA2 test environment (on a different machine) I'm using Excel 2016 (Office 365) 32 bit.

It could therefore be specific to my machine or, what I think is more likely, there's an issue with the VBA code that reads the list of files in the data directory when it's run by 64 bit Excel.

Is anyone else experiencing this in a similar environment?

[penguinsareawesome]

I am getting the same error using PA 2.0.4 with a 64-bit version of Excel 2013. Dimension worksheets are fine when opening using the 32-bit version (on multiple machines) so seems that the issue is the 64bit version of excel/Tm1 add-in.

I am also able to save the dimension on the 64-bit version as long as I open it via the standard excel function, not the Tm1 add-in drop -down option

[Eighty3]

We upgraded from TM1 10.2.1 to PA 2.0 this week and encountered this issue with Microsoft Excel 2013 64 bit

As pointed out by penguinsareawesome, 32 bit works fine

Thanks for the workaround Alan

Is IBM aware of this bug?

[jwafro]

I have a client with the same issue - opening developer options/ "dimension open" causes excel to crash with Excel O365 and Excel 2019 MSO 64 bit.
This same issue has been reproduced using PA 2.0.8 and 10.2.2 under the O365, using Planning Sample.

They need to use the developer feature as they wholesale change the dimensions used in their analysis, and alternative solutions are not as elegant - eg Dimensional editor is clunky, no desire to use TI scripts and CSV inputs either.

We have found that if the local server data directory, once an XDI or XRU file is saved in there will cause Excel to completely crash without error messages when attempting to open a file from that folder. When run directly on the server we got a VBA Automation Error code 440 before the system close.

Creating a new XDI/XRU file in a blank folder works fine, (eg "Dimension New") and loads into the system.

They are maintaining the system using the work around mentioned by others above, eg. Opening the XDI file in excel directly as an excel document and then using the developer/ Dimension Save option.

Anyone heard of a fix from IBM on this?

[Paul Coggan]

I replicated this issue with PA 2.0.6 and 2.0.9.2 with the current version of 64 bit Office 365 and could not find any reference to any IBM fixes so raised this with them. They have responded to indicate that Excel 2016 and 2019 are supported for Perspectives but Office 365 will not be tested with Perspectives and is thus not supported. The pop up message received when opening a dimension worksheet no longer gives an option to continue with the files open so it appears this is a roadblock unless the registry setting recommended by Alan above can be applied.

In practice we haven't had any issues with the 32 bit version of Office 365. This issue only occurs with 64 bit Office 365.

[Wim Gielis]

I am developing TM1 models for the last 13 years (if we wait 4 days). I have never ever used dimension worksheets, rules sheets, TI code sheets or whatever they would be called, in Excel. Can someone explain to me the relevance and benefit(s) they bring ? Then I can have a better idea on this. Aren't they pretty much obsolete nowadays ?

[gtonkin]

I am developing TM1 models for the last 13 years (if we wait 4 days). I have never ever used dimension worksheets, rules sheets, TI code sheets or whatever they would be called, in Excel. Can someone explain to me the relevance and benefit(s) they bring ? Then I can have a better idea on this. Aren't they pretty much obsolete nowadays ?

Dimension Worksheets used to be great in the early days when you did not receive your meta data/master data from a data source and had to basically build it in a spreadsheet linking the C levels etc. Visually, you could see quite nicely how everything was linked, you could add formulae, add comments, add formatting etc. etc.

I only used them briefly until I opted for building the mappings into attributes or similar cubes from which I could run processes to rebuild the required hierarchies.

I think the guys that spent more time on v8 and earlier would probably give better insights.

[Alan Kirk]

I am developing TM1 models for the last 13 years (if we wait 4 days). I have never ever used dimension worksheets, rules sheets, TI code sheets or whatever they would be called, in Excel.

They aren't called anything. As far as I'm aware there has never been an Excel-based process for generating TI code. If there was I would certainly have been using it in preference to the hinky little 1999-era dinosaur with its teeny tiny fonts based on an 800*600 resolution and which didn't even have find and replace. That's why many of us developed TI code in Notepad++ for years. (As I used to in my pre-Arc days.)

Can someone explain to me the relevance and benefit(s) they bring ? Then I can have a better idea on this. Aren't they pretty much obsolete nowadays ?

No.

The reasons I still use them when I need them:
(a) You can force the order of the elements. You aren't constrained by DimensionSortOrder.
(b) You can embed metadata about the model. It's far easier to include information about why particular consolidations or rollups are structured the way they are, and have it embedded inside the dimension sheets rather than external to the model. You've always been able to add comments to rules, but there the problem was that the classic rules editor sucked and the allegedly advanced rules editor wasn't much better. This is less of an issue now that I use Arc which is finally oh finally a good editor, but even so in an .xru I can write the documentation of the whole model in a separate sheet and not need to go hunting for it if I wish to do that.
(c) Colour coding blocks of rules and elements to make it easier to see where you are, especially in really long dimensions.
(d) Formula based rules. When I have a whole string of rules which are similar but not identical I can generate them by Excel formulas with variable expressions off to the side somewhere.
(e) Formula based elements in a dim, especially for a time dimension. Yes, they don't HAVE to be in an .xdi but if you're generating the structure in Excel anyway then they may as well be.
(f) Formula based attribute management. Sure, you can do that without an .xdi as well, but if you're maintaining the dim via an .xdi it may as well be done there too.
(g) You actually have a real honest to glub find and replace facility. (Again this advantage isn't as relevant to me now that I use Arc.)
(h) I almost forgot; time based consolidation trees. If there is a corporate restructure you can keep a snapshot of what the rollup structure was back in the day. Yes, you can do this to some extent through hierarchies these days, but you may not want to pay the price in either memory or potential user confusion of having a visible, functional hierarchy that shows the reporting structure from 18 months ago and 3 years ago. You may still want to have that preserved somewhere, though.

I don't use them if a dimension is being generated by an external data source. I do if it's a slowly changing dimension where the above factors are useful to me. I still use them more or less exclusively for rules unless it's a really simple set of rules which don't require much in the way of documentation, colour coding, formula generation, etc, in which case I'll do them in Arc.

[Wim Gielis]

Thanks Alan. I can see some useful bits there.