Documentation Deficiencies

Ideas and tips for enhancing your TM1 application
Post Reply
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:

Documentation Deficiencies

Post by Alan Kirk »

As we know, the TM1 documentation can be... how can we put this delicately... "sub-optimal", at times. I think it would therefore be useful to have a thread in which things which are either flat out wrong, badly explained, or inadequately explained can be compiled. I would hope that the thread can be referred to IBM in due course for them to address some of the shortcomings (as well as being a reference for the forum membership), so regardless of how frustrating some of the documentation can be (and believe me, I know, I know...) please try to keep posts in a "just the facts, ma'am" tone. It's probably also a good idea to keep to one post per topic, unless they're closely connected.

Allow me to begin with the following post...
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: Documentation Deficiencies QUDEFINE/QUDEFINEEX/QUEXPORT

Post by Alan Kirk »

Document: Reference Guide
Section/Chapter: TM1 Macro Functions
Topic: QUDEFINE and QUDEFINEEX

Deficiency
lowlim The lowest cell value to be considered for export.
highlim The highest cell value to be considered for export.

=QUDEFINEEX("local:SalesCube", "Topsell", Sheet1!B3:F5, 3000, 5000, TRUE, TRUE, FALSE)

This example creates a query set that contain elements listed in Sheet1, in the cell range B3:F5. When you run this query, TM1 inspects only cube cells identified by these elements and exports non-consolidated values in the range 3000 to 5000, including those derived through rules.
Neither the argument description nor the example address the situation, which would apply to the overwhelming majority of exports, where the user does not want to apply a low or high limit. Is the argument supposed to be a 0? A zero length string? Omitted completely?

The last is the correct option. If you don't want limits then just leave out the arguments completely, though it's still necessary to include place holder commas. Running the above query without limits would therefore be done in the form:

Code: Select all

vnt = Application.Run("QUDEFINEEX", "local:SalesCube", "Topsell", Sheet1!B3:F5, , , TRUE, TRUE, FALSE)
When using the function in an Application.Run call, the return value will be True if the view is created, or False if it is not.

-------------------------------------------------------
Document: Reference Guide
Section/Chapter: TM1 Macro Functions
Topic: QUEXPORT

Deficiency
The following appears in the argument list:
file The name of the delimited ASCII file (.cma) to contain the exported cube data. Do not include the file extension. The file is created in the local data directory.
This is incorrect. If you don't specify a path then the file may end up in the data directory, though it may not.

You can specify a path as part of the File argument to control where the export is sent to with one important proviso; as with the TI AsciiOutput /TextOutput functions, the path that you specify must be seen from the TM1 Server's point of view. You cannot specify a path on your local computer unless that path is a network share which can be seen by the TM1 server, or unless you are running the command on the server box itself.

If you specify a path that the server cannot see, the command will fail but will not cause a trappable runtime error. Executing the command via an Application.Run function will allow you to capture the result for checking. For example:

Code: Select all

vnt = Application.Run("QUEXPORT", s_CubeName, mSC_VIEWNAME, s_OutputFilePath)
vnt will be True if the export succeeded, or False if it did not.
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: Documentation Deficiencies TM1_API2HAN

Post by Alan Kirk »

I know what I said about keeping posts to "just the facts", but I have to concede that this one has been grinding my gears for so long that it's almost a grudge. It's something that everyone who programs in the API within Excel VBA needs to know, and which IBM point blank refuses to document, claiming that it's not a recommended way of doing things. Instead, they recommend that you log in every time you need to execute some API code. Yes, because you'll really be continually logging in and out to run some API code in the background, especially if the user is limited to a single connection on the server :roll: ...

Document: API Guide
Section/Chapter: TM1 Programming Concepts
Topic: TM1_API2HAN

Deficiency
Undocumented function.

