TM1 Websheets to Simplify, Document and Organize TI Process and Tasks


I’m all about easy. It is a phrase I say repeatedly to my clients that are new to IBM Cognos TM1 so that it helps them to focus in on what will be the simplest solution and not to recreate a cumbersome process in new software. This is really a guided example showing how to use TM1 websheets to organize TI Processes, be a tool to document TI Processes, and document TM1 tasks.

As a TM1 admin, you are pretty adept at toggling around cubes and understanding your data. Maybe you even built the whole model and it’s all second nature to you, so you skipped documenting procedures. Well… it all seemed second nature to you at the time, but a year has gone by and you need to prep for your upcoming budget cycle and now you are trying to remember what needs to be updated.

In any event, I want to explain how to simplify and manage tasks and documentation in TM1 with websheets. For this, I utilize Perspectives and the Applications folders of Server Explorer to streamline tasks. The Applications folders are great for grouping tasks with the use of links to documentation (ie Word documents), cube views set up for specific needs, and websheets that can take it all to the next level. By next level, I mean that you can create a websheet that provides data pulled from TM1, add-in Excel formulas to do calculations on that data (thus eliminating writing of additional rules in TM1), have an input area to update data points, include Action Buttons to run TI Processes, and insert notations to document the function. This will not only make your life easier, it will help you to remember how the heck you did something last year, allow you to delegate tasks to other team members and documents procedures to give you piece of mind knowing that you did not leave behind an unrewarding treasure hunt for your former co-workers to figure out how you did something after you won the lottery and left the company. You can feel free to relax and have that Pina Colada on a Tuesday while floating in the pool (note, only for lottery winners).

Here, I will run through where you take your cube that houses rates that are used in an annual budget cycle, from just being a cube for input into  streamlined TM1 Websheets that simplifies the procedure.

Step One: Create cube for rate input and save view specific for input in TM1 Websheets.

TM1 Websheets

What’s missing? Well, now you need to remember that your Seasonal_Factor that you must update is in the Rates cube. That’s not really evident when you first open Server Explorer.

Step Two: Use Applications folder area to organize tasks in TM1 Websheets

TM1 Websheets

Getting closer. You dragged your Seasonal_Factor_Input view up to your Admin folder so that you know it is part of your Admin functions.

What’s missing? Working with just this view, I don’t see the average of the last two years to figure out what is trending in my data. Also, I will either need to put a HOLD on the Total Year so that if I make any changes, I still balance to 100% for the year, else I need to use the Data Spreading at the Total Year level to ensure I am at 100%. This is more effort than I want and it can be prone to error of Total Year not equaling 100%.

TM1 Websheets

Step 3: Create a Websheet and put it in the Applications folder area in TM1 Websheets

Using Perspectives and my view I created earlier, I do a slice to Excel so that TM1 can put in most of the formulas that I need into my websheet I create. In another tab of the same websheet I add a different slice that pulls in dollar value data from two other years that I select on the first tab. In the third tab, using just Excel functions, I calculate percentage values. I go back to my first tab that I will use for input and again using just Excel functions, add a section that averages two years of data to give me a trend value. I use the SUBMN function for the Year drivers and layer in formulas to make the defaults for them dynamic, while giving me the ability to still select what years I want. I add two Action Buttons, one to set my values in the input area to my Rates cube and the second to run a TI Process that ensures that the Total Year equals 100% for each property. Last, I upload my websheet to the Server Explorer Applications folder called Admin. This will allow me to update my values and see trending in a simple websheet accessed via TM1 Web.

In TM1 Web, I click on my highlighted websheet.
TM1 Websheets

This opens a websheet that looks like this:

TM1 Websheets
In conclusion, websheets can handle a variety of tasks to make your planning and analysis procedures easier to manage and include more robust functionality. In this example, we went from a cube view used to input Seasonal Factor values, to a websheet that gave insight to trends, ease of data entry and assurance of accuracy. I can customize the formatting to make it as pretty as I want. I can export this out as a snapshot or pdf to send to others that may need this info, but do not have access to TM1. I could even print it out and hang it on my refrigerator if magnets stuck on my doors.

Most Excel formulas and formatting are supported in Perspectives and TM1 Web. TM1 Web does render slightly different from Perspectives, so you may need to make some adjustments to get the results you expect.

To learn more about TM1 Worksheet functions,  click here. 

When to Use Cognos BI vs TM1 Reporting

Cognos BI vs TM1 Reporting - Why Not Use Both?

Do you own or are looking to purchase both Cognos BI and TM1  and want to integrate them for reporting? If so, you may suddenly ask yourself, “Which do I use?” It is often overlooked that TM1 and BI can both be utilized for reporting. So let’s explore your options on Cognos BI vs TM1 reporting

TM1 Reporting Capabilities

With the new licensing model for TM1, IBM has extended the capability of TM1 reporting to include Cognos Insight, Cognos Analysis for Excel (CAFÉ), Scorecarding and Workspace for use on TM1 data. TM1 users have a number of options to meet their reporting needs. They can leverage the Excel add-in for TM1/Perspectives, CAFÉ, TM1 Web, Cognos Insight and Workspace for TM1 to do reporting.

Cognos BI vs TM1 Reporting

BI Reporting

BI reporting is ultra-powerful! If you are looking for robust, fancy reports with visualization; want to leverage direct integration with Microsoft PowerPoint; want to burst reports out to groups of user; or want to leverage data from a data warehouse or other source systems that are not in TM1, you should look to Cognos BI.

Cognos BI vs TM1 Reporting

Things to consider:

Do you need to write back to the data? If the user needs to enter data into a template for a budget or forecast, TM1 allows this capability. Whereas, BI is read only.

