TI copying data in mixed - text and numbers cube

Post Reply
IanB
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

Post by IanB »

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
Wim Gielis
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

Post by Wim Gielis »

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
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
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: TI copying data in mixed - text and numbers cube

Post by David Usherwood »

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 :D
User avatar
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

Post by Alan Kirk »

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 :D
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.

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.
lotsaram
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

Post by lotsaram »

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.

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;
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

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;
User avatar
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

Post by Alan Kirk »

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.
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.
"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.
lotsaram
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

Post by lotsaram »

Alan Kirk wrote:NValue is always populated as well, but it'll be populated with 0 if the element is a string.
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 ....
User avatar
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

Post by Alan Kirk »

lotsaram wrote:
Alan Kirk wrote:NValue is always populated as well, but it'll be populated with 0 if the element is a string.
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 ....
No, there is a difference between 0 and undefined/uninitialised.

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.
Niko
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

Post by Niko »

lotsaram 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.

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;
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

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;
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 first
Wim Gielis
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

Post by Wim Gielis »

Niko wrote: Sun May 22, 2022 11:03 ami 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 first
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
Niko
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

Post by Niko »

Wim Gielis wrote: Sun May 22, 2022 12:44 pm
Niko wrote: Sun May 22, 2022 11:03 ami 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 first
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.
Okay,i have succeed ,thankyou verymuch
Niko
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

Post by Niko »

Wim Gielis wrote: Sun May 22, 2022 12:44 pm
Niko wrote: Sun May 22, 2022 11:03 ami 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 first
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.
Hi,Wim Gielis:
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.
Wim Gielis
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

Post by Wim Gielis »

Hi,

From memory, TM1 10.2.2 FP4 but I could be wrong 8-)
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
User avatar
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

Post by Alan Kirk »

Wim Gielis wrote: Thu May 26, 2022 7:16 am Hi,

From memory, TM1 10.2.2 FP4 but I could be wrong 8-)
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.
Niko
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

Post by Niko »

Wim Gielis wrote: Thu May 26, 2022 7:16 am Hi,

From memory, TM1 10.2.2 FP4 but I could be wrong 8-)
i get it,Thank you and Alan Kirk.
Post Reply