Apliqo End UserWhite Papers & Technotes TechnotesPreparing your TM1 / Planning Analytics model for reporting via TM1 Rest API based user interfaces

Preparing your TM1 / Planning Analytics model for reporting via TM1 Rest API based user interfaces

Introduction

IBM Planning Analytics is not new, neither is the TM1 REST API. While the TM1 Server’s old APIs (C/C++ API, Java API, .Net API) have not yet been actively deprecated and old GUIs like Perspectives and TM1Web will likely be with us for a few more years, for a number of years now all GUI development has exclusively used the TM1 REST API. However, given the already quite long history of the TM1 REST API, we couldn’t find a single source anywhere advising TM1 model developers and administrators of common security pitfalls which need to be addressed in the back end in order for front end applications built with the TM1 REST API to work properly. This Technote seeks to fill this gap.

This article is written from the perspective of the TM1 developer or administrator. The language used is completely non-technical in terms of the TM1 REST API. There will be no discussion of OData, JSON, GET or POST operations, or for that matter any examples of REST http queries. However, we do assume a solid prior knowledge of how the TM1 Server security model works. All scenarios, both design and troubleshooting, are presented in a frame of access permissions required when using old APIs versus what is required when designing for front ends using the TM1 REST API.

The basic basics

The REST API doesn’t really care about the ServerName parameter. All that is needed is the server IP and port number allocated to the TM1 server instance combined with the protocol (http vs. https).

For your application to communicate with your TM1 Server via REST API you need to make sure a unique port across all services running on the host server is allocated. You do this by setting the HTTPPortNumber parameter in the tm1s.cfg file. If this parameter is absent from the configuration file then the default http(s) port value is 5001. The HTTPPortNumber parameter works in conjunction with the UseSSL parameter (default value =true). If UseSSL=true then the TM1server will accept only secure https connections. You can set UseSSL=false but SSL connection is advisable on any production server, especially those exposed on public gateways. It is also highly advisable to replace IBM’s default SSL certificate with a custom one.

Points to remember:

  • The TM1 admin server uses the following tcp/ip ports which must be unblocked
    • 5495 & 5498 for non-http(s) (old APIs)
    • 5895 & 5898 for http(s) (REST API)
  • The HTTPPortNumber parameter must be set to a unique value for each TM1 instance. These ports must also be unblocked in firewall settings
  • If changing the http port you will need to recycle the TM1 instance since this parameter is static

Recap: tenets of TM1 security

As stated above an existing knowledge of how TM1 security works is assumed. So we won’t be covering basics such as the additive nature of TM1 security or any special cases such as interaction between element and cell security, the effect of reserve and hold, or security overlay. All these concepts are unchanged, what’s different with the TM1 REST API is the requirement for additional object permissions versus “traditional” interfaces built with the old APIs.

Cube security

To enable READ access to a cube a group requires:

  • READ access to the cube in }CubeSecurity
  • READ access to each dimension in the cube in }DimensionSecurity

To enable WRITE access to a cube a group requires:

  • WRITE access to the cube in }CubeSecurity
  • READ access to each dimension in the cube in }DimensionSecurity

Dimension security is generally considered optional. In systems without dimension security in place (i.e. without a }DimensionSecurity cube) the default access level for all groups to all dimensions is READ. In systems with dimension security applied, if (and only if) security is maintained 100% manually, we can effectively forget about dimension security as the server will automatically set READ access to all dimensions in the cube for all groups with at least READ to the cube. However, in most real-world models things are a bit more complicated and security will be partly or completely automated. Once dimension security is applied, and especially if there is any automation involved then we must make sure that READ access to dimensions is in place or else cube access will fail. We will get back to this later.

Dimension security

WRITE” access to a dimension doesn’t mean the ability to edit a dimension, but rather the ability to edit attribute values. Or in other words, WRITE access to the }ElementAttributes_ cube of the dimension.

Cube drills

In order for cube drills to work and for users to see the drill context menu, groups who should have access to the drill (generally, but not always an exact overlap of users with at least READ access to the cube) must have READ access to each }Drill­_ process referenced in the }CubeDrill_ cube.

PickLists

For dimension and subset PickLists, the group must not only have WRITE access the cell to which the PickList is applied, but also READ access to the dimension used by the PickList. Don’t forget that element security applies to PickLists; users will see only the elements in the list to which thy have at least READ access.

If a }PickList cube is used rather than (or in addition to) a PickList attribute then READ access to the }PickList cube and }PickList dimension is also required. Without this users will not see PickLists originating from the }PickList cube.

Changes: what is different if using the REST API

All of the above still holds true. However, if any REST API based user interfaces are being used end users now require additional object permissions which were not previously needed with the old APIs. For example:

