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

12/22/2016

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. 

Cognos TM1 Blogs

IBM COGNOS TM1 Blogs

Below is a  massive list of IBM Cognos TM1  blogs. To search for a specific topic, use the search bar to the right.

What’s New in Planning Analytics Workspace 94
Written by Mike Bernaiche, April 4th, 2024 If you are using the AI forecast option in Planning Analytics, then you[...]
Beyond the ‘Office of No': Orchestrating Harmony Between Immediate Gains and Lasting Strategy
Written by Heather L. Cole, March 8th, 2024 In the dynamic landscape of modern business, achieving harmony between short-term financial[...]
What’s New in Planning Analytics Workspace 93
Written by, Mike Bernaiche, February 29th, 2024 In February 2024, IBM released the next version of Planning Analytics Workspace 93.[...]
IBM Planning Analytics as a Service on AWS
Written by Mike Bernaiche, January 18th, 2024 IBM recently introduced a new partnership and offering for Planning Analytics.  Planning Analytics[...]
Revolutionize Your Financial Stability with Better Planning Models
Written by Heather L. Cole, November 9th, 2023 As a financial executive, you are constantly facing the challenge of ensuring[...]
Breaking Down Barriers: Unleash Corporate Agility with Extended Planning and Analytics
Written by Heather L. Cole, November 2nd, 2023 In the ever-evolving landscape of business, the traditional Financial Planning and Analysis[...]
What’s New in Planning Analytics Workspace 90
Written by Mike Bernaiche, September 22nd, 2023 Planning Analytics Workspace 90 was released on September 19, 2023, and there are[...]
Escaping the Budgeting Blues: Rolling Forecasts with IBM Planning Analytics
Written by Heather L. Cole, September 1st 2023 Budgeting – the word alone can send shivers down the spines of[...]
What’s New in Planning Analytics Workspace 87
Written by Mike Bernaiche, June 28th, 2023 On May 17, 2023, IBM released Planning Analytics Workspace 87.  A reminder that[...]
The Power of IBM Planning Analytics for Mid-Market Companies: Leveling Up from Excel
Written by Heather L. Cole, June 14th, 2023 In the fast-paced world of mid-market companies, there's a hero waiting to[...]
What’s New in Planning Analytics Workspace 86
Written by Mike Bernaiche, May 12th, 2023 On April 18, 2023, IBM released Planning Analytics Workspace 86.  Continuing with their[...]
What’s New in Planning Analytics Workspace 85
Written by Mike Bernaiche, April 4th, 2023 In March 2023, IBM released Planning Analytics Workspace 85.  Updates to Planning Analytics[...]
What’s New in Planning Analytics Workspace 2.0.84
Written by Mike Bernaiche, February 16th 2023 It is time again to discuss what is new in Planning Analytics Workspace[...]
Docker Support – Every Planning Analytics Customer Must Read
Written by Mike Bernaiche, January 10th, 2023 If you own Planning Analytics and use Planning Analytics Workspace (PAw) on Microsoft[...]
What’s New in Planning Analytics Workspace 83
Written by Mike Bernaiche, January 26th 2023 It is a new year and IBM has released the first update to[...]
2023 Lodestar Solutions Services
Written by Mike Bernaiche, December 23rd, 2022 Lodestar Solutions Services team is ready to help in 2023!  I wanted to[...]
New in Planning Analytics Workspace 82
Written by Mike Bernaiche, December 16th, 2022 IBM released Planning Analytics Workspace 82 on November 17, 2022.  While this isn’t[...]
What’s New in Planning Analytics Workspace Version 80 and 81
Written by Mike Bernaiche, November 3rd 2022 It is that time to discuss what is new in Planning Analytics Workspace[...]
New in Planning Analytics Workspace 79
Written by Mike Bernaiche, September 15th 2022 On September 1, 2022, IBM released Planning Analytics Workspace 79 or 2.0.79.  There[...]
Coming Soon in Planning Analytics Applications and Plans
Written by Mike Bernaiche, July 29th 2022 Recently, I had the opportunity to see what was coming in Planning Analytics[...]
New in Planning Analytics Workspace Version 77 and 78
Written by Mike Bernaiche, July 28th 2022 It is that time again!  Providing you everything new in planning analytics workspace[...]
What’s New in Planning Analytics Workspace 75 and 76
Written by Mike Bernaiche, May 19th 2022 Typically, once a month, IBM updates Planning Analytics Workspace.  In April version 75[...]
What’s New in Planning Analytics Workspace 73 and 74
Written by Mike Bernaiche, April 7th 2022 Typically, once a month, IBM updates Planning Analytics Workspace.  In February version 73[...]
IBM Planning Analytics Versions Are End of Life
Written by Heather L. Cole, March 3rd 2022 Are you an IBM Planning Analytics TM1 user that has not upgraded[...]
Scenario Planning with IBM Planning Analytics
Written by Heather L. Cole, November 4th 2021Scenario Planning with IBM Planning Analytics Did the Pandemic disrupt your business?  Did[...]
What’s New in Planning Analytics Workspace Version 67 and 68
Written by Mike Bernaiche, October 21st 2021 Over the last couple of months, IBM has released 2 updates to Planning[...]
Forecast in Planning Analytics with AI
Written by Mike Bernaiche, October 14th 2021 Did you know that you can forecast in Planning Analytics Workspace by using[...]
Scorecards and Metrics in Planning Analytics
Written by Mike Bernaiche, September 30th, 2021Did you know that you can track your key performance indicators (KPI’s) with Scorecards[...]
What’s New in IBM Planning Analytics Workspace Version 64, 65 and 66
Written by Mike Bernaiche, August 19th 2021IBM continues to update Planning Analytics Workspace on a regular basis.  The pattern has[...]
How to Burst Reports from IBM Planning Analytics
Written by Heather L. Cole on August 5th, 2021 Are you an IBM Planning Analytics client and wondering how to[...]
How to Connect TM1 to Power BI
Written by Heather Cole, May 13th 2021Have you been asked to pass the TM1 data to Power BI, Tableau, Qlik[...]
Planning Analytics Workspace – Now What?
April 30th, 2021 Recently, it came to my attention that some Planning Analytics (formerly TM1) clients are not clear about[...]
What’s New in Planning Analytics Workspace Version 61 and 62
Written by Mike Bernaiche on April 23rd, 2021IBM continues to release updates every 30 to 60 days for Planning Analytics[...]
How to Easily Move Development Changes to Production in IBM Planning Analytics TM1 with Soterre
February 4th, 2021 TM1 Administrators of the IBM Planning Analytics that follow best practices should be using a development environment[...]
Version Control and Audit Trail for IBM Planning Analytics TM1 with Soterre
January 28th, 2021 Are you a TM1 Administrator and looking for a better way to know which Modeler changed a[...]
What TM1 Licenses Include IBM Planning Analytics Workspace?
January 7th, 2021 Looking for a better user interface to your powerful Cognos TM1/ Planning Analytics data?  Do your users[...]
Guide to Creating a Virtual Hierarchy
November 5th, 2020 In todays planning and forecasting world, the ability to change quickly and provide valuable data to your[...]
What Kind of Audit Trail is Available for Cognos Planning Analytics (TM1)?
October 29th, 2020 A budgeting and planning solution should always provide a good audit trail.  So, what kind of audit[...]
Sandboxes in Planning Analytics Workspace
October 1st, 2020 Think back to when you were younger playing in a sandbox.  Grabbing your plastic shovels and building[...]
Input and Spread Data in Planning Analytics Workspace
September 24th, 2020  Now that you have read the introduction to Planning Analytics Workspace (PAw) blog found here, it is time[...]
Benefits to Using IBM Planning Analytics Workspace
September 17th, 2020 If you are a TM1 or Planning Analytics client and you are not using Planning Analytics Workspace,[...]
Planning Analytics Excel Options
August 20th, 2020 Many of you use Excel when working with Planning Analytics.  You may be creating budgets, exporting files,[...]
Why Buy TM1 Connect?
July 31st, 2020 We are often asked; how can I bring my TM1 data into other software and create reports[...]
Why is TM1 Slow?
Are you a TM1 user and frustrated with the speed? Are your perspective reports extremely slow? Does opening a cube[...]
Creating the COVID Forecast with Cognos Planning Analytics, TM1
April 30th, 2020 ​Wow, the world has changed in just a few weeks!  Our way of life as we knew[...]
Cognos Planning Analytics for Excel
March 12, 2020Upgrading TM1, checkout Cognos Planning Analytics for Excel PAX Are you a long time TM1 user looking to[...]
Move Action Buttons from TM1 Perspectives to Planning Analytics for Excel (PAX)
March 5th, 2020Are you planning your upgrade from TM1 to Planning Analytics? After you review all the benefits, steps and[...]
IBM Let the Cognos TM1 Grandfather Clause Die Putting Old Clients at Risk!
February 20th, 2020​To all you very old TM1 clients I urge you to check your IBM entitlements!  I recently learned[...]
Neither Cognos BI nor Microsoft Power BI can Replace Cognos Planning Analytics (TM1)!
February 13th, 2020Recently I met with a client who owned and was using both IBM Cognos BI and TM1.  Their[...]
Is it Time to Redesign Your TM1 Planning System?
December 12th, 2019As business analytics coaches it is our passion to talk about latest innovations in the financial planning and[...]
Complete Model Development in Planning Analytics Workspace
November 11, 2019As business analytics coaches it is our passion to talk about latest innovations in the financial planning and[...]
Edit Dimensions and Others Greyed Out in Planning Analytics Architect
September 20, 2019The problem - Edit Dimensions and Others are GreyWe get this question from time to time - Why[...]
What does it cost to upgrade from TM1 to IBM Planning Analytics?
September 10, 2019“Hey, I heard a rumor that after September 2019 my TM1 10.2.x will not be supported. What does[...]
Model Directly in IBM Planning Analytics Workspace. A one stop shop
1/22/2019 Are you asking yourself if it is  possible to build a model directly in IBM Planning Analytics Workspace? There is[...]
7 Reasons Why You Should Purchase Workspace (PAw)
 9/11/2018 7 Reasons Why You Should Purchase Workspace (PAw)You have probably heard a lot about Planning Analytics Workspace (PAw).  However, like[...]
