Update excel data from TM1

[bast]

Hello

i have a problem whan i want update the data of an excel export from TM1. All my data have the DBRW fonction, but when i press F9 for update the data i have "#NOM?" error?

How can i update the data of my excel worsheet from TM1 ?

Thanks

[lotsaram]

A #NAME? error indicates that Excel does not recognize the DBRW formula which indicates that the TM1 add-in is not loaded.

If you want to update data that resides in a TM1 cube from excel then you have to meet the following conditions
1/ Have TM1 Perspectives installed and active in your excel session
2/ Be logged in to the relevant TM1 server
3/ Have write access to the cells in question

You aren't meeting conditions 1 or 2, and until you do point 3 is moot.

[bast]

Hi lotsaram

1/ Have TM1 Perspectives installed and active in your excel session :
YES
2/ Be logged in to the relevant TM1 server
YES the same server and the same cube
3/ Have write access to the cells in question
Yes i can access to the cells

But what is the TM1 add-in and how can i load it ?

thanks

[Paul Segal]

But what is the TM1 add-in and how can i load it ?

The TM1 add-in allows you to use TM1 from within Excel.

You can load it a number of ways, but from Excel choose Tools, Add-Ins, Browse and then go to your the Cognos TM1 program directory (this will be C:\Program Files\Cognos\TM1\bin for the default install assuming a 32 bit system) and select TM1P.XLA. This will load the add-in.

Paul

[Alan Kirk]

Hi lotsaram

1/ Have TM1 Perspectives installed and active in your excel session :
YES
2/ Be logged in to the relevant TM1 server
YES the same server and the same cube
3/ Have write access to the cells in question
Yes i can access to the cells

But what is the TM1 add-in and how can i load it ?

I wanted to wait until the first 10 or so of these were done before linking to them in the main forum, but what the hey. This:
http://www.youtube.com/watch?v=kjK8EtY-GuU

explains what the TM1 Add-in is. (Best viewed in 720 HD mode.)

[bast]

Hello paul

The TM1 add-in is loadding

[Alan Kirk]

Hello paul

The TM1 add-in is loadding

