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.

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:

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:

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

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):

Initialise the API by calling the TM1APIInitialize function;

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;

Set the Admin Host that the user handle will be using via the TM1SystemAdminHostSet function;

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;

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;

Create three value capsules which hold strings (text) by passing the following arguments to the TM1ValString function:

The pool handle created above;

The string that is to be contained in the value capsule; and

The value capsule containing the integer which specifies the maximum length of the string.

These three string value capsules will store the login, password and server name respectively;

Connect to the server (and obtain a handle to it) by passing the following arguments to the TM1SystemServerConnect function:

The pool handle;

The value capsule which contains the server's name;

The value capsule which contains the TM1 user's name (technically, the "client name"); and

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:

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?

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.

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)

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.

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.

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:

ArgumentDescriptionDimension: 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:

ArgumentDescriptionDimension: 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:

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

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.

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.

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.

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)

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

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:

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.

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.

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.