Additional security permissions

In addition to setting cube access and READ access to each dimension in the cube for the TM1 REST API users now also require:

  • READ access to the }ElementAttributes_ dimension and }ElementAttributes cube for each dimension in the cube
  • READ access to the }CubeDrill_ cube and }CubeDrillString dimension (should drill-through be defined for the cube)

Note:  strictly speaking explicit access to the attribute cube and attribute dimension isn’t absolutely necessary. A Rest based UI should still work without this, but things will work better and faster with this access. (This is due to a Rest API & OData quirk on requesting a single attribute value versus all attribute values for a given dimension.) And don’t forget that for many features MDX will be used, and quite often in advanced MDX explicit reference is made to attribute cubes. Without this access there will almost guaranteed be features that won’t work properly.

Note: the requirement for explicit access to }CubeDrill cubes depends on the version of the TM1 server (and Rest API) and/or the version of PA Workspace. Earlier versions needed this, the later ones don't. For safety and backwards compatibility it is safer to grant the permission.

Reminder: that as for the C based interfaces READ access to the }PickList_ cube and }PickList dimension (should the }PickList_ cube exist) are still required for REST based UIs. (No difference here, but don't for get to set these permissions!)

Note: there is no requirement to grant users READ permission to }CellSecurity_ or }CellAnnotations_ cubes in order for these features to work properly.

Explanation

The first several versions of the TM1 REST API supported only MDX Select queries and did not support MDX Set Expressions. Therefore the “native” way to query a dimension via the REST API is actually to query the dimension’s }ElementAttributes_ cube. In the initial versions of PAW and Apliqo UX the subset editor would fail if a dimension contained no attributes and hence the }ElementAttributes_ cube was absent. Obviously, this functionality gap has since been closed but due to code legacy and backwards compatibility the default query method is always to use the }ElementAttributes_ cube if it exists. In any case without READ access to the respective attribute cube users will be unable to see any attribute values.

Object names

When building a model, the TM1 / Planning Analytics developer needs to think beyond the "back end" to how the model will be consumed. The TM1 REST API is communicating in http(s), using a specific JSON format specified by the OData v4 standard. This means the TM1 model developer needs to be aware of html character entities, escape characters and the allowable characters in the OData specification. Although in general all characters are possible, this comes at the expense of extra complexity and processing logic in the front end application to deal with non-standard characters. This has been discussed previously on the Cubewise tech blog. Special characters should be avoided wherever possible and all object and attribute names limited to a-Z, 0-9, underscore and space characters only. (For attributes also avoid space character since in the OData standard spaces are not allowed in member property names).

Model design considerations

This leads to some recommended best practices when designing models which will be consumed via a TM1 REST API interface:

  • Limit object principal names and attribute names to standard characters only. (a-Z, 0-9, underscore and space)
  • All model dimensions should contain at least one attribute to ensure the existence of an }ElementAttributes_ cube
  • Should a dimension otherwise have no attributes we recommend creating a “Caption” attribute for the dimension as type “String”

Many people don’t realize but the Caption attribute doesn’t have to be an alias. In fact it is better practice to make Caption a string attribute as this will give you more flexibility and allow the application to be more user friendly. Even if values are not unique Caption attributes work much the same as aliases for display purposes; if no caption is set then the principal name will be displayed, otherwise the caption.

Other extra permissions

In addition to the attribute cubes for each dimension it is advisable to also explicitly grant users READ access to the }Cubes and }Dimensions dimensions (and to the }ElementAttributes_}Cubes and }ElementAttributes_}Dimensions cubes should they exist). The reason being is that these are the objects which will be queried in “report building” or “cube viewer” type UIs like the examples below.

Selecting a cube as a data source for a report requires access to the }Cubes dimension and the }ElementAttributes_}Cubes cube should it exist.

Selecting a dimension or hierarchy to add to a page requires access to the }Dimensions dimension and the }ElementAttributes_}Dimensions cube should it exist.

Toolbox

In preparing your model for use with a TM1 REST API based user interface you may find the following TurboIntegrator code snippet useful. Based on the assignment of cube security the snippet will make sure that permissions are set for all PickList and Drill objects and all dimensions associated with the cube. Even in a large model the following code should execute quickly.

Code to automatically set dependent permissions

cCubeSec        = '}CubeSecurity';

cDimSec         = '}DimensionSecurity';

cCubeDim        = '}Cubes';

cGroupDim       = '}Groups';

nCubes          = DimSiz( cCubeDim );

nGroups         = DimSiz( cGroupDim );

# loop cubes

iCube           = 1;

