One of the interesting facts of TM1 is that it is case and white space insensitive, that is, it ignores all white space and case in objects names. You can reference an element called "Operating Profit" in rules and TI as "OperatingProfit", "operatingprofit", "OPERATINGProfit" or even "O p E r A t I n g P r O f i T" (just to be different). Have you ever tried to add a space to make an Alias unique? It doesn't work for this very reason. It applies all objects including elements, dimensions & cubes names.

These flexible naming rules enable you to use logical names for you objects instead using underscores or hyphen's (i.e. Operating_Profit) that are the norm in SQL or other programming languages. This makes it easier to read and more user friendly to your users.

NOTE: When using object names in Excel it is best to use their full names with spaces as there have been issues with some formula's, especially those relating to Active Forms.

Reference p52 TM1 API Guide 9.5.2

A quick step guide to set-up TI to capture runtime information

It is important for all TI process to capture Runtime information like start time, end time, Process time, no of records etc. This information is very useful for the following reasons:

• Useful information for administrators/developers for Scheduling of Processes and chores

• Tracking Process time across time periods

• Informing end users of reports on latest updates on various TM1 objects with a published report

In order to capture this information, it is a best practice to set these up through Ascii outputs during the process run time and then running another process on the Ascii output to record the information to a Sys - TI Info cube.

The key reason to have this as Ascii outputs as against writing straight to a cube are:

• Avoid locking of other TM1 objects than the one that is being updated ( Black Belt)

• General Performance of TI is better

• Ascii outputs can be used by IT or other departments to develop their own reports

It is for the above reasons that we have to make sure Dimensions and Cubes are not updated as part of the same process.

Step 1: Define Variables and capture information in Prolog

Define the key variables required in the Prolog of the process

#Start Counter for Process Details

iProCount = 0;

#Define TM1 Object Variables

cCube = '';

or

cDim = '';

cChangeObject = cCube or cDim

cProcessName = GetProcessName;

#Define Process information Variables

#Define Global Variables

NumericGlobalVariable('DataMinorErrorCount');

NumericGlobalVariable('MetaDataMinorErrorCount');

#Define Start Time

vStartTimeN = NOW;

#Convert Start time to useful syntax

vStartTimeS = TimSt (StartTimeN,'\D-\M-\Y \h\i\s');

#Output Flat File Syntax

cFilePath = GetProcessErrorFileDirectory;

or can be a defined path in the Sever control or system information cube

cFileNamePro = 'TIInfo'| vStartTimeS |cProcessName |'Prolog - Process Details.txt';

cFilePro = cFilePath | cFileNamePro;

#Set the Header Row

AsciiOutPut ( cFilePro , 'Time', 'Object' ,  'Measure' , 'Value');

#Output required info

AsciiOutPut ( cFilePro , vStartTimeS, cProcessName,  'Start' , vStartTimeS);

AsciiOutPut ( cFilePro ,vStartTimeS, cChangeObject,  'Start' , vStartTimeS);

This would output the information file from Prolog to the nominated directory

PS: Make sure the Process record counter in updated in Metadata and/or Data tabs

#Record counter

iProCount = iProCount + 1;

Step 2: Define Variables and capture information in Epilog

Define the key variables required in the Epilog of the process

#Define Epilog Variables

vEndTimeN = NOW ;

vEndTimeS = TimSt (vEndTimeN,'\D-\M-\Y \h\i\s') ;

vRunTime = TRIM(STR(vEndTimeN - vStartTimeN,10,5) ) ;

#Convert Runtime to natural time clock

vRuntimeSec = (StringToNumber ( vRunTime ) * 100000) \ 1.15740767796523 ;

#Covert and Calculate the Process information

vsMetaDataMinorErrorCount = NumberToString ( MetaDataMinorErrorCount );

vsDataMinorErrorCount = NumberToString ( DataMinorErrorCount );

vsiCount = NumberToString ( iCount);

vsRuntimeSec = NumberToString ( vRuntimeSec);

#Define File Path and File Name

cFilePath = GetProcessErrorFileDirectory;

cFileNameEpi = 'TIInfo'|vStartTimeS|cProcessName|'Epilog - Process Details.txt';

