replacing Tm1 with VBA

[nykager]

Hi,

my boss wants me to replace the Tm1 system which we only use to get data from our SAP downloads into the Excelfiles (TM1 v.8 is to slow for her..)

Now my question, which may sound very stupid, but can I replace TM1 with VBA codes? I am only an Intern and not really the best in IT questions.

Thanks for your answer, and sorry if this thread is opened in the wrong section, I am as well not from an english speaking country, it is kinda hard for me

[David Usherwood]

Depends on what the TM1 application is doing. If it's been built properly I'd be really surprised if porting it to VBA would speed it up. Also, if you are on maintenance, you can upgrade to a newer version without charge.

[nykager]

We just use Tm1 to bring data from the SAP download to our balance sheets and statements.

The formulas in excel look like this: =DBRW("tm1usa:costctr",$B53,$A$1,$C$10,$B$1,$B$2)

While B53, A1, C10, B1 and B2 are criterias to sort the data in correctly.

Updating these files takes up to 5 minutes sometimes..

[Paul Segal]

On the off-chance, you should check that calculation is set to manual in Excel (Tools, Options, Calculation in the Excel menu bar).

[nykager]

Thank you, but thats not the case, I am in manual calculation when working with Tm 1 files.

If I hit F9 my excel will freeze and calculate for 5 minutes or something, then TM1 is updated and I can go on. And my Boss thinks, that VBA is way faster.
(My computer skills are decent, it is just the first time in my life I have to work with tm1)

[lotsaram]

Thank you, but thats not the case, I am in manual calculation when working with Tm 1 files.

If I hit F9 my excel will freeze and calculate for 5 minutes or something, then TM1 is updated and I can go on. And my Boss thinks, that VBA is way faster.
(My computer skills are decent, it is just the first time in my life I have to work with tm1)

I would advise you to get someone who is skilled in TM1 to review your reporting spreadsheets, I'm betting they are constructed in ways that are far from optimal. I notice that the example you gave in which your DBRW references are written uses a hardcoded reference to the cube not a cell reference.
=DBRW("tm1usa:costctr",$B53,$A$1,$C$10,$B$1,$B$2)
This means you are not using a VIEW function to optimize the retrieval of data from the TM1 server (hint look this up in the TM1 reference material and for a practical example slice out a view to a worksheet and look at cell B1.)

Recently I had a case where a workbook that was taking 5 - 8 minutes to calculate on each refresh was reduced to 20 - 30 sec on initial refresh and <5 sec on subsequent refreshes by adding correct VIEW formulas. This is a good place to start.

[ParisHilton]

I'm intrigued by the view function and the potential performance gains.

I've just done as you've suggested, and B1 refers to a view, but cell references still look like

=DBRW($B$1,B$5,$B$2,$A20,B$6)

Could you explain what the giveaway is?

Also, is a view function, simply a slice against a view? I would presume the performance gain would be from the view being precalculated in TM1 ?

Sorry for gatecrashing the party!!!!

[nykager]

Thank you very much.

Problem is, noone is skilled in TM1 here, they just want to get rid of tm1 because they think it is to slow.

Is there any 'easy to understand' tutorial online for tm1 or how should I learn about the view formulas?

Thanks again, great forum!

[ParisHilton]

Are they willing to pay c £1k a day for someone to come in and sort out the performance problems?????

[lotsaram]

Are they willing to pay c £1k a day for someone to come in and sort out the performance problems?????

If they are stuck on v8.1 I think not. The fact that an intern has been tasked with the problem is somewhat of a giveaway..

In terms of what a VIEW formuals does look up "Stargate" and ignore any references to SciFi. There are various discussions and technical papers burried away. Part of the benefit is indeed that once the view is calculated the vlaues are cached and returned without needign to recalculate (assuming no data change between queries), this is why subsequent refreshes are much faster. The other part of the benefit is that once the view is built the entire view array can be returned effectively in one go at more or less the same speed of a single cell retrieval, this is why using view formulas correctly can have performance benefits on the initial query and not jsut subsequent queries. (But it can be trial and error, the size of the benefit will depend on the time required to built the view versus the gain from the faster retrieval. It can depend on dimension size, complexity of calculations, etc. Most of the time there is a benefit and it can be very significant, but not always.)

[ParisHilton]

Are they willing to pay c £1k a day for someone to come in and sort out the performance problems?????

If they are stuck on v8.1 I think not. The fact that an intern has been tasked with the problem is somewhat of a giveaway..

In terms of what a VIEW formuals does look up "Stargate" and ignore any references to SciFi. There are various discussions and technical papers burried away. Part of the benefit is indeed that once the view is calculated the vlaues are cached and returned without needign to recalculate (assuming no data change between queries), this is why subsequent refreshes are much faster. The other part of the benefit is that once the view is built the entire view array can be returned effectively in one go at more or less the same speed of a single cell retrieval, this is why using view formulas correctly can have performance benefits on the initial query and not jsut subsequent queries. (But it can be trial and error, the size of the benefit will depend on the time required to built the view versus the gain from the faster retrieval. It can depend on dimension size, complexity of calculations, etc. Most of the time there is a benefit and it can be very significant, but not always.)

I didn't think the v8.1 bit through, but intern bit was a clue that Watson would have got.
Thanks for taking your time to explain the view issues. I'm a former XL junkie and I just love TM1 all the more , the more I find out about it. At it's heart it really is a dream for coders. I just hope IBM don't fluff it up too much and concentrate on getting some of the peripheral bits more interoperable and polished.

[Martin Ryan]

Problem is, noone is skilled in TM1 here, they just want to get rid of tm1 because they think it is to slow.

If there's a complete lack of interest in TM1 then you might be best to start from scratch. Export the SAP files to something else and just bypass TM1 completely.

Is there any 'easy to understand' tutorial online for tm1 or how should I learn about the view formulas?

There is a tracer for the DBRW formulas in the TM1 tools add-in (http://www.tm1forum.com/viewtopic.php?f=21&t=2515) which will help you get started.

There are a couple of tutorials on You Tube but there is nothing anywhere remotely comprehensive. You can get one on one training but that'll be the 1k a day figure mentioned above.

Otherwise you'll just have to muddle through the manuals and come back here for help when you need it.

Martin

[stex2727]

Are they willing to pay c £1k a day for someone to come in and sort out the performance problems?????

If they are stuck on v8.1 I think not. The fact that an intern has been tasked with the problem is somewhat of a giveaway..

Yet [sigh] I bet they pay their SAP consultants 2.5k per day.

Steve