How to get the the value out of a Amounts Dimension

[ShaneK]

I'm trying to create a TI Process to extract data from my Budget Cube into SQL Server and i can pass everything except the Value from the BudgetAmount Dimension. If i # out vVAR and just load 1 instead it works but i think the vVAR is trying to convert the leaf Value instead of the Number it's holding..

Can anyone tell me how to get the number and not the leaf from the Value Leaf of my BudgetAmounts Dimension.. BudgetAmounts has a value and a FX Value leaf.


vVAR = 'CellGETN(Value)';

vTemp = '';
vChar = '';
k=1;
WHILE( k <= LONG(Customer) );
vChar = SUBST(Customer, k, 1);
IF(vChar @= '''');
vChar = ' ';
ENDIF;
vTemp = vTemp | vChar;
k = k + 1;

END;
Customer = vTemp;


ODBCOutput('LIVE01-REPO',
'INSERT INTO repository.mapping.TM_TEMP
(
[vNominal]
,[vEntity]
,[vMK2_Cust]
,[Vmast_Cust]
,[vAcc_Mgr]
,[vPeriod]
,[vBU]
,[vDepartment]
,[vREGION]
,[vSALES_PERSON]
,[vVersion]
,[vValueType]
, [vValue]
)
VALUES (''' | Nominals| ''','''
| Entity | ''','''
| Customer | ''','''
| Master_Customer | ''','''
| Account_Manager | ''','''
| BudgetCalendar | ''','''
| BusinessUnit | ''','''
| Department | ''','''
| Regions | ''','''
| Sales_Person | ''','''
| Version | ''','
| numbertostring (vVAR ) | ')');

[tomok]

vVAR = 'CellGETN(Value)';

What is this formula? The CellGETN formula needs one parameter for the cube name, and one parameter for each dimension in the cube specifying an element from that dimension. This thing you have does nothing.

[ShaneK]

Hi, Thanks for replying, i found that on a IBM Fix link, and couldn't get it to do anything.

What I’m trying to do is get the data out of the BudgetAmounts Dimension, Value Leaf..

In the Variables page, it shows the BudgetAmounts as a Element Dimension and the Value Data Element..

I've tried numbertostring (BudgetAmounts ) and numbertostring (Value ) but both return a non numeric error, I’ve spent days googling this but can't find the answer.

I know the TI Proc works as i can send the load to SQL Server when i use numbertostring (1 )

I just can't figure out how to get the number associated to BudgetAmounts.Value into the numbertostring conversion.. I don't think it's a ATTR option, but could be wrong..

I don't even know what to google on this.. Data element of leaf in TM1 and a million other variations..

Any ideas?

Thanks

[jwilliamson_uci]

Hi Shane,

Your vVar should not have quotes around it. You are setting vVar equal to "CellGetN(Value)", which is a string. At the bottom, when you are trying to switch that from a number to a string, it is getting confused because it is already a string.

Jeff

[ShaneK]

Thanks Jeff, one step closer.

Now i'm getting this error message though

"1110112","DimLHINT","Unknown","Unknown","Unknown","P1","HIH-OCC","SALES","AMERICAS","Jane Poulton","Budget 2019-20","Value","128.2051282",Data Source line (1) Error: Data procedure line (0): Cannot convert field number 12, value "Value" to a real number.

I need to extract the 128.2051282 which is linked to the Value Leaf in BudgetAmounts..

vVAR = 'CellGETN(Value)'; just tried to send leaf called Value into a Decimal Field in a table on SQL Server

Also Tried

vVAR = CellGetN('Budget_MK2',Nominals,Entity,Customer,Master_Customer,Account_Manager,BudgetCalendar,BusinessUnit,Department,Regions,Sales_Person,Version,'Value');

Same Error

Any thoughts?

[gtonkin]

Field 12 looks like your measures and should be string.
Field 13 is the value and should be accessible using NValue or SValue depending on your measure type.

[ShaneK]

Hi gtonkin, any chance you could elaborate on that please, i'm not being lazy i just don't know how to google this stuff properly at the moment?

Can i use that with

vVAR = CellGetN('Budget_MK2',Nominals,Entity,Customer,Master_Customer,Account_Manager,BudgetCalendar,BusinessUnit,Department,Regions,Sales_Person,Version,'Value');

or

vVAR = 'CellGETN(Value)';

Field 13 is numeric variable type

Thanks

[Wim Gielis]

That should work:

vVAR = CellGetN('Budget_MK2',Nominals,Entity,Customer,Master_Customer,Account_Manager,BudgetCalendar,BusinessUnit,Department,Regions,Sales_Person,Version,'Value');

To retrieve a numeric value from a cube.
Alternatively use NValue which is the value that you have in the Metadata / Data tab.
Or still use your last variable to store the result.
Then use NumberToString to convert it and insert in the relational database.
All your variables except the last one should be string, the last one can be ignored unless you use it in the code.

[ShaneK]

Hi Wim,

Thanks for that, but are you able to provide a sample using NValue please?.

My Cube is called Budget_MK2

CellGetN('Budget_MK2',Nominals,Entity,Customer,Master_Customer,Account_Manager,BudgetCalendar,BusinessUnit,Department,Regions,Sales_Person,Version,BudgetAmounts)

The last Dimension in the Cube Holding data is caleld BudgetAmounts

Inside BudgetAmounts Dimension i have 2 leaf called Value & FXValue

I'm trying to pick out the numeric value from the leaf called Value.

Do you know how to use NValue or CellGetN to do that?

Thanks

[Wim Gielis]

As to the numeric value (cell value), you have 3 options:

Code: Select all

NumberToString( CellGetN('Budget_MK2',Nominals,Entity,Customer,Master_Customer,Account_Manager,BudgetCalendar,BusinessUnit,Department,Regions,Sales_Person,Version,'Value'))

or

Code: Select all

NumberToString( NValue )

or

Code: Select all

NumberToString( whatever_you_call_your_13th_variable_which_should_be_type_numeric )

All 3 methods have advantages. The only prerequisite is that you know what you are doing.

NValue refers to the value of cell that the TI process is processing in the view containing the data (=the data source). If you don't use a view as the data source of the process, you cannot use NValue.

In case there are issues with separators or delimiters, use the AsciiOutput function to write the values to a text file, and change the code based on that output and your evaluation of it.

Hope this is clear.

[ShaneK]

Thanks so much Wim... All Sorted.. Was just going round and round in circles with this..

[Wim Gielis]

You're welcome. Glad it's solved.