cFileEpi = cFilePath | cFileNameEpi;

#Output required info

#Header Row

AsciiOutPut ( cFileEpi , 'Time' , 'Object' ,  'Measure' , 'Value');

AsciiOutPut ( cFileEpi , vStartTimeS ,cProcessName,  'End' , vEndTimeS);

AsciiOutPut ( cFileEpi , vStartTimeS ,cProcessName,  'Runtime' , vRunTime);

AsciiOutPut ( cFileEpi , vStartTimeS ,cProcessName, 'META ERROR COUNT' , vsMetaDataMinorErrorCount);

AsciiOutPut ( cFileEpi , vStartTimeS ,cProcessName,  'DATA ERROR COUNT' , vsDataMinorErrorCount);

AsciiOutPut ( cFileEpi , vStartTimeS ,cProcessName,  'No of Records' , vsiCount);

AsciiOutPut ( cFileEpi , vStartTimeS ,cProcessName, 'RuntimeSec' , vsRuntimeSec);

AsciiOutPut ( cFileEpi , vStartTimeS , cChangeObject, 'End' , vEndTimeS);

AsciiOutPut ( cFileEpi , vStartTimeS , cChangeObject,  'Runtime' , vRunTime);

AsciiOutPut ( cFileEpi , vStartTimeS, cChangeObject,  'META ERROR COUNT' , vsMetaDataMinorErrorCount);

AsciiOutPut ( cFileEpi , vStartTimeS , cChangeObject, 'DATA ERROR COUNT' , vsDataMinorErrorCount);

AsciiOutPut ( cFileEpi , vStartTimeS , cChangeObject,  'No of Records' , vsiCount);

AsciiOutPut ( cFileEpi , vStartTimeS , cChangeObject, 'RuntimeSec' , vsRuntimeSec);

Step 3: Set up Sys  - TI Info cube

The dimension structure for the cube would be

Sys - TI Time , Sys - TM1 Object , Sys - TM1 Object Type , Sys - TI Info Measures

Step 4: Setup a generic Process to Load data from Ascii output files to the Sys - TI Info cube

This process should have

• File source defined as a parameter and the

• DatasourceType definition as CHARACTERDELIMITED to automatically change file source.

• In the MetaData and Data tabs update Object type based interrogation of control objects (}Cubes, }Dimensions,}Processes)

• Execute Process to move the file used to a back up directory ( batch Process)

Step 5: Create a wild card file search process

The next step in the process is to create a Wild Card File Search process that cycles through the nominated TI Process info file directory and passes the file name to the Process in Step 4

Step 6: View results in the Sys - TI Info Cube

The results are now available in the Process Info Cube that can sliced and diced by object types and measures across time periods.

It is essential that this Syntax is used in all processes to make sure all processes runtime details are logged.

A simple way of managing TM1 Excel worksheets across multiple server environments

Many larger companies using TM1 operate development, user testing and production environments with separately named TM1 server instances, for example; TM1_Dev,  TM1_Test and TM1_Prod.  Migrating TM1 server objects across servers is relatively straightforward as no object changes are required. However with reports and data entry templates developed in Excel it is usually a different matter as the server name reference will need to be replaced.  Often this is a tedious and manual job that involves a developer of admin manually opening files and performing a find and replace (or developing a VBA macro or windows script to automate this task.)  This violates the separation of environments principle as a report that has passed UAT must be changed  in order to function in production.

Well it doesn't have to be that way.  What follows is a simple method to share and promote workbooks between named servers with no requirement to find/replace server names at all.

All that is required is prior knowledge of the number of environments and the TM1 server name of each environment and the TM1User function.

Steps:

1.       For each environment have a cell or named range in the worksheet to hold the server name

2.       For each environment have a matching TM1User function linking to the appropriate cell containing the server name. If the user is connected to a server he function will return the TM1 client name, otherwise is will return an empty string

3.       Have a named range "pServer" which contains an IF test to see if the TM1User functions have returned values

4.       Replace any hardcoded server name references in TM1 formulas with pServer&":

The following two screen shots show the cells that are required and the formula logic:

Additional cells required