In a stand-alone TM1 application you need to take the following steps to connect to a server via the API. (Note: The "handles" referred to below are essentially pointers to locations in the computer's memory. They allow your application to find and connect to the various objects related to a TM1 server. For your purposes they're just long integers which need to be obtained by various "create" functions, and passed to other functions to obtain information from or about the TM1 server):
  1. Initialise the API by calling the TM1APIInitialize function;
  2. Obtain a handle that will be used to refer to the current client's TM1 session using TM1SystemOpen. This handle (referred to as the "user handle" below) does not connect you to anything; it's merely in preparation for connecting;
  3. Set the Admin Host that the user handle will be using via the TM1SystemAdminHostSet function;
  4. Create a value pool (a value pool is a container for value capsules, and value capsules are data structures which are specific to TM1) and obtain a handle to it by passing the user handle to the TM1ValPoolCreate function;
  5. Create a value capsule which will hold an integer value by passing the value pool handle to the TM1ValIndex function. This integer value will be the maximum length of the strings that you create in the next step;
  6. Create three value capsules which hold strings (text) by passing the following arguments to the TM1ValString function:
    1. The pool handle created above;
    2. The string that is to be contained in the value capsule; and
    3. The value capsule containing the integer which specifies the maximum length of the string.
    4. These three string value capsules will store the login, password and server name respectively;
  7. Connect to the server (and obtain a handle to it) by passing the following arguments to the TM1SystemServerConnect function:
    1. The pool handle;
    2. The value capsule which contains the server's name;
    3. The value capsule which contains the TM1 user's name (technically, the "client name"); and
    4. The value capsule which contains the user's password.
If you are running VBA code in an Excel environment which has the standard TM1 client loaded, however, it's pointless to have to continually log out and back in if instead you can "piggy-back" on the user's existing login by borrowing the user handle from that session.

The function which allows that is TM1_API2HAN. It is not declared in the standard function declarations .bas module that Cognos supplies with the API, and must be added manually. The declaration for the function is:

Code: Select all

Declare Function TM1_API2HAN Lib "tm1.xll" () As Long
It is called as follows:

Code: Select all

hUser = TM1_API2HAN
The function returns the user handle that is normally created via the TM1SystemOpen function in step 2 above, but there are notable differences:
  • It's unnecessary to call the TM1APIInitialize function since the standard TM1 client software has already done that.
  • Similarly there is no need to use the TM1SystemAdminHostSet function, since the user handle will have already been assigned the relevant admin host before TM1_API2HAN passes it to you.
  • You don't need to create the value capsules that are used by the TM1SystemServerConnect function, nor to call that function since again the TM1 client software will have taken care of this (if the user is logged in).
  • You do still need to get a handle to the server to do anything with it, but you do that using the TM1SystemServerHandle function instead of the TM1SystemServerConnect one. TM1SystemServerHandle takes two arguments; the user handle, and a string containing the server name. (Confusingly, not a value capsule containing the string (which TM1SystemServerConnect requires), but rather the string itself.)
  • You don't need to create a value pool to connect to the server, but you do for almost anything else that you do on the server. Consequently you'll almost certainly pass the user handle that TM1_API2HAN provides to TM1ValPoolCreate to create such a pool and obtain its handle.
