It's a common reporting requirement for rows in a report to be in a specific order, at the same time as being dynamic when new elements are added to a dimension. It may not be obvious how to achieve this, but it's relatively easy with a bit of MDX in an Active Form.  All that's required to sort an Active Form using an attribute is an attribute populated with the required sort order and a crafted MDX statement to use in the TM1RptRow function. The MDX statement to sort an existing subset using an attribute can be as simple as

{ ORDER(

  { TM1SubsetToSet( [Account] , "EBIT Accounts" ) } ,

  [Account].[ReportSortOrder] ,

  BASC ) }

This MDX statement will sort the "EBIT Accounts" subset in the "Account" dimension based on the values of the "ReportSortOrder" attributes in the "Account" dimension.

If using MDX in Active Forms is new to you then check out this previous tech tip LINK that explains how to use MDX in Active Forms.

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: