Issue with Decimals data load from TM1 Cube to Oracle table

[sathishh.mk]

Hi All,

we are loading data from TM1 Cube to Oracle Table.
All Decimal data is not loading the data into oracle table.

Example: One of the value in TM1 has 1.12345678.
when i loaded the this data into table..it is loading as 1.123.

Please help me on this.It is very urgent

Below is the sytax we are using for laoding the data:

ODBCOutput('TEstDEV',Expand( 'INSERT INTO TestUPLOAD (TIME_PERIOD,DEAL_DIM,PNL_DIM,BSLA_DIM,CURRENCY_DIM,COLLATERAL_DIM,INDEX_NAME,NET_FIN_REC,DISCOUNT_SPREAD_ADDER)
VALUES (''%YQ%'',''%Deal_Dim1%'',''%PnL_Dim%'',''%BSLA_Dim%'',''%Currency%'',''%Collateral_Dim%'',''%Index%'',%NET_FIN_REC%,,%DISCOUNT_SPREAD_ADDER%,)'));

Environment: TM1 9.5.1,Window 2008R2

Thanks in Advance!!

Regards,
Sathish

[jim wood]

I don't think this isn't really a TM1 issue. I'm guessing it will depend on the number of decimal places you have allocated to the column in your database. When it's loading the data it will be rounding the number,

Jim.

[manoj928]

Hi Jim,

We already defined Numeic field on DB side with Number(20,8) data type. So i don think so, thats the issue.

Thanks,
Manoj

[jim wood]

Have you set any formatting on the cube or dimensions that you are exporting?

[manoj928]

Yes JIm,

We have set formatting in main cube view as user are using same cube for data inputs.

So one more thing, is there any way we can define Fomatting at variable level while using cellgetn functions.

[jim wood]

I'll get back to you on that one. As for your original problem have you tried using SetODBCUnicodeInterface? I'm not sure it will make any difference but it's worth a try,

Jim.

[jim wood]

As for your variables. You could convert them to text before exporting them using NumberToStringEx. Using this function you are able to define the number of decimal places converted etc....

[manoj928]

Thanks Jim,

Thats really solved my problem.

But i doubt of TM1, why TM1 is not smart enough to feed Number to a table w/o any rounding. Thats really weird. is there any setting on TM1 side, just to avoid rounding issue w/o using NumberToStringEx function.

[jim wood]

That I'm not sure about. I think it's trying to be helpful. By limiting the export to 3 decimal places it is trying to a) reduce data lose and b) stop variable mismatches on the export.

[Duncan P]

The issue is with the EXPAND function which truncates decimals arbitrarily to three places. Additionally in some versions (I think before 9.5) if the number is above a certain size it truncates from the LEFT. Avoid EXPAND for numbers if you care, as you will, about getting a valid textual representation of the number you started with.

[nick_leeson]

Depending on where you are at and if you haven't found a workaround yet.

I would export the data from a text file into a csv file with and then load into oracle using SQLLoader or External Tables.

This would be a lot faster in terms of performance then a ODBC load via a TI.

[sathishh.mk]

Hi All,

This solution is worked for only for One numeric data load.
If want to load more than one numric with Numbertostringex then TI aborting stating the error at SQL last line of the Script.
just i am changing my variables like this:

TestFIN_REC=NUMBERTOSTRINGEX(cellgetn('Test_Cube',YQ,Deal_Dim,PnL_Dim,BSLA_Dim,Currency,Collateral_Dim,Index,'TestFin Rec'),
'###########.#########', '.', ',');
and
Test_SPREAD_ADDER=NUMBERTOSTRINGEX(cellgetn
('Test_Cube',YQ,Deal_Dim,PnL_Dim,BSLA_Dim,Currency,Collateral_Dim,Index,'TestSpread Adder'),'###########.#########', '.', ',');

Toload the data i am using same logic..nothing has changed..,but throwing error as error Executing SQL Query.
If i use any one of the above the script is working fine.

Please help me on this..It is bit urgent.

Thanks in Advance!!

Regards,
Sathihs

[lotsaram]

The issue is with the EXPAND function which truncates decimals arbitrarily to three places. Additionally in some versions (I think before 9.5) if the number is above a certain size it truncates from the LEFT. Avoid EXPAND for numbers if you care, as you will, about getting a valid textual representation of the number you started with.

This is really interesting and also somewhat disturbing!

[sathishh.mk]

Hi All,

This solution is worked for only for One numeric data load.
If want to load more than one numeric with Numbertostringex then TI aborting stating the error at SQL last line of the Script.
just i am changing my variables like this:

TestFIN_REC=NUMBERTOSTRINGEX(cellgetn('Test_Cube',YQ,Deal_Dim,PnL_Dim,BSLA_Dim,Currency,Collateral_Dim,Index,'TestFin Rec'),
'###########.#########', '.', ',');
and
Test_SPREAD_ADDER=NUMBERTOSTRINGEX(cellgetn
('Test_Cube',YQ,Deal_Dim,PnL_Dim,BSLA_Dim,Currency,Collateral_Dim,Index,'TestSpread Adder'),'###########.#########', '.', ',');

To load the data i am using same dataload script..nothing has changed..,but throwing error as "error Executing SQL Query".
If i use any one of the above, then the script is working fine.


dataload script:-

ODBCOutput('TEstDEV',Expand( 'INSERT INTO TestUPLOAD (TIME_PERIOD,DEAL_DIM,PNL_DIM,BSLA_DIM,CURRENCY_DIM,COLLATERAL_DIM,INDEX_NAME,NET_FIN_REC,DISCOUNT_SPREAD_ADDER)
VALUES (''%YQ%'',''%Deal_Dim1%'',''%PnL_Dim%'',''%BSLA_Dim%'',''%Currency%'',''%Collateral_Dim%'',''%Index%'',%TestFIN_REC%,
%Test_SPREAD_ADDER%)'));


Please help me on this..It is bit urgent.

Thanks in Advance!!

Regards,
Sathish

[Steve Rowe]

Suggest you replace ODBC output with asciioutput and then put offending SQL statement into proper SQL client to accurate error message.


Re : Expand

Yeah it's not too smart!

Somewhat disturbing is a bit of an understatement though, can someone who has the ablity raise a bug?

[tomok]

Please help me on this..It is bit urgent.

That's easy. Stop using the EXPAND function. It is not required in order to use the ODBCOUTPUT function. Just build the SQL string yourself by concatentating the pieces together and then using that in the function. I do it all the time and have never had your problem. You can search this forum for examples of how to build the SQL string,

[rcondado]

Hi All,

I had same problem importing data from csv file.
Reading documentation about Expand function, we have:

This function "expands" TurboIntegrator variable names, enclosed in % signs, to their values at run time. If the variable name represents a string variable, the entire variable expression must be enclosed on quotes. For example, "%V1%".

If Expand is fed with a numerical value, an implicit type conversion is performed and the numerical value is converted into a string.

That string has a fixed minimum length of 10 characters. If the converted number is too small to fill 10 characters, it is padded with leading spaces. Only three leading decimal characters are converted. For example, a numerical value of 0.123456789 is converted into the string "0.123".

So the solution for me was so easy as change variable Type to STRING. I hope this will be helpful.

[TrailRunnerMark]

The key piece of the function description for EXPAND,

That string has a fixed minimum length of 10 characters. If the converted number is too small to fill 10 characters, it is padded with leading spaces. Only three leading decimal characters are converted. For example, a numerical value of 0.123456789 is converted into the string "0.123".

was only added in the 10.1 version documentation (at least for Cognoss Express). The 9.5 documentation makes no mention of this "feature".