Do you already own TM1 or BI licenses? I am a believer that you should use what you have. If you have a user who already has a BI license, but you want to provide them reports on the data in TM1, consider providing them the reports on TM1 data through BI. Cognos BI has real-time integration with TM1. It will save you from having to also buy them a TM1 license. If you have TM1 and the user is only viewing data that’s in TM1, and the TM1 reporting option meets your needs, you might not need a BI license until you need more robust reporting or reporting off data not in TM1.

Do you want to take Analytics to the next level? I recommend that clients leverage TM1 when they need write back capability to the data, and create “what if models”. But I recommend that they also leverage BI for robust reporting with visualization and dashboards that include data from multiple data sources. Depending on your current IBM licensing model, you may need to buy both a BI and TM1 license for your users, or you might be able to purchase a license that includes both.

Are you integrating BI and TM1? When integrating TM1 and BI, the design of BI is very important! Your consultants should ask questions about your reporting needs and have familiarity with BI, or at least have a strong network they can refer to. Define the levels in TM1 so BI has dimensional functionality. I recommend you look at TM1 as a data mart and the principles of data warehousing should be considered. For example, you will want to create reporting cubes that feed BI. These cubes should have all the versions and time dimensions that you might want to leverage in BI. If you build them in TM1, you will simplify the BI report writing experience.

Size matters: When you use BI and TM1 together, you need to make sure your environment is sized properly. You need to anticipate your server and memory needs to accomplish your goals. Because TM1 is an “in-memory” solutions, it can be a hog.

Which IBM representative are you working with?

One thing to note about the IBM Cognos sales processes is that in most territories today, IBM has separate IBM sales reps for TM1 and BI. The sales person may not have knowledge on both Cognos BI vs TM1 Reporting. It’s up to you to be knowledgeable and create your Business Analytics road map. So, if you have questions, don’t hesitate to reach out to an Analytics Advisor at Lodestar Solutions. 

If you liked this blog on Cognos BI vs TM1 Reporting, then check out this info on Cognos Express by clicking here

Cognos TM1 Contributor Licenses Functionality

​TM1 Contributor Licenses Functionality Explained

Are you confused by the TM1 Contributor Licenses Functionality (now called Performance Management User)?

Here is the description IBM provided to me about TM1 Contributor Licenses Functionality, but this led to some questions:

The Enterprise Planning TM1 Contributor/ Performance Management User role is targeted at the business analyst or manager for contributing to enterprise TM1 applications. Contributor/PM User licenses must have related server rights, either through Analytics Server licenses, or previously granted server rights from earlier versions of TM1.

The TM1 Contributor/PM user has access to:

  • Modify & review workflow status through Planning Manager or TM1 Contributor/PM User
  • Perform read/write data operations via any supported TM1 interface, including TM1 Contributor, TM1 for Microsoft Excel and TM1 Web. Also, from other separately purchased tools such as Executive Viewer, or custom applications using the TM1 API
  • Execute TM1 Turbo Integrator processes and chores
  • Create private & public presentation objects via TM1 for Microsoft Excel
  • Read data from and write data to a TM1 server through IBM Cognos Connection web portlets displaying TM1 viewers
  • This user also has all any additional rights found under Enterprise Planning TM1 Explorer

What does “create private and public presentation objects via TM1 for Microsoft Excel” mean? IBM is using the term “TM1 for Microsoft Excel” to refer to the Excel Perspectives add-in, and the term “presentation objects” to refer to TM1 Workbooks or Websheets. The term “presentation objects” can be extended to refer to Cube views as well. But doesn’t the ability to create Public objects require assignment to an Admin or Data Admin security group; and, therefore, a TM1 Modeler license? Let’s explore that thought …

With designated permissions, non-admin Users having a TM1 Contributor / PM User license may:

  1. Save public subsets, public cube views, and public workbooks/websheets
  2. Add presentation objects to public Application Folders and make them public

This is accomplished by having an Admin user designate the non-admin user’s TM1 Security Group with ‘Admin’ rights to specific objects using TM1 Security.

  1. A non-admin user belonging to a Security Group having ‘Admin’ access to an existing Application Folder may add presentation objects to the Folder and make them public *
  2. A non-admin user belonging to a Security Group having ‘Admin’ access to a designated Dimension may create Public Subsets for that Dimension *
  3. A non-admin user belonging to a Security Group having ‘Admin’ access to a designated Cube may create Public Views for that Cube *

* This capability is available in Perspectives / Architect with TM1 10.1.x and in TM1Web with TM1 10.1.1 FP2 IF3. The capability is also targeted for TM1Web 10.2 with planned mid-year 2014 releases (10.2 FP2 and 10.2.2).

You may also be wondering how a user can save views to Application Folders in TM1Web. Current releases of TM1 10.1.1 and TM1 10.2 permit a non-admin user to save a new Cube view opened from an Application Folder ‘back into’ that same Application Folder using the ‘Save As’ action. By default, the non-admin user can perform this action to save private Cube views; and with the designated permissions identified above, the non-admin user can perform this action to save public Cube views.

Note that licensing guidelines are always subject to change, so please check with your IBM rep for updates. This is not intended to be construed as legal advice and may not be the views of IBM.

Thank you, IBM! We really appreciate clarification as our clients want to stay in compliance!

Here is a video we just posted on our YouTube channel on this topic:

TM1 Contributor Licenses Functionality

If you would like help reviewing your IBM Business Analytics licenses, contact me at You can also check out our Licensing videos on:

Thanks for taking the time to read about TM1 Contributor Licenses Functionality!