There are some traps, however:
  • Even if the user is not logged in, the user handle value will be a non-zero value so you can't rely on that to determine whether the user is connected. However if the user isn't logged in the TM1SystemServerHandle function should return 0, and you can test that to ensure that you have a valid server connection to work with.
  • With most TM1 API code you need to call TM1ValPoolDestroy (if you've created a value pool) to destroy that pool, as well as TM1SystemClose and TM1APIFinalize to terminate the session. You should still use TM1ValPoolDestroy if applicable but you must not use TM1SystemClose or TM1APIFinalize. If you do, it will destabilise the session that you're "borrowing" the user handle from and, in turn, Excel. The user's Excel session will most likely crash without warning the next time they attempt to use TM1 functionality.
There you go, Iboglix, I've done it for you. See, it really wasn't that hard now, was it?
"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.
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: Documentation Deficiencies Subsiz/SubsetGetSize

Post by Alan Kirk »

Document: Reference Guide
Section/Chapter: TM1 Worksheet Functions and TM1 TurboIntegrator Functions/ Subset Manipulation Functions
Topic: SubSiz and SubsetgetSize respectively.

Deficiency
Missing cross-references.

In an ideal world a software company should implement nomenclature standards which ensure that functions which return the same results (or expose the same functionality) bear the same name, regardless of the interface being employed. Unfortunately Applix didn't, which means that the names of worksheet functions are sometimes (but not always) different from the equivalent functions in TI or Rules. The result of this is an occasional mental blind spot when working across different interfaces when trying to recall the name of the function that you need, which is why all such functions should be cross-referenced in the help file.

In TM1 Worksheets the function to use to obtain the number of elements contained in a subset is SubSiz. In TI Processes, it's SubsetGetSize. There is no corresponding function for Rules, which cannot normally be driven by subsets since it would make the results of the rules unpredictable depending on which elements were added to or subtracted from the subset, or even whether the subset had been deleted at any given time.

NB: The version 9.0 .chm help file did contain a cross-reference in the SubSiz entry, though it didn't in the SubsetGetSize one. Neither topic contains a cross-reference in the 9.5.2 web version of the Reference Guide.
"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.
User avatar
qml
MVP
Posts: 1094
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1 / Planning Analytics
Version: 2.0.9 and all previous
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: Documentation Deficiencies

Post by qml »

Documents: TM1 TurboIntegrator 9.5.x and TM1 Reference 9.5.x
Section/Chapter: TurboIntegrator Reserved Words -> Process Function Names and TM1 TurboIntegrator Functions

Deficiency
Missing/inconsistent function descriptions and cross-references.

A lot of people have noticed by now how full of holes the documentation is. You look at the list of TI functions in one place and it's totally different to the list in another place.

This results in a large number of TI functions being only listed as reserved words, but not really described in any detail, effectively ending up as "undocumented TI functions":

AllowExternalRequests
AttrToAlias
CubeLockOverride
CubeSetConnParams
CubeSetIsVirtual
CubeSetSAPVariablesClause
CubeSetSlicerMembers
DimensionEditingAliasSet
DimensionElementInsertByAlias
EncodePassword
IsNull
LockOff
LockOn
ReturnSQLTableHandle
ReturnViewHandle
SwapAliasWithPrincipalName

What is perhaps even funnier, there are a number of functions that have their description in TM1 Reference, but are not listed as reserved words / available TI functions in the TurboIntegrator doc:

BatchUpdateFinishWait
CubeClearData
DataSourceSAPUsingRoleAuths
DataSourceSAPUsingTexts
DisableBulkLoadMode
EnableBulkLoadMode
Expand
GetProcessName
GetUseActiveSandboxProperty
ODBCOPENEx
ServerActiveSandboxGet
ServerActiveSandboxSet
TextOutput
TM1User (and BTW, this is not even a TI function, but a rule and worksheet function that can be used in TI)
Kamil Arendt
User avatar
bihints.com
Posts: 52
Joined: Tue May 20, 2008 8:56 am
OLAP Product: TM1
Version: 9.0.3
Excel Version: 2003
Contact:

Re: Documentation Deficiencies

Post by bihints.com »

Ironically, the SwapAliasWithPrincipalName trick, that has been going around the blogs since last year, has just been posted on the official IBM technotes:

http://www.ibm.com/support/docview.wss?uid=swg21571068

A few years ago, I suggested to one Applix support manager of the time that it could be useful to have the basic documentation setup online as a crowdsourced wiki and let users edit it, with a disclaimer that these would not be the official docs. Then the best contributions could be integrated to the official docs, less maintenance for them, better docs for us, everyone would win.
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: Documentation Deficiencies

Post by Alan Kirk »

Document: TM1 User Guide (Online Version, 9.5.2)
Section/Chapter: TM1 Web Overview
Topic: Starting TM1 Web

Deficiency
Enter the URL provided by your TM1 Web administrator, using the following example.

http://machine_name/
virtual_directory/TM1Weblogin.aspx

where:

machine_name is the name of the Web server used to deliver TM1 Web pages.

virtual_directory is the name of the virtual directory the Web server uses to locate TM1 Web pages. The default virtual directory name is TM1 Web.
No it isn't. The default virtual directory name is TM1Web, without a space. TM1 may ignore whitespaces. Web addresses are rather less forgiving.
"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.
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: Documentation Deficiencies

Post by Alan Kirk »

Document: Reference Guide
Section/Chapter: TM1 Macro Functions
Topic: TWEVENTER1 / TWEVENTER

Deficiency
This one is arguable; it may well be that Iboglix never intended the functions for normal user usage (and therefore never documented them), but the first one is too useful to ignore in an Excel/VBA environment.

The functions appear to be exported from the tm1.xll library (the native names being twEvEnt1 and TwEvEnt respectively) and can be called by the usual Application.Run method of VBA. The TWEVENTER1 one is the one that this article relates to; TWEVENTER doesn't work in the way described below. It isn't clear whether this was the intention of the function, or just a happy side-effect.

When the cursor is on a cell which contains either a SubNm or DimNm formula, running the TWEVENTER1 macro is equivalent to having the user double-click on the cell; that is, it will launch the subset editor to allow a new element to be selected. If the active cell contains any other kind of content (including TM1 formulas like DBRW), or is empty, or the client is not connected to the server, the macro will have no effect.

This is useful if you want to avoid giving the users direct access to the SubNm formulas and therefore run the risk of having them damaged. You can create a VBA procedure which:
  • Sets the active cell to a hidden one which contains a SubNm formula;
  • Runs the TWEVENTER1 macro via the Application.Run event;
  • Sets the cursor back to where it was;
and attach that procedure to a drawing object via the Assign Macro... item on the right click context menu. If you make the drawing object transparent and place it over a cell which contains a formula referring back to the value of the hidden SubNm formula, the users will never have direct access to those formulas and can therefore never "break" the functionality by accidentally deleting the SubNm formula. It also provides you with the ability to do some validation within the VBA function if you need to.

Syntax

Code: Select all

Application.Run "TWEVENTER1"
Arguments
None
"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.
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: Documentation Deficiencies

Post by Alan Kirk »

Document: Reference Guide
Section/Chapter: Rules Functions
Topic: ElIspar, ElIsComp and ElIsAnc

Deficiency
The syntax and argument list are sloppily written. "The name of an element" is hardly useful information when there are two elements which need to be passed in a specific order, especially when the order is different in ElIsComp and ElIsPar. You need to read through either the preamble or the example to figure out which element goes to which argument, when it should be obvious from the syntax declaration. In addition relevant cross-references are missing from the topics.

The following change would be useful:

ElIspar
Replace the Syntax line with:
ELISPAR(Dimension, ParentElement, ChildElement)
Replace the argument list with the following:
Argument Description
Dimension: A valid dimension name.
ParentElement: The name of a consolidation element within the Dimension which may be an immediate parent of the ChildElement.
ChildElement: The name of an element within the dimension which may be an immediate child of the ParentElement. This element may be either a Leaf element or another Consolidation element.
Add the following to the end of the example:
To determine whether a consolidation is any number of levels above the ChildElement, use the ElIsAnc function instead.

Please be aware that if you have multiple hierarchies in the Dimension then this test will check all of those hierarchies for a match.
ElIsAnc
Replace the Syntax line with:
ELISANC(Dimension, AncestorElement, DescendantElement)
Replace the argument list with the following:
Argument Description
Dimension: A valid dimension name.
AncestorElement: The name of a consolidation element within the Dimension which may be one or more levels above the DescendantElement in one of the dimension's hierarchies.
DescendantElement: The name of an element within the dimension which may be one or more levels below the AncestorElement in one of the dimension's hierarchies.
Add the following to the end of the example:
To determine whether a consolidation is one and only one level above the AncestorElement, use the ElIsPar function instead.

Please be aware that if you have multiple hierarchies in the Dimension then this test will check all of those hierarchies for a match.
ElIsComp
Replace the syntax line with:
ELISCOMP(Dimension, ChildElement, ParentElement)
Replace the argument list with the following:
Argument Description
Dimension: A valid dimension name.
ChildElement: The name of an element within the dimension which may be an immediate child of the ParentElement. This element may be either a Leaf element or a Consolidation element.
ParentElement: The name of a consolidation element within the Dimension which may be an immediate parent of the ChildElement.
Add the following to the end of the example:
There is no function to check whether ChildElement is more than one level below ParentElement in a hierarchy. Instead, use the ElIsAnc function and reverse the order of the arguments. (That is, check whether ParentElement is an ancestor of ChildElement rather than attempting to check whether ChildElement is a descendant of ParentElement.)

Please be aware that if you have multiple hierarchies in the Dimension then this test will check all of those hierarchies for a match.
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: Documentation Deficiencies

Post by Alan Kirk »

Document: Reference Guide
Section/Chapter: Rules Functions / Logical Rules Functions
Topic: If

Deficiency
The document does not mention the number of nested If() functions that can be contained in a rule. The limit of 20 is mentioned in the corresponding entry in the Process Control TurboIntegrator Functions section.

Experimentation suggests that the number of nested If() functions that can be contained within a rule is 30 in version 10.1 If you exceed this number you will receive a stack overflow error. This appears to occur independent of the line length. (In the test a rule which had 30 If() statements compiled despite being longer than one which had 31 If() functions.)

Note that the presence of a rule which has 30 nested If() functions suggests that the overall design of the cube should be revisited. Aside from being difficult to follow and therefore to maintain, performance is likely to be impacted if there is a large number of such statements.
"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.
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: Documentation Deficiencies

Post by Alan Kirk »

Document: Reference Guide
Section/Chapter: TM1 Turbointegrator Functions / Dimension Manipulation Functions
Topic: DimensionElementInsert

Deficiency
The documentation neglects to mention two issues that can arise in conjunction with the use of this function.

The first can occur when the ProcessQuit or ProcessError commands are used.

The documentation notes that this function cannot be used in the Data or Epilog tabs, which by extension means that it must be used in either the Prolog or Metadata tab. However if you call ProcessQuit or ProcessError in either of those tabs then any DimensionElementInsert calls that you have done will be lost. It appears that the dimensions will only be updated at the end of the Metadata tab. The solution to this is to use the ProcessBreak command instead. If necessary, you may need to encase any code in the Epilog inside an If()/EndIf block to ensure that it will only execute if you are not exiting the process in this way.

Note that the creation of the elements is not affected if you use ProcessQuit or ProcessError in the Data or Epilog tabs.

However you should be aware that the situation is different when you use the DimensionCreate function in the Prolog. Even if you use ProcessQuit or ProcessError in the Prolog or Metadata tab, the dimension will still be created. Since that function is committed but the DimensionElementInsert calls are not, you can potentially be left with an empty dimension.

The second issue to be aware of is that because the elements are not committed until the end of the Metadata tab, any CellPutN / CellPutS statements which use the new elements in either the Prolog or Metadata tabs will not have any effect. The solutions are to either:
(a) Leave the writing of any data to either the Data or Epilog tabs; or
(b) Do the element creation in a separate process which is called from the Prolog or Metadata tabs.
"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.
Duncan P
MVP
Posts: 600
Joined: Wed Aug 17, 2011 1:19 pm
OLAP Product: TM1
Version: 9.5.2 10.1 10.2
Excel Version: 2003 2007
Location: York, UK

Re: Documentation Deficiencies

Post by Duncan P »

At the risk of sounding like a cracked record - if you use a sub-process to modify the dimension, take note of this warning.
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: Documentation Deficiencies

Post by Alan Kirk »

Document: Reference Guide
Section/Chapter: TM1 Turbointegrator Functions / Dimension Manipulation Functions
Topic: DimensionElementPrincipalName

Deficiency

Code: Select all

Syntax
DimensionElementPrincipalName( DimName, ElName )
Argument  Description
 
DimName    The name of the dimension from which you want to retrieve a principal element name.
ElName     An element name. ElName can be either an element alias or a principal element name.
The documentation fails to warn you of the consequences of feeding the wrong value to DimName.

In a piece of code I inherited, the function was called in the Metadata tab to obtain the principal name of an element in a particular dimension. The dimension was specified by a variable sDim.

sDim was not initialised in the Metadata tab and therefore carried over its value from the Prolog tab. Its last use on the Prolog tab was for an entirely different dimension.

In theory the function should therefore have thrown an error and the code should not have worked since the element did not exist in the dimension specified. In practice it did not throw an error and did work because the function returned whatever value is passed to ElName, regardless of whether that element existed in the specified (or any other) dimension. In the case of this particular code, users were generally entering the principal name anyway which is why errors weren't encountered.

The only time you will get an error is if the dimension specified does not exist on the server.

This fact can make tracking down downstream bugs a little problematic.
"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.
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Documentation Deficiencies

Post by declanr »

Document: Reference Guide
Section/Chapter: TM1 Turbointegrator Functions / Subset Manipulation Functions
Topic: SubsetCreateByMDX
Version: 10.1 +

Deficiency:

The documentation reads as below:
SubsetCreateByMDX



This function creates a public subset based on a passed MDX expression.

This is a TM1® TurboIntegrator function, valid only in TurboIntegrator processes.


Syntax
SubsetCreatebyMDX(SubName, MDX_Expression);




Argument


Description



SubName

The name you want to assign to the subset.


MDX_Expression

An MDX expression that returns a subset.



Example
SubsetCreatebyMDX('0-level months', '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL([month] )}, 0)}, ASC)} ' );

This example creates a public subset named '0-level months' based on an MDX expression that returns a subset consisting of all 0-level elements in the Month dimension, sorted in ascending alphabetical order.



Parent topic: Subset Manipulation TurboIntegrator Functions

Making no mention of the third argument "Dimension Name", passing the third optional argument allows the function to create an empty dynamic subset (without the argument an empty result of correctly syntaxed MDX will result in an error to the TI)

Code: Select all

SubsetCreateByMDX ( SubsetName, MDX, DimensionName);
Declan Rodger
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Documentation Deficiencies

Post by declanr »

Document: Turbointegrator Guide
Section/Chapter: Turbointegrator Basics
Topic: String Length Limit in TurboIntegrator
Version: 9.5.2 (fixpack something or other?)

Deficiency:

The documentation reads as below:


Open and Close navigation pane


String Length Limit in TurboIntegrator


TurboIntegrator is capable of handling string data in sizes of up to 8000 single-byte characters at a time. This limit applies when your TI process is performing actions such as assigning a value to a variable or importing individual records of data. Any value or record longer than 8000 single-byte characters is truncated.

This limit applies when your TI process is performing actions such as assigning a value to a variable or importing individual records of data. Any value or record longer than 8000 single-byte characters is truncated.

For example, if you are importing lines of data from a text file, each line of text can be no more than 8000 characters long. If you are importing data from a comma delimited file, each record in the file can be no more than 8000 characters long.

Since one of the fixpacks in 9.5.2 (not sure which one) the limit has actually been around 65,000
Declan Rodger
Duncan P
MVP
Posts: 600
Joined: Wed Aug 17, 2011 1:19 pm
OLAP Product: TM1
Version: 9.5.2 10.1 10.2
Excel Version: 2003 2007
Location: York, UK

Re: Documentation Deficiencies

Post by Duncan P »

The 65000 limit is on the total line length output in a call to ASCIIOutput. The limit on text variable manipulation in TI is considerably higher, over a million and a half.

See these two posts Maximum Number of Characters for a Text Cell? and Adding ATTRPUTN
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Documentation Deficiencies

Post by declanr »

Duncan P wrote:The 65000 limit is on the total line length output in a call to ASCIIOutput. The limit on text variable manipulation in TI is considerably higher, over a million and a half.

See these two posts Maximum Number of Characters for a Text Cell? and Adding ATTRPUTN
Good catch.
Declan Rodger
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: Documentation Deficiencies

Post by Alan Kirk »

bihints.com wrote:Ironically, the SwapAliasWithPrincipalName trick, that has been going around the blogs since last year, has just been posted on the official IBM technotes:

http://www.ibm.com/support/docview.wss?uid=swg21571068
Interestingly that technote is now in the land of 404. I can't recall all of what was in it but interestingly the following APAR appeared the year after this post and another one by Andy Key which also referred to the technote (and quotes part of it). I'll put an extract from the APAR in case of link rot:
PM65881: FUNCTION SWAPALIASWITHPRINCIPALNAME DELETES DATA FOR ALIAS WITH INITIAL NAME, AFTER SERVER RESTART

APAR status
Closed as program error.

Error description
Customer wants to rename the dimension, hence used SwapAliasWithPrincipalName. Swapping successful.?After swapping the names, customer deleted the alias.?Still the data is available with new name.?Once he restarts the TM1 server, the data is no longer available with the new name.??Customer claims that, once swapped the names, the data elements also should get swapped and hence it to be a bug.??Refer video attached and testcase description for details.

RECOMMENDATION:
Please upgrade to IBM Cognos TM1 10.1.1 or IBM Cognos TM1 9.5.2 FP3
Also, in the 10.1 Fix Lists, specifically the list of things which were fixed in 10.1.1:
PM65881 Function SwapAliasWithPrincipalName deletes data for alias with initial name, after server restart


I know I've always been pretty wary about using this function (not that I've really needed it), and if I recall that was the reason.

