TI copying data in mixed - text and numbers cube
-
- Posts: 22
- Joined: Tue Jul 01, 2008 2:12 pm
- OLAP Product: TM1 SSAS
- Version: 9.5,10.2
- Excel Version: 2010 2013 2016
- Location: UK
TI copying data in mixed - text and numbers cube
Hi everyone
I thought this would be easy but...
I have a standardised TI process to copy data between cubes - or within cubes
This is relatively simple - data source is a view - manipulate subsets and views in the Prolog and push data across in the Data tab
So far this works well and I've been able to easily deploy across a variety of situations
Now, I want to use this in a cube where the measures dimension contains both numbers and strings - n and s elements
The problem comes at the variables tab. You have to define an element as a String or a Numeric
If I choose numeric, the process fails unless I remove the string elements from the source view
I could choose string - but then I suppose I get into string to number conversions - untidy
I could define two processes - a bit fiddly
I'm sure I'm missing something here. Is there a simple solution?
Thaks for any suggestions
IanB
32 bit v9.1 sp4 with Excel 2003
I thought this would be easy but...
I have a standardised TI process to copy data between cubes - or within cubes
This is relatively simple - data source is a view - manipulate subsets and views in the Prolog and push data across in the Data tab
So far this works well and I've been able to easily deploy across a variety of situations
Now, I want to use this in a cube where the measures dimension contains both numbers and strings - n and s elements
The problem comes at the variables tab. You have to define an element as a String or a Numeric
If I choose numeric, the process fails unless I remove the string elements from the source view
I could choose string - but then I suppose I get into string to number conversions - untidy
I could define two processes - a bit fiddly
I'm sure I'm missing something here. Is there a simple solution?
Thaks for any suggestions
IanB
32 bit v9.1 sp4 with Excel 2003
-
- MVP
- Posts: 3105
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: TI copying data in mixed - text and numbers cube
Hi Ian
It might be an idea to work with 2 views. 1 for the string measures, 1 for the numeric measures.
Create a loop through the measures dimension, and check the type of element. (or a loop through a subset generated by MDX containing the leaf level elements)
Then, using SubsetElementInsert, add elements to a subset containing numeric elements or to a subset containing string elements.
Perhaps I am also overlooking something basic, but this could work and is not too untidy.
Wim
It might be an idea to work with 2 views. 1 for the string measures, 1 for the numeric measures.
Create a loop through the measures dimension, and check the type of element. (or a loop through a subset generated by MDX containing the leaf level elements)
Then, using SubsetElementInsert, add elements to a subset containing numeric elements or to a subset containing string elements.
Perhaps I am also overlooking something basic, but this could work and is not too untidy.
Wim
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Site Admin
- Posts: 1453
- Joined: Wed May 28, 2008 9:09 am
Re: TI copying data in mixed - text and numbers cube
what about VALUE_IS_STRING? The wizard uses this, then you use Nvalue or Svalue for the output. Not _completely_ sure what populates these, but have a dig around. It's even documented in the help
- Alan Kirk
- Site Admin
- Posts: 6606
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: TI copying data in mixed - text and numbers cube
VALUE_IS_STRING is one of three "hidden" variables (i.e., they don't appear on the Variables tab) which exist when the data source is a cube view. The other two, as you've noted, are SVALUE and NVALUE, which return the cell value in the relevant format. (Obviously NValue is 0 if it's a string.) This allows you to just Ignore the actual value rather than worrying about whether to set it to Numeric or String, and use VALUE_IS_STRING in an If block to determine whether to use the NValue or SValue hidden variable to manipulate the data. The variables are created automatically (and populated automatically) just by virtue of the data source being set to a View. However they're "real" variables; you'll see them if you look at a .pro file.David Usherwood wrote:what about VALUE_IS_STRING? The wizard uses this, then you use Nvalue or Svalue for the output. Not _completely_ sure what populates these, but have a dig around. It's even documented in the help
It's an instance where the TI designers really thought ahead to cover just this kind of example.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- MVP
- Posts: 3651
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: TI copying data in mixed - text and numbers cube
Here's a simple example of how to use Value_is_String, NValue and SValue. These variables exist for any TI that has a VIEW as a datasource. Value_is_String is a true/false test, SValue is always populated, NValue is only populated if the source cell is numeric. These hidden variables are in every VIEW process and they are covered in the help but maybe not highlighted as much as they should be.
Alternatively if these variables didn't exist or you didn't want to use them you could do something like the following which would be equivalent. As the cell type is determined by the element type of the last dimension you can test this condition. Note, for this to work you would have to set the variable type of the "Value" variable to String.
Code: Select all
IF( VALUE_IS_STRING = 1 );
CellPutS( SVALUE, cCube, vDim1, vDim2, vDim3, vDim4, vDim5 );
ELSE;
CellPutN( NVALUE, cCube, vDim1, vDim2, vDim3, vDim4, vDim5 );
ENDIF;
Code: Select all
vElType = DTYPE( cDim5, vDim5 );
IF( vElType @= 'N' );
CellPutN( NUMBR(VALUE), cCube, vDim1, vDim2, vDim3, vDim4, vDim5 );
ELSE;
CellPutS( VALUE, cCube, vDim1, vDim2, vDim3, vDim4, vDim5 );
ENDIF;
- Alan Kirk
- Site Admin
- Posts: 6606
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: TI copying data in mixed - text and numbers cube
Nuh-uh. NValue is always populated as well, but it'll be populated with 0 if the element is a string. Presumably that's to ensure that the variable will never throw an "undefined" error if it's used by mistake on a string element.lotsaram wrote:Here's a simple example of how to use Value_is_String, NValue and SValue. These variables exist for any TI that has a VIEW as a datasource. Value_is_String is a true/false test, SValue is always populated, NValue is only populated if the source cell is numeric.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- MVP
- Posts: 3651
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: TI copying data in mixed - text and numbers cube
In a very strict sense yes, where the value is not numeric the variable is initialised with a value of zero, but this is semantics, a null value in TM1 terms is for most intents and purposes the same thing as no value. Potato, potato ....Alan Kirk wrote:NValue is always populated as well, but it'll be populated with 0 if the element is a string.
- Alan Kirk
- Site Admin
- Posts: 6606
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: TI copying data in mixed - text and numbers cube
No, there is a difference between 0 and undefined/uninitialised.lotsaram wrote:In a very strict sense yes, where the value is not numeric the variable is initialised with a value of zero, but this is semantics, a null value in TM1 terms is for most intents and purposes the same thing as no value. Potato, potato ....Alan Kirk wrote:NValue is always populated as well, but it'll be populated with 0 if the element is a string.
In VBA a numeric variable will automatically have a value of 0 when first used (assuming that Option Explicit isn't set). This is not the case in TI, where use of a variable which has not been assigned a value either by the process or by an earlier line of code will generate a syntax error.
So it's more like potato, tomato.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- Posts: 50
- Joined: Tue Feb 15, 2022 6:43 am
- OLAP Product: IBM Cognos TM1
- Version: PA 2.09 / TM1 11.7.00000.42
- Excel Version: EXCEL 2019
- Location: Asia
Re: TI copying data in mixed - text and numbers cube
i am surprised that whether or not the three variables (value_is_string /svalue/nvalue) should be declared first in the prolog,if i want to use them in the data?thankyou firstlotsaram wrote: ↑Mon Jun 08, 2009 10:19 pm Here's a simple example of how to use Value_is_String, NValue and SValue. These variables exist for any TI that has a VIEW as a datasource. Value_is_String is a true/false test, SValue is always populated, NValue is only populated if the source cell is numeric. These hidden variables are in every VIEW process and they are covered in the help but maybe not highlighted as much as they should be.Alternatively if these variables didn't exist or you didn't want to use them you could do something like the following which would be equivalent. As the cell type is determined by the element type of the last dimension you can test this condition. Note, for this to work you would have to set the variable type of the "Value" variable to String.Code: Select all
IF( VALUE_IS_STRING = 1 ); CellPutS( SVALUE, cCube, vDim1, vDim2, vDim3, vDim4, vDim5 ); ELSE; CellPutN( NVALUE, cCube, vDim1, vDim2, vDim3, vDim4, vDim5 ); ENDIF;
Code: Select all
vElType = DTYPE( cDim5, vDim5 ); IF( vElType @= 'N' ); CellPutN( NUMBR(VALUE), cCube, vDim1, vDim2, vDim3, vDim4, vDim5 ); ELSE; CellPutS( VALUE, cCube, vDim1, vDim2, vDim3, vDim4, vDim5 ); ENDIF;
-
- MVP
- Posts: 3105
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: TI copying data in mixed - text and numbers cube
You can just use them. As Lotsaram already wrote, these variables exist and can be used as such, without any other action to be taken.
Note, they only exist when the data source of the process is a view.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 50
- Joined: Tue Feb 15, 2022 6:43 am
- OLAP Product: IBM Cognos TM1
- Version: PA 2.09 / TM1 11.7.00000.42
- Excel Version: EXCEL 2019
- Location: Asia
Re: TI copying data in mixed - text and numbers cube
Okay,i have succeed ,thankyou verymuchWim Gielis wrote: ↑Sun May 22, 2022 12:44 pmYou can just use them. As Lotsaram already wrote, these variables exist and can be used as such, without any other action to be taken.
Note, they only exist when the data source of the process is a view.
-
- Posts: 50
- Joined: Tue Feb 15, 2022 6:43 am
- OLAP Product: IBM Cognos TM1
- Version: PA 2.09 / TM1 11.7.00000.42
- Excel Version: EXCEL 2019
- Location: Asia
Re: TI copying data in mixed - text and numbers cube
Hi,Wim Gielis:Wim Gielis wrote: ↑Sun May 22, 2022 12:44 pmYou can just use them. As Lotsaram already wrote, these variables exist and can be used as such, without any other action to be taken.
Note, they only exist when the data source of the process is a view.
Sorry to disturb you. I wonder if you know which version of TM1 starts to support REST API. Please let me know if you know
Thankyou first.
-
- MVP
- Posts: 3105
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: TI copying data in mixed - text and numbers cube
Hi,
From memory, TM1 10.2.2 FP4 but I could be wrong
From memory, TM1 10.2.2 FP4 but I could be wrong
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
- Alan Kirk
- Site Admin
- Posts: 6606
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: TI copying data in mixed - text and numbers cube
It's somewhere in 10.2.2. I could look it up but it's academic, because there were a lot of feature gaps in the 10.x iterations. To get the full API functionality requires a jump to one of the PA2 releases.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- Posts: 50
- Joined: Tue Feb 15, 2022 6:43 am
- OLAP Product: IBM Cognos TM1
- Version: PA 2.09 / TM1 11.7.00000.42
- Excel Version: EXCEL 2019
- Location: Asia
Re: TI copying data in mixed - text and numbers cube
i get it,Thank you and Alan Kirk.