TM1 Tutorials.com

TM1 Tutorials Consultation – Ben Hill

When I started this site as a freelancer I was happy to cater to the need for non biased technical resources which solve real world problems. Back in Jul 2012 I joined InfoCube Consulting (Australia) as Director and Principal Consultant with the intention of creating something bigger than myself. I’m happy to say that now we are a team of over 10 expert consultants and we are growing quickly. So…

If you ever feel the need for expert advise in the IBM Cognos space, Why not call in the consultants who have been pouring their expertise into sharing best practice development for years. If geographically possible I will be more than happy to attend our first meeting and discuss how we can solve your immediate needs.

Kind Regards,
Ben Hill
Director & Principal Consultant
InfoCube Consulting Australia Pty Ltd


TM1 10.2 – Introducing CAFE

In this article we are going to introduce to you the new Excel Client for TM1 called Cognos Analysis for Microsoft Excel (CAFÉ). For those of you that have been using IBM Cognos BI you might think that this is not new and that you might have been using it for years. But it is new for TM1 users because IBM Cognos Analysis for Microsoft Excel is now integrated with IBM Cognos TM1 data sources! And I might add that it looks good too!!

CAFÉ can be used as a perspectives replacement for end-users and possibly in the future it will replace perspectives! It is an excel add in, as perspectives but it looks and feels more modern and updated while perspectives has been static for a couple of years without any significant improvements. One big advantage that CAFÉ has over perspectives is performance. WAN performance has been significantly improved in CAFÉ. All the basic functionality that you expect from a TM1 excel client is there. Like sandboxes, write back and data spreading.

CAFÉ has to modes that you can work. Explorations and Flex Views

Explorations mode

The explorations mode is very intuitive and BI users will feel right at home because it feels like your are working with Business workspace advanced (former business insight advanced).

With the explorations mode you can:

  • Explore data or to create views
  • Drill down, drill up, drag and drop items from the analysis pane
  • Edit the sets that you include in the crosstab or list
  • Filter your data using the context pane
  • Use existing subsets to populate your crosstabs or create new subsets.
  • Create calculated fields
  • Create sets using mdx
  • Convert the exploration into a flex view
  • Contribute to a plan by directly writing back values or use excel formulas or spread values across the crosstab
  • Use Excel charts

Flex Views mode

The Flex Views feels closer to perspectives. You cannot use it to explore data but to create highly customisable reports. In the Flex Views mode you can:

  • Drag and drop multiple pre-made views from the analysis pane or use a flex view created from the explorations mode. You can also use views from different cubes in the same sheet.
  • Use the dimension tree in the analysis pane to drag and drop items into the columns or rows
  • If you know the member names you can directly type them in the rows or columns and café will get the values
  • In this mode you can use excel formulas to create calculations
  • Use excel charts
  • Have multiple views in one excel sheet and you can use excel references to connect them
  • Contribute to a plan by directly writing back values or use excel formulas or spread values across the crosstab

 

Finally, for experienced users, it comes with its own API that you can use to automate the refreshing or publishing of workbook, document, and presentation content. You can also create VBA macros or use a scheduling tool to automate things.

 

 


TM1 10.2 – Enhancement: Workflow can trigger Processes

In this new version of TM1, 10.2, a new feature has been added to enable the change of workflow state of a node to trigger TI processes.  A preset TI process can be triggered immediate before and immediate after the workflow action.

This feature can be quite useful in a lot of ways, for example, if you have a separate BI report cube setup, when a node is submitted, TI process can be triggered to update the BI report cube without extra actions.  And once all the nodes are locked, then you can be sure that BI reporting cube is ready for reporting, without running extra processes.

Here are few simple steps:

  1. In Application Design mode, double-click on an application that you wish to update.
  2. In the Properties pane, scroll down to select Custom Processes, and click on the Ellipse … to bring up the Custom Processes Settings screen.
  3. Here you can specify which workflow actions can be enabled to trigger a TI process, either immediately before or immediately after the action, as well as the process need to be triggered.

There you go, a very useful feature that requires just a few simple steps.  This feature can potentially enhance and streamline the business planning processes.

