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.
Director & Principal Consultant
InfoCube Consulting Australia Pty Ltd
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
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.
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:
In Application Design mode, double-click on an application that you wish to update.
In the Properties pane, scroll down to select Custom Processes, and click on the Ellipse … to bring up the Custom Processes Settings screen.
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.
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
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.
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.
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
Finally you can export your diagrams into images and include them in presentations or reports.
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
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
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.
Go to the tool’s directory from command prompt
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.
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.
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.
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.
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:
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.
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.
A dimension editor tab will open on the main pane, click the Time Dimension Tool icon on the toolbar.
This is where it gets interesting.
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.
Below is a table showing the different hierarchy path:
Calendar year path
Lunar year path
365 (or 366) days
Force month to the end of calendar end date: Yes/No
Fore month to the end of calendar end date: No
Include in first month
Include in second month
Include in month contains biggest part
Split between the two months
Click Duration, specify the start and end date range of your time dimension.
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.
Click Member Names, here you can specify the format of each member level, and prefix and suffix.
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.
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.
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.