Writing Data from Excel to Tm1 Cube

[Firefly]

Could any one please advise me how to write data from Excelsheet to TM1 Cube What are the step and involved

[David Usherwood]

Read the DBSW entry in the help. This is an Excel function which does exactly what you want. You can put it in your worksheet or call it using VBA eg application.run("DBSW"...).

[par3]

You can try the DBS - function as well.

It gets explained very good in the help file.

[Martin Ryan]

DBSW is the generally preferred method as it is optimised to minimise network traffic. DBS should only be used if there is a problem with the DBSW formulae (manifest by *key_errs that won't go away), or if you require things to be calculated in sequence, rather than all in one go.

There's also DBSS for sending up strings.

Martin

[Firefly]

Thanks you All for the information. Appreciate for your all quick suggestion. I tried to do this following steps still I am getting Key error .Please advise Am i doing anything wrong?

I have a cube called status. which has two dimension one is month and other is text. when i did the DWR i see a result like this

Text
MOnth 10

when i try send with DBSW(12,Statuscube, Month ,Text) it still show a key error.Please advise me

[Alan Kirk]

Thanks you All for the information. Appreciate for your all quick suggestion. I tried to do this following steps still I am getting Key error .Please advise Am i doing anything wrong?

I have a cube called status. which has two dimension one is month and other is text. when i did the DWR i see a result like this

Text
MOnth 10

when i try send with DBSW(12,Statuscube, Month ,Text) it still show a key error.Please advise me

A key error means that either:
- The name of the cube is wrong; or
- The names that you're using for the elements are wrong.

The TM1 Tools add-in (which you can download from this thread will help you to identify which references in your formula have a problem.

Also, if that really is supposed to be a "text" value (that is, you have the element defined as an "S" type rather than an "N" type in the Text dimension) you should be using DBSS, not DBSW. As per the Help file:

DBSW sends a numeric value to a TM1 cube. This function cannot send a string to a cube. To send strings, use the DBSS function.

(In reality, in 9.4 I have no problem sending a string to a cube using DBSW. However you shouldn't rely on it.)

[paulsimon]

Hi

Is this literally what you are typing?

DBSW(12,Statuscube, Month ,Text)

This implies that you are sending numeric data in to a text cell.

I am not clear as to whether Statuscube is a literal value or a Range Name.

I would expect your formula to be more like

=DBSS("12","MyServer:StatusCube","Month","Text")

Regards


Paul Simon

[Firefly]

Thank you all now it worked after using the DBSS function as it was going to string.

[edward]

help please

=DBSS(B63,$D$50,pVersion,pYear,$D$56,pDivision,pBusinessUnit,pCostCategory,$A63,BK$61)

result

=DBSS("ukb0m","Global Overheads Budget Data","Budget V1","FY12","Annual","UK Business Parks","Developments","Travel and Entertainment","0002","Cost Code")

even I have result in the fx I still have key_err on my excel sheet.

I am wondering if I am doing anything wrong ..

thanks

Edward

[Alan Kirk]

=DBSS(B63,$D$50,pVersion,pYear,$D$56,pDivision,pBusinessUnit,pCostCategory,$A63,BK$61)

result

=DBSS("ukb0m","Global Overheads Budget Data","Budget V1","FY12","Annual","UK Business Parks","Developments","Travel and Entertainment","0002","Cost Code")

even I have result in the fx I still have key_err on my excel sheet.

Aaaaand... "the fx" in this context would mean, what exactly? I'd normally think that you're referring to "the function" but you're saying that the result of that is a key error.

I am wondering if I am doing anything wrong ..

If any of the cells that you point to in your DBS formula contain DBRW formulas, change them to DBR instead to ensure that they're evaluated separately from the main batch of TM1 formulas. If not, describe the formulas that the cells contain.

[Martin Ryan]

=DBSS("ukb0m","Global Overheads Budget Data","Budget V1","FY12","Annual","UK Business Parks","Developments","Travel and Entertainment","0002","Cost Code")

You're missing the server reference from your cube reference in argument two. I.e. you need something like this (where server is your TM1 instance name)

=DBSS("ukb0m","server:Global Overheads Budget Data","Budget V1","FY12","Annual","UK Business Parks","Developments","Travel and Entertainment","0002","Cost Code")

[Alan Kirk]

=DBSS("ukb0m","Global Overheads Budget Data","Budget V1","FY12","Annual","UK Business Parks","Developments","Travel and Entertainment","0002","Cost Code")

You're missing the server reference from your cube reference in argument two. I.e. you need something like this (where server is your TM1 instance name)

=DBSS("ukb0m","server:Global Overheads Budget Data","Budget V1","FY12","Annual","UK Business Parks","Developments","Travel and Entertainment","0002","Cost Code")

D'oh! I remember thinking about that when I was writing the reply, then left that entire paragraph out. The perils of multi-tasking...

Martin is probably right; what I left out of my reply was that you can only leave out the server name if you're running a local server. If it's a local server then "CubeName" alone will work, but for any remote server it has to be, as Martin said, "ServerName:CubeName"

[edward]

Thanks Guys it worked when I add the server name .. now I have a problem ... one line with the DBSS is correct in the cube and rest are all in correct when it writes in the cube ... excel 2003 .. I tried it from tm1 9.4.1 and 9.5.1 .. all have the same outcome that most of the numbers are incorrect in the cube , however, the spreadsheet has the correct number ...

[Alan Kirk]

Thanks Guys it worked when I add the server name .. now I have a problem ... one line with the DBSS is correct in the cube and rest are all in correct when it writes in the cube ... excel 2003 .. I tried it from tm1 9.4.1 and 9.5.1 .. all have the same outcome that most of the numbers are incorrect in the cube , however, the spreadsheet has the correct number ...

Are you saying that you're using a DBSS to write numbers? Because if you are, that won't work. If you aren't, please clarify exactly what you are doing.

In any case, if you are using DBS/DBSS/DBSW formulas to write to a cube, it's worth using the formula tracer in TM1 Tools to determine where the formula is writing to. If you get the "wrong" results it's almost certain that it isn't writing to where you think it's writing.

[Christopher Kernahan]

For those without TM1 Tools, if you select the formula, click into the fx bar in Excel, doubleclick on the argument, and press F9, it will change the cell reference into the contained value.

[edward]

Thanks Guys it worked when I add the server name .. now I have a problem ... one line with the DBSS is correct in the cube and rest are all in correct when it writes in the cube ... excel 2003 .. I tried it from tm1 9.4.1 and 9.5.1 .. all have the same outcome that most of the numbers are incorrect in the cube , however, the spreadsheet has the correct number ...

Are you saying that you're using a DBSS to write numbers? Because if you are, that won't work. If you aren't, please clarify exactly what you are doing.

In any case, if you are using DBS/DBSS/DBSW formulas to write to a cube, it's worth using the formula tracer in TM1 Tools to determine where the formula is writing to. If you get the "wrong" results it's almost certain that it isn't writing to where you think it's writing.

Hi Alan,
Yes DBSS is being used to write numbers. Is here any other way to use different function?

thanks

[edward]

For those without TM1 Tools, if you select the formula, click into the fx bar in Excel, doubleclick on the argument, and press F9, it will change the cell reference into the contained value.

Hi Christopher,

The values are corrent inthe inputsheet and the value in the cube is not matching in the inputsheet.

Thanks

[Alan Kirk]

Thanks Guys it worked when I add the server name .. now I have a problem ... one line with the DBSS is correct in the cube and rest are all in correct when it writes in the cube ... excel 2003 .. I tried it from tm1 9.4.1 and 9.5.1 .. all have the same outcome that most of the numbers are incorrect in the cube , however, the spreadsheet has the correct number ...

Are you saying that you're using a DBSS to write numbers? Because if you are, that won't work. If you aren't, please clarify exactly what you are doing.

In any case, if you are using DBS/DBSS/DBSW formulas to write to a cube, it's worth using the formula tracer in TM1 Tools to determine where the formula is writing to. If you get the "wrong" results it's almost certain that it isn't writing to where you think it's writing.

Hi Alan,
Yes DBSS is being used to write numbers. Is here any other way to use different function?

Have you taken a moment to read the entry on DBSS in the Reference Guide?

This function sends a string to a cube of any number of dimensions. This function cannot send a numeric value to a cube. Use the DBS function to send numeric values.

(Or DBSW, but not DBSS.)

[Martin Ryan]

Are you saying that you're using a DBSS to write numbers? Because if you are, that won't work.

Did you notice that comment from Alan? That's probably your problem. You should be using dbsw or dbs.