Dashboards in TM1? Legacy Users Need to Purchase Workspace Add-On.
7/10/18 Are you a Legacy TM1 shop and your users are asking about dashboards in TM1?  For all of you[...]
New Hierarchy Level Behavior in Planning Analytics (TM1) Set Editor
3/20/2018Leaf Element Level naming convention is really changing...It's when I first started teaching the Performance Modeler (PM) class and showing[...]
Have You Secured Funding Yet? It’s Budgeting Season, Secure Funding Now For IBM Cognos
Have You Secured Funding Yet?  It's Budget Season.  Secure Funding Now for IBM Cognos!​Do you need process improvement and require[...]
Lodestar Solutions is Hiring – TM1 Consultant Needed
5/11/2017TM1 Consultant OverviewAre you an expert in Cognos TM1? Do you have advanced knowledge of Microsoft Excel, Access or SQL[...]
TM1 User Maintained Public Subsets
3/6/2017 As a TM1 Admin have you ever wished for user maintained public subsets? User maintained public subsets puts the[...]
Recreating TM1 Alternate Hierarchies
2/8/2017 Cognos TM1 allows alternate hierarchies which, from a user perspective, is greatly appreciated but from a TM1 Admin perspective,[...]
TM1 Websheets to Simplify, Document and Organize TI Process and Tasks
12/22/2016 I’m all about easy. It is a phrase I say repeatedly to my clients that are new to IBM[...]
Are Your IBM Security Certificates About To Expire?
10/4/2016 The purpose of SSL certificates (Secure Sockets Layer) are for encrypting data as it is transferred around a network.[...]
How to: Deleting or Adding Dimensions to TM1 Cubes
6/1/2016 Today’s topic is about deleting or adding dimensions to TM1 cubes that you are using in Production. With TM1’s[...]