Note the highlighted cell is a named range called "pServer"

Formulas in each cell

Note in other TM1 Excel formulas (SUBNM, VIEW, TM1RptView, TM1RptRow, etc.) hardcoded server name strings are replaced with a reference to the pServer named range.

The workbook will now be capable of being passed between environments with no further need for any modifications. This technique is suitable for any customer using TM1 across multiple servers where each instance of TM1 is running under a separate TM1 server name.

Did you know that TM1 has Financial Functions like the ones in Excel? The three functions are: PV, PAYMT and FV.

The table below gives an overview of what each function does. If you are interested in a further, more detailed explanation of the functions , then please read further.

NB: If periods are in months and the interest rate is per annum, simply divide the interest rate by 12.

As an example, the screenshot below shows a cube which has a rule using the PMT function to calculate interest payments on loans starting in different months. With TM1 you can calculate the total interest in a month from loans of various ages (e.g. loans that are just in their first month all the way up to loans that started years ago). By using one time dimension for when the loan started and another time dimension for when interest is paid, the cube can isolate a single loan that would have been lost in a complicated Excel spreadsheet trying to do the same "triangulation". As well as consolidating the interest paid in the month, TM1 can readily consolidate the payments off the principal in the month and the total money loaned out in any given month.

PART 2: The Maths behind the Financial Functions

If you're going to use TM1's Finance functions, it's important to know exactly what calculations are going on behind the scenes. So, for those interested in what the functions are actually doing please read on...

Example of using PV to do NPV calculations

When doing investment appraisal it is common to calculate the Net Present Value of an investment. That is, the value in today's dollars of the cash that will be generated in future years less the cost of the investment.

The PV function can be used to calculate the Present Value of a constant stream of cash going in or out at the end of each year.

For example, a business can borrow money at an interest rate of 10% p.a. The business has an opportunity to buy a machine for $250,000 that will reduce costs by $100,000 at the end of each year for the next 3 years but will be worthless after that. Should it buy the machine?

PV (amount going in or out at the end of each period, interest rate, periods) = Present Value

PV(100000, 0.1, 3) = $248,685

The present value of all the savings is only $248,685. That means the benefit is less than the cost of the machine so the business shouldn't buy it.

What does the function actually do?

It multiplies the outflow at the end of each year by the relevant discount factor then adds them all together.

The value of the $100,000 saved at the end of the first year is only worth $90,901. That's because if you invested $90,901 now it would be worth $100,000 in 1 year's time. $90,901 + 10% interest = $90,901 x (1 + interest rate) = $100,000. The formula to get $90,901 is:

1 / (1 + interest rate) x $100,000 = 0.9091 x $100,000 = $90,901.

The value of the $100,000 saved at the end of the second year is only worth

1 / (1 + interest rate)² x $100,000 = 0.82645 x $100,000 = $82,645

The value of the $100,000 saved at the end of the third year is only worth

1 / (1 + interest rate)³ x $100,000 = 0.75131 x $100,000 = $75,131

The PV function then adds $90,901 + $82,645 + $75,131 = $248,685

Example of using PAYMT to calculate loan repayments

The PAYMT function can be used to calculate the repayments needed on a loan or the returns needed to justify an investment.

For example, a business takes out a 3 year loan on a $250,000 truck at an interest rate of 10%. If repayments are made at the end of each year, what will the repayment be?

PAYMT(principal, interest, periods) = recurring repayment

PAYMT($250000, 0.10, 3) = $100,529

What does the function actually do?

Example of using FV to calculate investments

The FV function can be used to calculate what a periodic investment will accumulate to over time, using compound interest. That is, what the future value will be.

For example, a business invests $100,000 in its pension fund at the end of each year at an interest rate of 10%. What will the fund be worth after 3 years?

Future Value = FV(Recurring Payment at period end, Interest Rate, Number of periods)

Future Value = FV($100000, 0.1, 3) = $331,000

What does the function actually do?

In year 1, there is no interest because the payment is at year end.
In year 2, the initial investment is worth $100k + 10% = $110k
In year 3, the initial investment is worth $110k + 10% = $121k
Accumulate the 3 separate investments in this way and the total is $331,000.

