Excel-wizard to migrate Excel-forms from Alea to Tm1?

Not related to a specific OLAP tool. (Includes forum policies and rules).
Post Reply
Benoit
Posts: 5
Joined: Fri Nov 11, 2011 4:38 pm
OLAP Product: Tm1
Version: 10.2.2
Excel Version: Excel 2012

Excel-wizard to migrate Excel-forms from Alea to Tm1?

Post by Benoit »

Hi,

we are migrating from Alea onto Tm1. Obviously we have a large amount of reports in excel where the functions have to be converted:
DBGETC ->DBRW,
DE.NAME -> SUBNM,
and so on ...

I am surely not the first one who have to cop with this. Does anyone know about Excel-assistent which can enlight the cure?

Many thanks

Benoit
Marcus Scherer
Community Contributor
Posts: 126
Joined: Sun Jun 29, 2008 9:33 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2016
Location: Karlsruhe

Re: Excel-wizard to migrate Excel-forms from Alea to Tm1?

Post by Marcus Scherer »

Hi Benoit,
if it is a mere find/replace in formulas issue in Excel it can be solved with some VBA. I could program it on Monday if you want. Please confirm.
Bon week-end, M.
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Excel-wizard to migrate Excel-forms from Alea to Tm1?

Post by lotsaram »

If you just need something to do a whole lot of find/replace then the following should do the trick.
TM1 reference updater

It was designed for a different purpose but provided you are just changing formula names from one value to another without changing syntax then I think it will do what you want as is.
Benoit
Posts: 5
Joined: Fri Nov 11, 2011 4:38 pm
OLAP Product: Tm1
Version: 10.2.2
Excel Version: Excel 2012

Re: Excel-wizard to migrate Excel-forms from Alea to Tm1?

Post by Benoit »

Hi Marcus and lotsaram,

many thanks for your replies.

The Reference update looks cute. I still didn't tested it, but the VB-Code makes a good impression. :D

The biggest problem is that, even if Alea and Tm1 are near to each other, some differences make the Find/Replace to a torture: while Alea manages server and cube as 2 separate Excel cell, Tm1 get it in one separate by a ":"
=DBGETC($A$1;$A$2;... --> =DBR($A$1&":"&$A$2; ...

same for the header dimension, where the subset aren't used in Alea:
=DE.NAME($A$1;"Region";"Germany") --> =SUBNM($A$1&":Region";"Region";"Germany")

Means, I will not have to make a Find/Replace but also to parse the formulas to get the parameters done. :(

Let's see what comes. I will report...

Benoit
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Excel-wizard to migrate Excel-forms from Alea to Tm1?

Post by lotsaram »

I would suggest that in any case if doing something as substantial as changing software vendors that looking at a model and report redesign would be in your best interests anyway ..
Post Reply