TM1

Did you know there may be several situations during development where you may want to clear all of the data from a specific cube? This could be due to a new process to reload data, a second round of user testing, or a migration from DEV to PROD. 

There are a few approaches that can be used to clear data from a cube.  One approach is to create of a view which consists of the entire cube and using then performing a ViewZeroOut against that view. This can be done via the following script:

# Define cube name

strCube = ‘Finance’;

# Destroy any existing views and recreate a new one

IF (VIEWEXISTS (strCube, strView) <> 0);

VIEWDESTROY (strCube, strView);

ENDIF;

VIEWCREATE (strCube, strView);

# Perform Zero Out

VIEWZEROOUT (strCube, strView);

The benefit to this approach is that you now have flexibility to manipulate your view. You can have the view filter to specific elements within a dimension and/or set the view to ignore values such as rule-based values.

Another approach is to use the CubeClearData command. The CubeClearData command clears all data within a cube. Using this function is faster than writing a script at the expense of using an “all or nothing” approach. The same process defined above can be written using the following single line:

# Define cube name

strCube = ‘Finance’;

# Clear all data from the cube

CUBECLEARDATA (strCube);

Note that the CubeClearData command also clears any cells in the cube that are fed with feeders. If you want to restore the fed cells then you must either manually resave the rule that establishes the feeders or use the CubeProcessFeeders function.

There are multiple ways that can be used to clear data from a cube and you can define what approach is best for you.

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Learn more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Creating Selectors in Planning Analytics Workspace

IBM Planning Analytics Tips & Tricks: Creating Buttons in Planning Analytics Workspace

IBM Planning Analytics Tips & Tricks: Synchronizing Selectors in Planning Analytics Workspace

Need more guidance? Take a look at our IBM Planning Analytics Training services and our Customer Care Program.

 

Did you know one of the great things about Excel is that there are many built-in functions that calculate values associated with specific industries? One of these industries is the financial industry where Excel is sometimes used to calculate values associated with annuities. But did you know that some of these formulas are also built into Planning Analytics for use within rules?

Here are three annuity formulas that can be used in Planning Analytics rules:

The FV formula returns the value of an annuity at the time of the last payment.

           FV(payment, interest, periods)

Payment = The amount of the payment made per period.

Interest = The interest rate paid per period.

Periods = The number of periods in the annuity.

The PV formula returns the initial or principal value of an annuity.

           PV(payment, interest, periods)

Payment = The amount of the payment made.         

Interest = The interest rate paid per period.

Periods = The number of periods in the annuity.

The PAYMT formula returns the payment amount of an annuity based on a given initial value or principal, an interest rate, and a number of periods.

            PAYMT(principal, interest, periods)

Principal = The present value (total amount) that a series of future payments is worth now.

Interest = The interest rate paid per period.

Periods = The number of periods in the annuity.

All of these formulas assume that payments are made at equal intervals of time (when applicable) and that they are made at the end of each period. 

Excel has a lot of great built in functions, so why can’t Planning Analytics?  Well, as you can see … it does!

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Learn more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Creating Selectors in Planning Analytics Workspace

IBM Planning Analytics Tips & Tricks: Creating Buttons in Planning Analytics Workspace

IBM Planning Analytics Tips & Tricks: Synchronizing Selectors in Planning Analytics Workspace

Need more guidance? Take a look at our IBM Planning Analytics Training services and our Customer Care Program.

 

Did you know one of the great things about creating TM1 Web sheets is that you simply need to create an Excel sheet and then press a button called Publish? This will convert your Planning Analytics formulas into a web-based interface that your users can easily access.

In addition to the Planning Analytics formulas, IBM is continuing to expand the use of Excel’s native formulas.  Planning Analytics version 2.0.7 introduced the use of the IFERROR formula into TM1 Web sheets.

The IFERROR formula determines if an error exists and then returns an alternative value, such as a text message, in place of the error message.  The error checks include any of the following results: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!

=IFERROR (value, value_if_error)

Here is an example of the IFERROR formula within a spreadsheet:

=IFERROR ( A1/B1, “There is an error in the calculation”)

The result of this formula will eliminate hard-to-read results such as #N/A or other results that could make your Web sheet less user friendly.

IBM continues to expand the Excel formulas that can be used in TM1 Web and Revelwood will continue to keep you updated with these additions.

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Learn more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Creating Selectors in Planning Analytics Workspace

IBM Planning Analytics Tips & Tricks: Creating Buttons in Planning Analytics Workspace

IBM Planning Analytics Tips & Tricks: Synchronizing Selectors in Planning Analytics Workspace

Need more guidance? Take a look at our IBM Planning Analytics Training services and our Customer Care Program.

Did you know that rules are used in TM1 to perform live calculations without the need for any manual intervention or on-demand scripts? This is a very powerful aspect of Planning Analytics and, when used properly, increase the value of the tool for your users.

There are some aspects of rules that I often see people forgetting. Here are some friendly reminders about how to use these components properly:

Division

TM1 offers two different approaches to write a division formula via the “slash”. Not only do I often forget the difference between the two slashes, but I also sometimes forget the names of each slash.

/

The forward slash; goes bottom left to top right

Division by zero using this operator returns an undefined value

\

The back slash; goes top left to bottom right

Division by zero using this operator returns zero

Mathematical Comparisons

TM1 allows for mathematical comparison of numbers using standard nomenclature such as =, >, < and <>. However, TM1 also offers the ability to compare strings via the same functionality. You can compare strings by simply adding an @ symbol to the nomenclature.

Numeric check:  IF ( numValue = numCounter )

String check: IF ( strValue @= strCounter )

And, Or, Not

When writing comparisons, we often use the AND concept and the OR concept.  AND is written by using the ampersand symbol ( & ) and OR is written by using the percent symbol ( % ). But did you know that you can also write NOT by using the tilde ( ~ ).

~( x = 5 ) is the same as writing ( x <> 5 )

Rules are a very powerful component of Planning Analytics. Hopefully these components help you continue creating efficient code.

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Learn more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Creating Selectors in Planning Analytics Workspace

IBM Planning Analytics Tips & Tricks: Creating Buttons in Planning Analytics Workspace

IBM Planning Analytics Tips & Tricks: Synchronizing Selectors in Planning Analytics Workspace

Need more guidance? Take a look at our IBM Planning Analytics Training services and our Customer Care Program.

Did you know IBM Planning Analytics rules can be used to manipulate the case used for text?  There are rules to change values into both uppercase and lowercase.

UPPER

Converts an entire string to upper case

UPPER(‘Lee Lazarow wrote this’) becomes LEE LAZAROW WROTE THIS

LOWER

Converts entire string to lower case

LOWER(‘Lee Lazarow wrote this’) becomes lee lazarow wrote this

CAPIT

Converts the first letter of each work to upper case

CAPIT(‘Lee Lazarow wrote this’) becomes Lee Lazarow Wrote This

But what if you only want to capitalize the very first character?  You can do this by using these commands in conjunction with the LONG and the SUBST formulas. 

Here is an example of a set of rules that show various ways to manipulate a text string.

By combining formulas together in Planning Analytics, text can easily be manipulated in a variety of ways.

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Learn more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Ways to Stop a TI Process

IBM Planning Analytics Tips & Tricks: Ways to Skip a Record Using a TI Process

IBM Planning Analytics Tips & Tricks: How to Maintain a Hierarchy via a TurboIntegrator Process

Need more guidance? Take a look at our IBM Planning Analytics Training services and our Customer Care Program.

Did you know there are two different ways to stop a TurboIntegrator script?

The ProcessBreak command stops processing source data and proceeds directly to the Epilog.  This approach allows you still run code in the Epilog that can perform tasks such as deleting subsets, writing to a process tracking cube, or running other processes.

Here is an example of skipping the data and metadata section if a parameter called pVersion does not exist:

IF ( DIMIX ( ‘Version’, pVersion) = 0 );                  PROCESSBREAK;             ENDIF;

As you can see, there are no parameters to this command.  You use this by simply telling Planning Analytics to go to the Epilog.

If, however, you want to completely end the process then you can use the ProcessQuit command.  This command completely terminates the process without running anything in the Epilog.  Here is an example of doing the same check with ProcessQuit.

IF ( DIMIX ( ‘Version’, pVersion) = 0 );                  PROCESSQUIT;             ENDIF;

It’s up to you to determine how you want to stop a TI script from processing.

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Learn more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Ways to Skip a Record Using a TI Process

IBM Planning Analytics Tips & Tricks: Creating Selectors in Planning Analytics Workspace

IBM Planning Analytics Tips & Tricks: Creating Buttons in Planning Analytics Workspace

Need more guidance? Take a look at our IBM Planning Analytics Training services and our Customer Care Program.

Did you know there are two different ways to skip specific records when you are running TurboIntegrator scripts?

ItemSkip is used to skip a specific record. This is often nested within an IF statement and used when you want to simply ignore a record. Here is an example of skipping a record if a variable called vValue is negative:

IF ( vValue < 0 );                  ITEMSKIP;             ENDIF;

As you can see, there are no parameters to this command. You use this by simply telling Planning Analytics to skip the record.

If, however, you want to skip the record and create a record in the error log then you can use the ItemReject command. This command allows you to customize the message that appears in the log. Here is an example of rejecting the same record using ItemReject.

IF ( vValue < 0 );                  ITEMREJECT (‘The value is less than zero.’);             ENDIF;

It’s up to you to determine how you want to skip records.

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Learn more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Creating Selectors in Planning Analytics Workspace

IBM Planning Analytics Tips & Tricks: Creating Buttons in Planning Analytics Workspace

IBM Planning Analytics Tips & Tricks: Synchronizing Selectors in Planning Analytics Workspace