While( iCube    = nCubes );

   sCube       = DimNm( cCubeDim, iCube );

   sCubePL     = '}PickList_' |sCube;

   sCubeDr     = '}CubeDrill_' |sCube;

   # loop Groups

   iGroup          = 1;

   While( iGroup   = nGroups );

       sGroup      = DimNm( cGroupDim, iGroup );

       sSecLvl     = CellGetS( cCubeSec, sCube, sGroup );

       If( sSecLvl @<> '' & sSecLvl @<> 'NONE' );

           # cube security for PickList  Drill cubes

           If( CubeExists( sCubePL ) = 1 );

               CellPutS( 'READ', cCubeSec, sCubePL, sGroup );

               CellPutS( 'READ', cDimSec, '}PickList', sGroup );

           EndIf;

           If( CubeExists( sCubeDr ) = 1 );

               CellPutS( 'READ', cCubeSec, sCubeDr, sGroup );

               CellPutS( 'READ', cDimSec, '}CubeDrillString', sGroup );

           EndIf;

           # loop dimensions in cube

           iDim        = 1;

           While( iDim = CubeDimensionCountGet( sCube ) );

               sDim    = TabDim( sCube, iDim );

               sDimAttr= '}ElementAttributes_' | sDim;

               CellPutS( 'READ', cDimSec, sDim, sGroup );

               If( CubeExists( sDimAttr ) = 1 );

                   CellPutS( 'READ', cDimSec, sDimAttr, sGroup );

                   CellPutS( 'READ', cCubeSec, sDimAttr, sGroup );

               EndIf;

               iDim    = iDim + 1;

           End;

       EndIf;

       iGroup      = iGroup + 1;

   End;

   iCube       = iCube + 1;

End;

Note: if your model has no dimension security applied then comment out all lines writing to the }DimensionSecurity cube.

Don’t forget to perform a SecurityRefresh after running the code above.

Troubleshooting (Generic)

Here are some common troubleshooting scenarios and likely solutions to assist in rectifying permissions issues with your application.

Subset editor is not working for a particular dimension
  • }ElementAttributes_ cube does not exist for the dimension. Solution: create Caption attribute
    AttrInsert( 'DimName', '', 'Caption', 'S' );
  • User does not have READ access to }ElementAttributes_ cube for the dimension
User is not offered correct drill-through context
  • User does not have READ access to relevant }CubeDrill_ cube
  • User does not have READ access to relevant }Drill_ processes references in the rules of the drill cube
  • User does not have READ access to the }CubeDrillString dimension
PickList does not display for user, but cell is writeable
  • User does not have READ access to relevant }PickList_ cube
  • User does not have READ access to the }PickList dimension
  • PickList is type “Dimension” or “Subset” and user lacks READ access to the dimension referenced in the PickList
  • PickList is type “Dimension” or “Subset” and user lacks at least READ access to any elements in the PickList (in the case that the referenced dimension has element security applied)
User is unable to add additional dimensions or hierarchies to a view

“Add dimension” dialog displays but dimension dropdown list is empty

  • User does not have READ access to }Dimensions dimension
  • User does not have READ access to }ElementAttributes_}Dimensions cube (should the cube exist)
User is unable to create a new widget or change cube data source of existing widget
  • User does not have READ access to }Cubes dimension
  • User does not have READ access to }ElementAttributes_}Cubes cube (should the cube exist)

Troubleshooting (Apliqo UX specific)

“Edit Mode” button does not appear for the user on a public view
  • User has not been assigned the report builder role in the ContentStore instance (“APQ PUser” group)
“Edit Mode” button appears but user is unable to save changes
  • The app has been protected to prevent accidental changes. Solution: unprotect the app in the “App Management” console then save the changes.
  • The user’s access to the screen is restricted to “report consumer” level as the user only had READ and not WRITE access to the screen due to element security assignments to the }APQ UX App dimension
On login user is redirected to App Management console
  • The user’s defined homepage does not exist
    OR
  • The user the user has no security rights to the defined homepage
    AND
  • The user is assigned the report builder role in the ContentStore instance (“APQ PUser” group)
On login user is redirected to blank screen

On login the user sees the “AppBar” navigation bar but an empty screen below (no dashboard renders)

  • The user’s defined homepage does not exist
    OR
  • The user the user has no security rights to the defined homepage
    AND
  • The user is assigned only the report consumer role in the ContentStore instance (“APQ User” group)
User is unable to create private views
  • The user uas no value set for the }TM1_DefaultDisplayValue alias in the }Clients dimension
  • The }APQ UX Client dimension has not been synced to the Content Store's }Clients dimension and the user does not exist in the }APQ UX Client dimension
  • User is logged in with the built-in Admin account. The Admin account is excluded from creating private views. Log in with a personal user account

0 Comments

Add your comment

E-Mail me when someone replies to this comment