Some examples of how this feature can be used:

  • Updating BI Reporting Cube: To ensure the BI reporting contains the latest planning data, TI process can be triggered immediately after a node is committed or submit.  A process is triggered to update a separate reporting cube (it would not matter if it is on the same or different server instance) from the planning cubes.  This would not require separate action to update BI reporting cube (or improve efficiency in case your reporting cube is linked by rules).
  • Import latest actuals from ERP: To ensure the user is seeing the latest and greatest actuals from your ERP, a TI process can be kicked off to import actual financial data from EPR just before user takes ownership.
  • Notify other users when a node is committed: When there are multiple contributors to an application node, can utilise a TI process to notify all relevant users that data is updated, and peer review may be required.
  • Trigger report distribution/printing: When a top consolidation node is locked/submitted against a business unit, it triggers report distribution to all managers for that particular business unit.  If all managers have access to the reporting portal online, this TI process can act as a notification mechanism.
  • Sending reviewer or contributor an email on submit or reject of a node: triggers a process to notify all parties involved.
  • Process to lock/unlock a node: In some cases, the business may want to prevent contributors from updating a node when it has been taken offline.  A process can be triggered to lock access on the server to that specific node immediately after it has been taken offline, and a process to unlock it just before it is brought back online again.

The list can go on and on, it really depends on your business and application, and how this feature may enhance your planning process.

 


Sydney Networking – TM1 10.2 Upgrade Special.

October 18th – Sydney TM1 Networking. TM1 Catchup – 10.2 Upgrade Special.

We have arranged a special TM1 catch up dedicated to the 10.2 update. We believe this is the biggest update to the platform since Applix acquired TM1 in 1996.

This is an opportunity to discuss what is new, what we like, what we don’t like and of course how we can make the best of the update over some amazing food and of course beer!

Whether you know all about 10.2 or not much at all – we will share stories and antidotes from experience as TM1 gurus!

Here are a few key points we have picked up from the 10.2 upgrade, if there is something else you would like discussed just let us know.

  • Metric cubes which tie corporate strategy to corporate planning through interactive scorecards
  • More efficient multithreading, which makes TM1 faster (by a factor of the number of cores you have).
  • Cognos Café – a completely new Excel front-end
  • TM1 applications now support multiple uses of a single approval hierarchy

So come along, we’d love to say hi, share a bite and buy you a drink.

The details are:

  • Date: Friday, 18th October
  • Time: 6PM
  • Location: Cabana Bar, St Leonards

RSVP: Please RSVP through the meet-up site here.

We look forward to seeing you there.


TM1 10.2 – New Feature: Data Flow Diagram

One of the biggest challenges we face when reviewing a TM1 application is getting an understanding of the data flow logics between cubes. There is a number of tools that can help creating some sort of  flowcharts, but none of them is really good.  An alternative is going through all the cube rules, to understand what connects the cubes, but that could very time-consuming.

In this new version of TM1 10.2, this problem has been addressed!  You can now create flowchart diagrams of your TM1 server with ease.

TM1 Data Flow Chart

TM1 Data Flow Chart

Creating A Data Flow Diagram

When creating a data flow diagram, there are options to create a diagram for the whole application, or a custom diagram highlighting only the selected cubes.

  1. To create a diagram for the whole model, all have to do, is right click in the model design pane and choose “Show Data Flow” and the diagram of the server will be created for you automatically.  Once the diagram is created, you can customise and rearrange everything in any way you want.
  2. The other option is to create a new custom diagram.  Right click in the model design pane, go to new and choose “New Data Flow”. Then you will be represented with a blank canvas and as you drag and drop the cubes, Performance Modeller (PM) will create the links between the cubes.

Thinker arrows mean that, there are more than one links between the cubes, for example, the link between the cubes Promotion Plan and Gross Margin Calculation, as you can see above. You can also group the cubes and customise the diagram in any way you want. Another cool feature is that you can drag a drop folder and bring in the diagram all the cubes in that folder, if they are not already included. This makes it easy to highlight any new cubes created in your TM1 server.

To top everything, this new tool can understand manual rules and feeders and show them in the diagram, like below.

TM1 Data Flow Chart with Manual Rules

TM1 Data Flow Chart with Manual Rules

Finally you can export your diagrams into images and include them in presentations or reports.


Application Maintenance TI Process Package

This is a transferred out package contains a number of turbo integrator processes that demonstrates the use of application maintenance utility.  This TI process package can by imported directly into an existing TM1 server instance, or run from a completely separate stand-alone TM1 server instance.

Here is a list of objects included in this package:

  • Brief instructions on how to ‘install’ and use this package
  • Cube: Parameter cube, and relevant dimensions
  • TI: Activate an application
  • TI: Deactivate an application
  • TI: Deploy an application
  • TI: Export rights to file
  • TI: Import rights from file
  • TI: Refresh rights of an application
  • TI: Reset application states
  • TI: Disable a TM1 server
  • TI: Enable a TM1 server
  • TI: Generate an encrypted password file

