Add Number Formats using TI

One of the most neglected and least prioritised activity among the grand scheme of development, in my opinion, is applying correct formats to data, both in cubes as well as standard web reports. In all fairness, if you’re one of my kind (occasionally-fall-into-a-slack type) who often miss out on this detail, then you have arrived at the right place. In my defence, I still feel that given the number of data points that one must apply formatting, it is only natural to miss a few, which then gets picked by business in live environment.

Well, this blog is all about how you save yourself from such embarrassments and I’m hoping by the time you are done reading, you would have understood how to easily apply number formats in a cube using a straightforward TI script.

Typically applies in a scenario where request has been made during the development of a new model; unchallenging then as the modeller can at all times update it manually using Architect in Dev environment and migrate the relevant object/s across to accomplish this during implementation.

This however becomes interesting when all the changes are expected to be migrated as a part of the TI script, thereby losing the liberty to migrate any .cub or .dim files.

Solution

Adding a script to the Promotion TI that is executed in a non-development environment enables application of the relevant formats, refer illustration below.

The Script:

sDim = 'Test Measure';

sAttrDim = '}ElementAttributes_'| sDim;

#-- Create control dim and cube in case there was no attribute previously created for the dimension

IF( DimensionExists( sAttrDim ) = 0 );

    DimensionCreate( sAttrDim );

    CubeCreate ( sAttrDim, sDim, sAttrDim );

ENDIF;

#-- Add Format element in case there was no formatting set for any element in the dimension

IF( DIMIX( sAttrDim, 'Format' ) = 0 );

     AttrInsert( sDim, '', 'Format', 'S' );

ENDIF;

sFormat = 'b:#,##0.00;(#,##0.00)CO|2|Y';

sEle = 'Value';

AttrPutS( sFormat, sDim, sEle, 'Format' );

Image 1 and 2 below capture “As-Is” and “After Apply” effects to the formatting

As-Is:

After Apply:

Tip: Using DimensionElementInsert as opposed to AttrInsert in the above code results in a numeric attribute created in the dimension. As we know Format attribute is a String Attribute, having a numeric attribute for format restricts the future formatting updates using the traditional manual method via Architect, so be vary of using that.

For more Information: To check on your existing Planning Analytics (TM1) entitlements and understand “how to”, reach out to us at info@octanesolutions.com.au for further assistance.

Get a free one-hour consultation on us.

Octane Software Solutions Pty Ltd is an IBM Registered Business Partner specializing in Corporate Performance Management and Business Intelligence. We provide our clients advice on best practices and help scale up applications to optimise their return on investment. Our key services include Consulting, Delivery, Support and Training.

Octane has its head office in Sydney, Australia as well as offices in Canberra, Bangalore, Gurgaon, Mumbai, and Hyderabad.

To know more about us visit, OctaneSoftwareSolutions.