The formula for accumulating each individual investment made at year end is:
Investment x (1 + interest rate)^{(Periods -1)} = $100,000 x (1 + 0.1)^(3-1) = $121,000

To accumulate all the recurring investments the formula is:

Cube optimisation can be done a number of ways:

• Using the inbuilt re-order dimensions in TM1 (9.5.1 and above only) or
• Using Vizier (http://cubewise.com/vizier-91.php) which will always find the optimal order. For more information on Vizier and its many other features please contact Cubewise (http://cubewise.com/contact-us-7.php)
• Manually using the "re-order dimensions" right-click menu option from the cubes object tree in server explorer

Cube optimization / dimension re-ordering is only suitable for numeric only cubes. If the cubes contain string data then the last dimension can't be changed and therefore there will be minimal benefit as the last dimension is the most important by far for cube optimisation.

Optimising dimensions in a cube structure does not change the actual order of dimensions in the cube structure; it is an internal server change so rules, reports and anything referencing the cube is not affected.

The method described below is relevant in the following instances:

• You do not have Vizier; or
• Cube sizes are so big that time and system resources required to find the optimum order via an iterative algorithm is out of the question

Generally speaking optimising dimension orders in a cube can prove to be a time consuming exercise based on the recommended optimal ordering of smallest sparse to largest sparse, followed by smallest dense to largest dense dimensions.  From our experience, Cubewise has found that 90% of the gain comes from placing the correct dimension as the last dimension in a cube.  In all documentation to date however there is rarely any mention of methods of determining what the last dimension should be!

In terms of what constitutes a "large" dimension and a "dense" dimension in terms of the optimal order of "small sparse to small dense followed by large sparse to large dense," this is somewhat of a grey area, a "large dense" dimension and therefore the candidate as the optimal LAST dimension may only have a few hundred elements, hence it may not be what we would usually think of as being a "large" dimension. 

For the purposes of a useable rule of thumb a "large dense dimension" is where there are more than 75% of populated N level elements with more than 200 N elements in the dimension.  The "largest densest" dimension for the purposes of optimising a given cube will depend on the relative size and density of other dimensions in the cube.  Provided the "large" dimensions are over a certain size generally density is the more important factor in determining the optimal last dimension.

During the re-ordering process generally allow for a temporary RAM increase on the TM1 server by a factor of two for the cube that you are re-ordering.  Where the dimension order is better than the current order, gains of up to 80-90% can be made on the current cube size (but likewise if the order is worse the result could be a 10 fold increase versus current cube size.)  Therefore cube optimisation should only be attempted with adequate memory resources on the server, otherwise you risk causing an out of memory crash of TM1. 

Time to spill the beans - here is a pretty simple method of calculating optimal dimension order using Excel and TM1.

 1       Calculate the number of N level elements within a dimension

• Create a 0 level subset within the dimension
• In Excel use the SUBSIZ formula SUBSIZ(Dimension,SubsetName)
• This will give you the number of N level elements in the dimension
• Note: The measure used to calculate density for the other dimensions should be a data input measure not a rule calculated measure

2       Calculate the number of populated elements within each dimension in the cube excluding the measures dimension

• Construct a view with the dimension to test as the row subset using the level 0 subset
• Have no column subset and ensure the other dimensions are "Total ....."
• Select a measure element that you know has data e.g. Total Sales or Net Profit
• Zero supress the view
• Snapshot to Excel and perform a count on the number of rows

3       Calculate the % used

• Divide the number of populated elements by the number of n level elements in the dimension and this will give you the % of populated cells
• Do this for each dimension

4       Analyse results

• Rank/Sort the dimensions by density then by size. Exclude small dimensions. The remaining dimensions at the top of the list will give you an idea of what the best order for the dimensions should be.

The large dimension with the highest density should be the last dimension in the cube.  You can repeat to determine the 2^nd last dimension, 3^rd last dimension etc. but be aware that there are diminishing returns by an approximate factor of 10. Thus if 90% of the gain is from correctly positioning the last dimension there is only a 9% gain from the optimal 2^nd last dimension and there is rarely any point continuing beyond this point.