If it's loaded then either:
- You've typed the name of the function incorrectly; or
- One of the arguments that you're passing to the DBRW function refer to a range name that doesn't exist. (Though that would normally generate a key error rather than a name error. If the formula contains another invalid expression along with the DBRW, though, a #Name error is possible.)

To check the second possibility I suggest using the formula tracer in TM1 Tools.

You may also want to post the exact formula from the formula bar.

[bast]

Hello paul

The TM1 add-in is loadding

If it's loaded then either:
- You've typed the name of the function incorrectly; or
- One of the arguments that you're passing to the DBRW function refer to a range name that doesn't exist. (Though that would normally generate a key error rather than a name error. If the formula contains another invalid expression along with the DBRW, though, a #Name error is possible.)

To check the second possibility I suggest using the formula tracer in TM1 Tools.

You may also want to post the exact formula from the formula bar.

hi alan

Yes, when i see the details of the DBRW function and i push Enter i have the key error : "KEY_ERR" so i have a problem of a range name ?

[Alan Kirk]

Hello paul

The TM1 add-in is loadding

If it's loaded then either:
- You've typed the name of the function incorrectly; or
- One of the arguments that you're passing to the DBRW function refer to a range name that doesn't exist. (Though that would normally generate a key error rather than a name error. If the formula contains another invalid expression along with the DBRW, though, a #Name error is possible.)

To check the second possibility I suggest using the formula tracer in TM1 Tools.

You may also want to post the exact formula from the formula bar.

hi alan

Yes, when i see the details of the DBRW function and i push Enter i have the key error : "KEY_ERR" so i have a problem of a range name ?

Not necessarily a range name; it's just that you originally said that it was a #Name error. A #Key_Err means that you are using an argument in your DBRW which does not represent a valid element in the corresponding dimension. As I said above, the tracer in TM1 Tools will tell you exactly which argument has the problem.

[bast]

Thanks alan

just an other point

When i connected in my serveur, and see the details of DBRW in a cells and push Enter, i have a correct number, but i can't do that in all my cells of my worksheet.

[Paul Segal]

Thanks alan

just an other point

When i connected in my serveur, and see the details of DBRW in a cells and push Enter, i have a correct number, but i can't do that in all my cells of my worksheet.

What happens when you press the F9 key to recalc?

[bast]

Thanks alan

just an other point

When i connected in my serveur, and see the details of DBRW in a cells and push Enter, i have a correct number, but i can't do that in all my cells of my worksheet.

What happens when you press the F9 key to recalc?

Nothing, i have always the same number, no updating

[Alan Kirk]

Thanks alan

just an other point

When i connected in my serveur, and see the details of DBRW in a cells and push Enter, i have a correct number, but i can't do that in all my cells of my worksheet.

What happens when you press the F9 key to recalc?

Nothing, i have always the same number, no updating

You wouldn't happen to have one of the arguments in your DBRW formula pointing to another DBRW formula, would you? Both the key error and the behaviour that you describe (the correct value being returned when you edit the formula) sound suspiciously like that may be the case.

If so, you would have to change the first formula from a DBRW to a DBR.

DBR formulas are evaluated individually. DBRW formulas are evaluated in batches so if you have one DBRW which is dependent on another one, then the second one can't know the value that it's supposed to use as an element name until the batch has finished calculating. By then it's too late.

[bast]

Hi Alan

As i said when i edit the formula in a cell the value return, but i have to do before the same things in the different subname at the top of the worksheet.
Then when i edit the formula and changed DBRW to DBR in one cells the value return also. But i can do that in all my cells.

An other point when i delete a value in a cells the value don't return, how can i explain the problem ?

i want precise that the files has been send by a friend who use Excel 2007, but i use Excel 2003

thanks

[Paul Segal]

Hi Alan

As i said when i edit the formula in a cell the value return, but i have to do before the same things in the different subname at the top of the worksheet.
Then when i edit the formula and changed DBRW to DBR in one cells the value return also. But i can do that in all my cells.

An other point when i delete a value in a cells the value don't return, how can i explain the problem ?

i want precise that the files has been send by a friend who use Excel 2007, but i use Excel 2003

So it sounds like you are referencing a DBRW from your DBRW formulas, as Alan said. Again, as Alan pointed out, the more efficient way of correcting this is to make the formula you are referencing from the other DBRW formulas a DBR. This will mean the rest can be DBRW formulas and should be faster to refresh.

When you say "delete a value", what do you mean? How are you deleting the value?

The version of Excel shouldn't make a difference, although are you both on the same version of TM1?

[bast]

HI PAUL

I want to say that when i delete a value in a cells and when i press F9 for refresh my worsheet, the value don't return

[Paul Segal]

Do you literally delete the value (i.e. by hitting the delete key)? Is so, then you are deleting the DBRW formula that TM1 depends on to go and get the value you want. If you want the value to be 0, then you should put 0 in the cell. This shouldn't overwrite the DBRW formula.

[bast]

Hi Alan

As i said when i edit the formula in a cell the value return, but i have to do before the same things in the different subname at the top of the worksheet.
Then when i edit the formula and changed DBRW to DBR in one cells the value return also. But i can do that in all my cells.

An other point when i delete a value in a cells the value don't return, how can i explain the problem ?

i want precise that the files has been send by a friend who use Excel 2007, but i use Excel 2003

So it sounds like you are referencing a DBRW from your DBRW formulas, as Alan said. Again, as Alan pointed out, the more efficient way of correcting this is to make the formula you are referencing from the other DBRW formulas a DBR. This will mean the rest can be DBRW formulas and should be faster to refresh.

When you say "delete a value", what do you mean? How are you deleting the value?

The version of Excel shouldn't make a difference, although are you both on the same version of TM1?

Where i have to change DBRW formula to DBR ? because in each cells of my worksheet i have DBRW formula ?

Thank you very much

[Wim Gielis]

Where i have to change DBRW formula to DBR ? because in each cells of my worksheet i have DBRW formula ?

Thank you very much

Edit > Find / Replace will work too.

[Alan Kirk]

So it sounds like you are referencing a DBRW from your DBRW formulas, as Alan said. Again, as Alan pointed out, the more efficient way of correcting this is to make the formula you are referencing from the other DBRW formulas a DBR. This will mean the rest can be DBRW formulas and should be faster to refresh.

Where i have to change DBRW formula to DBR ? because in each cells of my worksheet i have DBRW formula ?

Let me see whether I can explain this graphically. You start out with a report based on a standard slice. The DBRW is in the bright yellow cell. The arguments are in the pale yellow cells. All of them are SubNm functions except for the account in cell A15, which is hard coded. The Weeks value is in B14, and is also a SubNm. This works fine.

Rpt1.jpg (177.86 KiB) Viewed 18311 times

Now suppose that we want to put a second column in which shows the values for the corresponding period in the previous year. In this model there's a cube that can do that called z_Dates. It has two dimensions; one shows all of the dates, the other the lookup values. We can then create a DBRW formula to look up the "Last Year" value for whatever period the user selects. Again the formula is in bright yellow cell C14), with the arguments in light yellow.

Rpt2.jpg (186.47 KiB) Viewed 18311 times

So far, so good. However let's now add a DBRW which uses that prior year value. It goes into cell C15, and points to the same cells as the original one except for the Weeks dimension, which uses the calculated prior year value in C14 instead of the SubNm formula in B14.

What happens when we press [F9]? This does:

Rpt3.jpg (219.63 KiB) Viewed 18311 times

Why?

Because when we hit [F9] TM1 tries to recalculate all of the DBRW formulas at the same time. The problem is that it can't calculate C15's value without knowing the value for C14, because it needs to know the week that you want to use. And since C14 has yet to be calculated, C15 can't know the week dimension element and the result is a key error.

However if you edit cell C15 by itself instead of pressing [F9] you will get the right answer because when you edit a cell, that cell and that cell alone is being calculated. Cell C14 won't be calculated, which means that it still has its original value. That means that C15 will be able to use that value. And that means... it won't have a key error.

Rpt4.jpg (211.2 KiB) Viewed 18311 times

To repeat, the only time that there will be a problem is when you press [F9], at which time TM1 will be trying to calculate C14 and C15 at the same time, with C15 failing because the calculation of C14 is incomplete.

The solution to this is to change C14 from a DBRW to a DBR. DBR's are calculated singly rather than in a batch which means that when you hit [F9], TM1 will calculate C14, then calculate all of the DBRW forumulas.

This sounds like the situation that you're describing.

What you would need to do is change any cells which are like C14 on your own sheet to DBR's. (That is, any DBRW's which other DBRW's rely on.)