How to: Deleting or Adding Dimensions to TM1 Cubes

6/1/2016

Today’s topic is about deleting or adding dimensions to TM1 cubes that you are using in Production. With TM1’s introduction of Performance Modeler, you now have the ability to add or delete a dimension from an existing cube.  Before this, you would have had to create a whole new cube.  While this is pretty simple when done in the build phase, there are several dependencies to be mindful of, especially if you do this on a cube that is currently in use.  It’s a nice feature to be able to do this, but it does open up Pandora’s Box…

Dependent Cubes:  Utilize the Model Flow Chart functionality in Performance Modeler so that you can see what other cubes are dependent on the cube you are changing.  This will show you the dependencies of rules, feeder and links to other cubes that may be affected.  Unfortunately, this will NOT let you know what TI Processes are affected.

Cube Dimension Order: Take note of the dimension order of your cube BEFORE you make your change.  Since you are using Performance Modeler to add/delete a dimension, it will be the driver of the dimension order.  So for example your cube was originally ordered with the dimensions of Year, Month, Measure and you decide to add Version.  TM1 may change it to Month, Version, Year, Measure.  Be mindful of this, because the correct dimension order is necessary in scripting of any rules that use a DB function and in any TI processes.

Views:  Since you are changing the structure of the cube, views (and any private views) will become invalid.  Take inventory of existing views and how they are set up for both dimension position and subsets used by those dimensions.  This is especially important if views are used in TM1 Web and TM1 Applications (aka Contributor).  You will have to recreate the views and if using TM1 Applications, update your Application and redeploy it.  The Application section of Server Explorer for views used in TM1 Web will need to be updated too.

Existing Data: You will be prompted how you want to handle your existing data. The choices are Clear Cube Data, Duplicate Data or to specify an element from the dimension changed.

Rules/Links/Cube Calulations:  Any manual rules and feeders that use the DB function will need to be updated.  Make sure you have your dimension order correct in the DB functions when you update them.  Links and Cube Calculations may require updates too.

Websheets:  Any websheets that are dependent on this restructured cube will need to be updated.  Websheets pull in data by the coordinate of the cube cell, so again, you need to update the formulas with the correct dimension order.  Communicate this with your users before you make your intended change.  You may have some power users that have their own websheets that you don’t know about and this will affect them too.

TI Processes: These will also need to be updated for whatever the data source is – even if it is a process using the same cube.

Cognos BI, Cognos Insight & 3rd Party Software:  You will have to be cognizant on what is used here as well.

In the end, there are valid reasons for deleting or adding dimensions to TM1 cubes, so I am not advocating against it. It’s quite similar as if you created a whole new cube to replace an existing cube.  You just need to think through all the ripple effects. For more information, don't hesitate to contact us at Sales@LodestarSolutions.com or check out this link for the IBM Performance Modeler user guide: http://public.dhe.ibm.com/software/data/cognos/documentation/docs/en/10.2.0/prfmdl_ug.pdf

 

X