Performance Modeler – Transfer Out Package: Application Maintenance Utility TI Processes
TI Processes in a Zip: Application Maintenance Utility TI Processes

 


TM1 10.2 – New Feature: Enable/Disable Applications from Commandline

In this new release of TM1 10.2, a new feature has been added to allow users to perform application maintenance using command line, or in a batch file.

The main purpose of this utility is to automate some of the application maintenance, using a combination of standard TI processes and TI processes that execute commands available in this utility.  Also, chores can be setup to execute these periodically, such as overnight application maintenance.

This utility can be found in the <TM1 installation directory>\webapps\pmpsvc\WEB-INF\tools\, and the main file to use is app_maintenance.bat.

 

Here are a list of commands that you can perform using this utility:

  • Activate an application
  • Deactivate an application
  • Deploy an application
  • Import, export and refresh rights to an application
  • Reset workflow states of an application
  • CAM Logon
    • Logon using an encrypted password
    • Logon using an encrypted password created by TM1Crypt.exe
    • Create an encrypted password file
    • Encrypted password with increase key strength
  • Log to a file
  • Set logging level: ERROR, DEBUG, INFO, and OFF
  • Execute a sequence of commands from a command file
  • Package the app_maintenance tool
  • Disable a server
  • Enable a server

Here are an example on what the .bat file looks like:

  • Deactivate an application

The command syntax is pretty self-explanatory, that needs to identify the TM1 Application portal URL, log-in credentials, application ID, and the action

These command batch files can be executed from a TI process, for example:

And of course, you can string together with other standard TI processes into a chore to be performed overnight.

There are syntax examples for each command available through help, -h.  You can output this help to a text file for future reference.

  1. Go to the tool’s directory from command prompt
  2. Enter app_maintenance.bat -h > sample_syntax.txt

This utility can be packaged and “installed” onto another machine, using the -package command option.

Note, to use this utility, a version of Java JRE must be running of the machine that utility is running, along with all the JRE settings in place in order for this utility to find it.

Utility Package: Application Maintenance Utility TI Processes


TM1 10.2 – Enhancement: Approval Hierarchy can be reused

In TM1 10.2, an added enhancement to allow the same approval hierarchy to be used in multiple applications.  This enhancement can bring lots of benefits to the business and efficiency to TM1 models are designed.  No doubt, this change most of you had been waited for. This change in conjunction with control dimension provides numerous possibilities of easily creating new TM1 application.

Business Benefits

This enhancement is very useful when a business operates different budget cycle to forecast cycle, or different budget deadlines for different regions or geographical locations.  The flexibility is here to meet those business requirements.

  • Separate applications can be deployed based on the same data cube

    For example, separate applications for budgeting and forecasting, since they may have different deadlines and contributed by different users.  In this case, both applications can be updated simultaneously by different users, without the need to taking ownerships from each other.  While cube data is kept centrally in the same cube, makes analysis much easier, such as variance analysis.

  • Ability to utilise different element groups within the same approval hierarchy
  • Different regions or business groups may have different reporting deadlines and operates with different planning cycles.  Having separate applications using the same approval dimension can be helpful, while all business groups or regions rolls up to company total for consolidation and analysis purposes.

  • Same set of information is shared between all applications based on the same cube.
  • Since all applications are setup to link to the same cube, multiple applications can be updated simultaneously (not interferences with each other).  And at the same time, any updates made to one application (eg. a scenario version) can potentially viewed immediately other applications, provide it has read access to that scenario version.

Of course, there may be a lot more other use and benefits for having the flexibility of reusing the same approval hierarchy dimension.

A note, although the same approval hierarchy can be reused, the key constraint is that the cube slices between different applications can not be overlapped, since you can not have multiple users updating the same cell value at the same time.  When Performance Modeller identifies an overlap during application creation and update, user will get an error regarding that.

Control Dimension

Another new feature has been added in this new release of TM1 10.2, Control Dimension.  This feature allows applications to further trim down to specific region of the cube(s) that needs updating (or reviewing).  The main intended use of the control dimension is to allow different scenario versions to be deployed in different applications.

For example, Budget Version 1 is only deployed in 2013 July Budget Plan, while Budget Version 2 is deployed in 2013 October Budget Plan (July Budget Plan as read only in the same application).

Once the control dimension subset is added to an application, you can update the access to available elements within the subset for the selected application.

This new feature adds flexibility on how applications and TM1 models are designed, and adds further usability for business planning application.

 


Generating a Time Dimension in Performance Modeler

Almost all TM1/Cognos Express applications will require a time dimension, and creating a time dimension has never been easier, with Time Dimension Tool provided in performance modeller.

Here are a few simple steps:

  1. Start up performance modeller, log onto IBM CX/TM1 Planner Applications (http://<server host>:9300/pmpsvc) and click the performance modeller icon on the top right corner , then select a TM1 server instance.
  2. In Model Design pane, right-click on a folder where you want to create your time dimension, select New, then Dimension.  Enter a dimension name, e.g. Months, and select Time as dimension type.
  3. A dimension editor tab will open on the main pane, click the Time Dimension Tool icon on the toolbar.
  4. This is where it gets interesting.

  5. Click Period Level, here you can choose the required dimension levels, whether to include years, quarters, months and weeks.  Each level has options on how you can organise the hierarchy of your time dimension, and choices available depend on the selection you made for years.  Brief explanations for each period level option at tips field.
  6. Below is a table showing the different hierarchy path:

    Period Level

    Calendar year path

    Lunar year path

    Years
    • 365 (or 366) days
    • 52 weeks
    Quarters
    • 3 months
    • 13 weeks
    Months
    • Calendar months

     

    • Force month to the end of calendar end date: Yes/No
    • 444 weeks
    • 454 weeks
    • 544 weeks
    • 445 weeks
    • Fore month to the end of calendar end date: No
    Weeks
    • Include in first month
    • Include in second month
    • Include in month contains biggest part
    • Split between the two months
    • n/a
  7. Click Duration, specify the start and end date range of your time dimension.
  8. Note when using the date selector to specify start date and end date, select the date before your intended date.  For example, if you want your start date to be 1-Jul-2012, select 30-Jun-2012, and 1-Jul-2012 will appear as start date.  Alternative, is to enter it manually, but need to enter in the exact format as presented, e.g. Jul 01, 2012, otherwise it can not be recognised.

  9. Click Member Names, here you can specify the format of each member level, and prefix and suffix.
  10. For weeks level, you can choose its member name as start date of the week, end date of the week, week number.  The wizard also allows you to specify the date format at week level.  If a date format option is not available, you can over-type with a customised format, unlike the format dropdown option.

  11. Press ok.

End result:

The advantage of this tool is, not only creates the time dimension hierarchy, but also inserts the standard handy time dimension attributes, such as:

    Start date, end date, last period, first period, previous period and next period.

These attributes are very handy for rule calculations and/or turbo integrator processes.
This Time Dimension Tool is very easy to use, only a few simple steps.  No need for complex turbo integrator processes.  Of course, TI processes definitely has many advantages, that’s ultra flexible and adaptable to any business’ needs.


TM1 10.2 – Feature Change: Cell Security Cubes

Model security can now be applied and amended on an application using Performance Modeller, this includes security for cells, cubes, dimensions, processes and chores.  A few key changes to note:

  • Changing security on applications will take effect immediately
  • Security applied to the applications can be deleted with ease in the model
  • Cell security on a cube can be applied to selection of dimensions, instead of all dimensions in the cube
  • Cell Security cubes can be deleted without restarting the TM1 Server and manually removing the “.cub” files

Apply and update of model security

Model security editor has been added to Performance Modeller in the 10.2 release.  Security of each type are grouped under the relevant grouping, which makes it easier to understand what security has been applied to a model at a glance.  To apply security to an object, right click on the object, select Configure Security, then select a permission type you want to apply.

Security changes to the model takes effect immediately following the security rule is saved.  No need to refresh security and/or restarting server.

Deleting model security rules

Model security can be now removed/deleted without requiring security refresh or shutting down the server, simply right-click on an existing security rule, and click Delete.  And security changes takes effect immediately following the security rule is saved.

In previous release of 10.1, for example, removing dimension security would require shutting down the server, deleting the ‘.cub’ file relating to the dimension security in the background, then bringing the server backup.

Cell security can be applied to a selection dimensions, no longer default to all dimensions in a cube

In the previous release of 10.1, when applying cell security, it is default to all dimensions of the cube.  This is no longer the case in the new release of 10.2.

When creating a cell security rule, security editor in Performance Modeller will prompt user to select the dimensions to participate.

Standard TM1 cell security priority will applies, where cell security will take precedent over dimension element security, with exception when NONE is inserted against a dimension element, which would be suppressed from user.

This key change makes cube security control much cleaner, easier to understand and manage.

Its really good to see IBM really taking a look at how TM1 developers are working with security cubes and enhancing this. This is evident through how rules are listed automatically under the security cubes even if the rule files have not been generated / created on the cube.