Though interestingly it still doesn't seem to be documented in the Reference Guide, other than in the reserved words list.
"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.
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: Documentation Deficiencies

Post by Alan Kirk »

Document: Reference Guide
Section/Chapter: TM1 TurboIntegrator Functions / Subset Manipulation TurboIntegrator Functions
Topic: SubsetIsAllSet
Versions: 9.5.2 to 10.2.2 at least

Deficiency
The following appears as the third argument:
Flag: Any non-zero value specifies that the subset uses all the current elements from the parent dimension and will dynamically update to use all elements from the parent dimension whenever the subset is called.
Specifying a zero value freezes the elements in the subset as the current set of all elements in the parent dimension. The subset will not dynamically update to use all dimension elements in the future.
Paragraph 1 is true. Paragraph 2 is not. If you execute this function with the Flag argument set to 0, it simply obliterates every member of the subset, leaving it empty.
"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.
User avatar
qml
MVP
Posts: 1094
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1 / Planning Analytics
Version: 2.0.9 and all previous
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: Documentation Deficiencies

Post by qml »

Alan Kirk wrote:Paragraph 1 is true. Paragraph 2 is not. If you execute this function with the Flag argument set to 0, it simply obliterates every member of the subset, leaving it empty.
I am unable to reproduce the behaviour you are describing with the two versions I have on hand: 10.2.2 FP4 and 10.1.1 FP2. For me it works exactly as documented. The steps I used to test it are as follows:
  • * Create a test subset manually via Subset Editor that does not contain all the elements of the dim.
    * Run SubsetIsAllSet with the flag set to 1.
    * Confirm the subset now returns all elements of the dimension.
    * Run SubsetIsAllSet with the flag set to 0.
    * Confirm the subset still returns all elements of the dimension.
    * Add one element to the dim.
    * Confirm the subset still returns all elements of the dimension except for the new one.
Kamil Arendt
Post Reply