MDX Libraries are centrally stored Multidimensional Expressions for use in member selection of subsets. When library entries are used in subsets, they are linked at run-time to the MDX expression, meaning, if a change is made to a library entry, all subset using that MDX will reflect the changes to the MDX expression the next time the subset is used.
When you select an MDX library entry from the navigation pane, you will be presented with a screen to enter or update the MDX expression as shown below:
Creating MDX Library Entries
Creating Library Entries simply amounts to typing or pasting an MDX expression into the text area and saving it with a name that can be used later. In many cases, the MDX expression requires context to be useful. This context can be supplied with the use of tokens. Tokens are keywords enclosed in percent signs (%). When TM1Connect encounters a token, TM1Connect will require the parameter to be specified when the library entry is used.
Specifying tokens in MDX statements
Tokens can be used anywhere within the MDX expression, including one that requires an MDX expression to operate, however, a token cannot reference another library entry. You can use any name for the token with the exception of %dimName%. When TM1Connect encounters %dimName% during a query, TM1Connect will replace the token with the current dimension name. This allows for a single MDX expression to be used with multiple dimensions.
Consider the following MDX Library entry called Commisioned Employees:
FILTER(
{ TM1FILTERBYLEVEL( {TM1SUBSETALL( [Employee] )}, 0)},
[Employee].(
[Business Unit].[%BusinessUnit%],
[Period].[%Year%],
[Employee].CurrentMember,
[Employee Measure].[Commission] ) > 0 )
)
When using this MDX library entry in a subset, TM1Connect will display the following:
This library entry can now be used in any view that uses the Employee dimension to restrict the list of employees to only those that are commissioned within a specific year and business unit.
|
BEST PRACTICE: Unless you are proficient at writing MDX statements, it is often helpful to enable recording in the TM1 Subset editor to build and test the MDX statement first. Once it has been validated, the generated MDX statement can be pasted in this input area. |
|
CAUTION: If you use the expression recorder in TM1 Subset Editor to generate the MDX statement, it is important to replace the function TM1SubsetBasis() with TM1SubsetAll() or some other MDX expression. TM1SubsetBasis() function assumes you are starting with a subset already defined in TM1 to operate according to the settings of the saved subset (such as currently displayed alias name). TM1SubsetBasis() has no foundation with a custom MDX statement in TM1Connect, so it may not return the desired results. |
Deleting MDX Library Entries
To delete a library entry, click on the entry to be deleted from the navigation pane and press the Delete button. This will remove the library entry from TM1Connect but it will not update any subsets using the entry. If a subset is requested which attempts to access the deleted entry, an error will occur.