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. 

IBM Cognos software and Law Firm KPIs


We at Lodestar Solutions get asked by law firm clients what should they be doing with IBM Cognos software specifically their TM1 and/or IBM Cognos BI? The answer usually is, “a lot more than what you are probably doing”. Many times they use TM1 to plan budgets, but under-utilize it for streamlining data analysis. Other times they use IBM Cognos BI to create all kinds of jazzy reports that only tell a story about the past. For those that already own IBM Cognos software, expand your usage and get all that you can from your investment. For those that are looking for tools to solve their KPI reporting needs and especially for legal firms wanting to determine the best law firm KPIs, IBM Cognos can do it. With IBM Cognos, you can create your financial dashboard, performance dashboard, and KPI analysis on top of meeting your budget and forecasting needs.

law firm KPIs

Here is an excerpt from the American Bar Association regarding key law firm  KPIs  reporting. While this does not specifically mention IBM Cognos software, it definitely has good points you can take away. Check the list and see if you can currently answer questions for these KPIs in your firm. If you can, are you getting them answered timely enough? If you can’t answer them, is it due to lack of access to source data? Is data not being captured? Is there no time to produce answers? Do you not have the tools or just no one was asking?

Three criteria to qualify as law firm KPIs:

  • It must be key to the firm's success.
  • It has to be quantifiable.
  • It must reflect the firm's strategies and goals.

However, we would offer a couple of cautionary notes, with the most important being that law firm KPIs should serve as a guide and not a hitching post. KPIs are simply a tool. They are no substitute for the use of common sense and good judgment when it comes to the management of your law firm.


To translate this point into meaningful terms to firms, the following chart divides a sample of common law firm KPIs into the two categories:

Fees billed in a month Number of matters opened
Hours billed Hours worked
Cash receipts Number of average days billing in lockup*
Revenue per lawyer Number of matters per client; number of lawyers billed per client; number of types of matters billed to clients
Effective hourly rate Average fee per matter; average fee per new matter; ratio of average billed to average worked rate (realization)

*This is the sum of the unbilled work in progress and accounts receivable divided by the average fees billed on a daily basis for the past 12 months on a rolling basis (the most recent 365 days, not a fixed year).

Table 1: Business Development KPIs

Client Growth Rate This is a simple measurement to quickly determine if your practice’s client base is growing, or whether growth in revenue has all come from existing clients. This helps firms to focus their business development spend accordingly. This is the ratio of the number of clients that the firm handled its first matter in the past 12 months to the total number of active clients (active can be defined as having handled a matter for in two of the past three years).
Average Fee per Client You would track this to see if your client legal spend with you is growing, flat or declining. It allows you to think about whether the change is rate-driven, client-driven, etc., and then you can respond accordingly. This is the fee revenue for the year divided by the number of clients billed during the year.
Average Fee per New Client Firms use this indicator to measure whether the clients being added are contributing to the overall revenue growth as it is, compared to the same ratio for existing clients, from a cost-of-doing-business perspective; in the long term, firms want to generate more revenue from fewer clients. This is the related fee revenue for the year divided by the number of new clients (clients that the firm handled its first matter for in the past 12 months).
Number of Matters per Client Firms use this as another indicator of growth, because of the focus on existing clients and the ease and lower cost of generating work from them, compared with searching for and landing new clients. This is the ratio of number of matters billed to the number of clients billed and is calculated by dividing the former by the latter.
Client Retention Again, firms use this as another indicator of the health of the practice, because of the focus on existing clients and the ease and lower cost of generating work from them, compared with searching for and landing new clients. This is the ratio of number of clients billed in the last 12 months to the same clients that had been billed in the 12 months before that.
Growth in Top Clients Firms, while growing the business, also want to make sure they don’t become overdependent on any one or small group of clients that could adversely impact the firm’s finances if those clients left. Also, firms want to understand where the bulk of their fees are coming from and where they should focus the bulk of their business development efforts. This is the ratio of fees billed to top 100 clients (number can be adjusted to size of firm) in the past 12 months to the fees billed to the top 100 clients in the 12 months prior to that.
Dormant Client Percentage Clients that you once had are the easiest ones to pursue for new work, so this is important for that reason and also as an early warning sign of whether you have a quality-of service issue to follow up on. This is the ratio of the number of clients that the firm has not handled a matter for in two of the past three years to the number of total clients.

Table 2: Productive KPIs

Percentage of Partners' Hours Firms track this to see who is doing the work in their firms, to address potential issues such as work delegation, complement imbalance, etc. It is seen to be an overall barometer to determine whether partners are working harder or smarter. This is the ratio of partners' hours worked to the total hours worked by all timekeepers.
Billable Hours per Full-Time Equivalent Timekeeper This measure is more relevant than just measuring change in gross billable hours, as the total hours can increase as a result of more timekeepers, but the actual workload per lawyer could be shrinking, resulting in firms facing a situation of more lawyers than work and an imbalance in their complement. This is calculated by taking the gross number of billable hours worked by paralegals, associates and partners and dividing by the number of full-time equivalents in each category.
Ratio of Average Billed to Average Standard Rate Firms use this for a variety of reasons, including extrapolating what the likely impact will be on billed fees of potential rate increases, as an early warning flag of possible quality issues depending on the magnitude of the gap between billed and standard, and to see if there is a billing issue. This is the ratio of the average billed hourly rate for a timekeeping category—which is calculated by dividing fees actually billed by the hours billed—to the average standard hourly rate for the category of timekeeper— which is the value carried in work in progress of the hours that were billed.
Billings per Full- Time Equivalent Some firms look at this in conjunction with their average overhead to ensure profitability is being achieved, to identify potentially underperforming timekeeper groups, to balance complements, and to determine appropriate levels of compensation. This is calculated by dividing the gross amount of fee billings by paralegals, associates and partners by the number of full-time equivalents in their respective categories.
Billable Hours per Legal Assistant Firms use this as a basis for targeting staff levels and increasing or reducing existing complement on a basis that most lawyers understand—the billable hour. This is calculated by dividing the total billable hours for all timekeepers by the number of legal assistants.
Number of Matters Opened Firms, particularly those not driven by billable hours, use this as another measurement of workload, often in conjunction with the Average Fee per Matter. This is calculated by totaling up the number of new matters opened in the past 12-month period and comparing it to similar totals from prior years.

Table 3: Financial KPIs

Net Income Ratio Firms use this for a variety of reasons, including the direction their overhead is going, as a benchmark of profitability that is not subject to the manipulations of how many partners one has today, and as a comparative for compensation. This is the ratio of the firm's net income (income prior to any distributions to partners) divided by the total fee billings of all timekeepers.
Average Net Overhead This is the net cost that each partner must cover before any profits are generated by his or her billings. It is a handy tool for putting relativity into compensation levels. This is calculated by taking the total expenses of the firm before any distributions or salaries are paid to partners minus the billings of all nonpartner timekeepers. This number is then divided by the number of partner full-time equivalents (equity and nonequity
Unbilled Days Firms use this KPI to measure the length of time it takes to bill the work they do. This is calculated by dividing the fee portion of unbilled work in progress by average billings per day (which is calculated by dividing the firm's total fee billings for the past 12 months by 365 days).
Uncollected Days Firms use this KPI to track the length of time it takes to collect their accounts after they are rendered. This is calculated by dividing the fee portion of accounts receivable by average billings per day (which is calculated by dividing the firm's total fee billings for the past 12 months by 365 days).
Charge-Off Percentage Firms use this to track how much of their accounts receivable are actually going uncollected. This is simply the amount of billings written off as uncollectible divided by the fees billed for the year.


Approaching the list of KPIs enumerated in Tables 1 through 3 is similar to the challenge of a buffet. There’s a desire to sample everything, but if you are not careful, you will miss out on the “good stuff.”

Given the breadth of experience of the readers of Law Practice, we have selected five KPIs that most firms, irrespective of the type of work or clients they focus on, are likely to track because of both their ease of calculation and practicality.

Context is critical to the effective use of a KPI. Too many firms only benchmark against their own past experience rather than comparing their KPI metric to the same KPI of other law firms. In part, this is because access to benchmarking data in the legal profession is limited. However, we have managed to obtain benchmarking data for these five KPIs for 2012 and 2011, which is presented in the chart after each KPI description. The source of the benchmark information is Lexis Firm Insight, which is an online solution that provides information ranging from billing rates to collection days and is updated quarterly.

Law Firm KPIs

Our brief analysis of the five law firm KPIs follows:

1. Lockup measures the combined time it takes to bill and collect a firm’s accounts. This is critical to projecting cash flow and projecting when you might need to borrow against your bank credit facility. It is the sum of the Unbilled and Uncollected Days, which is calculated by dividing the unbilled fee portion of a firm’s work in progress and the fee portion of the receivables by the average billings per day. This latter calculation is derived by dividing the Fees Billed for the 12 previous months by 365 days.

Table 1

Unbilled Days 2012 50 63 87
Unbilled Days 2011 51 62 81
Uncollected Days 2012 67 76 80
Uncollected Days 2011 68 78 89
Total Lockup 2012 117 139 173
Total Lockup 2011 119 140 170

2. Billable Hours per Full-Time Equivalent Timekeeper measures the number of hours worked by paralegals, associates and partners. This is more relevant than just measuring change in gross billable hours, as the total hours can increase as a result of more timekeepers, but the actual workload per lawyer could be shrinking, resulting in firms facing a situation of having more lawyers than work and an imbalance in their complement. This KPI is calculated by dividing the gross number of billable hours worked by paralegals, associates and partners by the respective number of full-time equivalents in each category. Note that a full-time equivalent reflects how many actual timekeepers your firm had for the period in question. For example, a lawyer who was with the firm for six of the past 12 months would be treated as one-half of a full-time equivalent in determining your lawyer count.

Table 2

Equity Partners 2012 1,676 1,526 1,440
Equity Partners 2011 1,646 1,559 1,482
Nonequity Partners 2012 1,584 1,438 1,311
Nonequity Partners 2011 1,727 1,504 1,383
Associates 2012 1,740 1,647 1,518
Associates 2011 1,736 1,642 1,544
Paralegals 2012 1,418 1,274 1,151
Paralegals 2011 1,395 1,298 1,172

3. Average Billed Rate measures the effective rate actually billed by the timekeepers (which will frequently differ from their standard or work rate by anywhere from 5 to 11 percent) and is key to monitoring and raising flags as to whether it is potentially client discounting, lawyer discounting or work quality issues. This KPI is calculated by the gross value of time billed for a timekeeping category divided by the related hours billed.

Table 3

Equity Partners 2012 $512 $401 $322
Equity Partners 2011 $514 $390 $315
Nonequity Partners 2012 $393 $334 $285
Nonequity Partners 2011 $378 $334 $271
Associates 2012 $283 $243 $213
Associates 2011 $306 $238 $200
Paralegals 2012 $174 $156 $122
Paralegals 2011 $174 $150 $126

4. Leverage measures the ratio of associates to partners. This measurement is still thought be a critical driver of law firm profitability through the leveraging of a high ratio of associates to partners. (Note that in 2013 and beyond, this should be broadened to include all nonpartner timekeepers ratio to partners.) It is calculated by dividing the associate full-time equivalents by the partner full-time equivalents.

Table 4

2012 .98 .78 .56
2011 1.06 .71 .51

5. Billings per Full-Time Equivalents measures the revenue generation by each timekeeper type, which is important to both budgeting for the future and historical analysis with the intent of not repeating past mistakes, which has included losing overachieving partners due to impact on the firm’s overall profitability. It is calculated by taking the gross amount of fee billings by paralegals, associates, and partners and dividing it by the respective number of full-time equivalents in each category.

Table 5

Equity Partners 2012 $693,391 $532,146 $405,765
Equity Partners 2011 $699,782 $541,316 $430,595
Nonequity Partners 2012 $478,845 $427,130 $339,679
Nonequity Partners 2011 $568,266 $466,581 $333,892
Associates 2012 $449,470 $345,489 $281,084
Associates 2011 $416,833 $346,501 $269,524
Paralegals 2012 $210,135 $171,979 $140,737
Paralegals 2011 $195,793 $167,368 $119,950

Another, but less timely, source of benchmarking data is ALM’s annual Survey of Law Firm Economics. However, it does have the benefit of providing data both by geographical region and firm size.

Before moving on to our conclusion, one note of caution: No single KPI tells the full story. Rather, they must be viewed collectively to achieve the best-informed decision-making results.


The messages that we hope you take away from this law firm KPI article can be summarized as follows:

  • ​Determine what is required for your firm to successfully execute its strategy and then select the law firm KPIs that help you know how you are progressing toward these goals.
  • Ensure you have a balance of leading KPIs and lagging KPIs so they enable your firm to act in a preventative mode as well as measuring progress.
  • No single KPI will provide all the answers that firms seek but rather collective vision is required.
  • KPIs are not that complicated to calculate but you need to be able to mine the data in your systems in order to have proper input.
Law Firm KPIs

As IBM Cognos consultants and business analytics coaches, Lodestar Solutions can guide you in your Business Analytics Roadmap (BAR) to make the most of your IBM Cognos investment. We can also guide you through getting the most functionality out of your IBM Cognos software.

If you are interested in collaborating with other TM1 users in law firms to expand and share your IBM Cognos software knowledge, please contact Serena D'Arpa at

For the full article to which this blog is attributed, please visit the September 2013 article Your Financial Dashboard from the American Bar Association.

And if you'd like to read more about how these KPIs in relation to your dashboard, check out our previous blog on Determining KPIs & Metrics For Your Dashboard.

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.

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/2017Cognos TM1 allows alternate hierarchies which, from a user perspective, is greatly appreciated but from a TM1 Admin perspective, probably[...]
TM1 Websheets to Simplify, Document and Organize TI Process and Tasks
12/22/2016I’m all about easy. It is a phrase I say repeatedly to my clients that are new to IBM Cognos[...]
Are Your IBM Security Certificates About To Expire?
10/4/2016The purpose of SSL certificates (Secure Sockets Layer) are for encrypting data as it is transferred around a network. These[...]
Top TM1 Automation Techniques Used by Expert Consultants
9/20/2016 As a TM1 modeler, you likely have more tasks to do in a day than there is time. You[...]
Why Backup Your TM1 Development Environment?
6/18/2016Hello… Atif Hameed here for Lodestar Solutions. As business analytics coaches, it is our passion to talk about things that[...]
How to: Deleting or Adding Dimensions to TM1 Cubes
6/1/2016Today’s topic is about deleting or adding dimensions to TM1 cubes that you are using in Production. With TM1’s introduction of Performance[...]
Creating a Flexible TM1 Allocation Model
5/3/2016This past week I had to script several TI Processes to make allocations as flexible as possible. This was done[...]
Update TM1 Contributor Views with TI Process
4/13/2016I recently had a client ask how they could go about updating the latest month for users to see when[...]
Why to Upgrade From Excel to Cognos TM1 or Express
  Most people have a love/hate relationship with Excel. They love what it can do but hate when it fails[...]
Naming Conventions in TM1 – Keep Your Data Clean!
2/9/2016As business analytics coaches, it is our passion to talk about things that make your job easier. Today we are[...]
TM1 Data Export in Multiple Columns
2/2/2016In TM1 (aka Performance Management), you can use a Turbo Integrator (TI) process to not only load data, but to[...]
Cognos Command Center – True Integration Capability
1/6/2016As a business analytics coach at Lodestar Solutions, it is our passion to talk about things that make your job[...]
IBM Cognos TM1 10.2.2 Fix Pack 4 Is Available
11/17/15It's here. IBM Cognos TM1 10.2.2 Fix Pack 4 is now available and, along with a list of all the bug fixes[...]
TM1 Perspective Websheets & Active Forms
​TM1 Perspective Websheets & Active FormsTM1 Perspective Websheets & Active Forms can be extremely helpful to take your TM1 data[...]
TM1 Drill Through From Cube to Cube Not Working?
9/30/15If you are going to utilize the Cognos TM1 Architect drill through functionality from one cube to another, there are some considerations[...]
IBM TM1 Applications Web: Better Tool Today
9/2/15As Business Analytics Coaches, it’s our passion and responsibility to find and talk about products that make your job easier.[...]
Understanding Cognos TM1 Licensing
8/26/15​Cognos TM1 licensing - Enterprise vs. ExpressTo better understand the IBM Cognos TM1 licensing functionality and structure, we’ll break it[...]
Moving to Cognos TM1 from Transformer/Powerplay: Pros and Cons
8/4/15As a Business Analytics Coach and resident Transformer/Powerplay expert, I’m often asked about the advantages or disadvantages to moving from[...]
Cognos TM1 TurboIntegrator – Database Table Manners
7/29/15 Cognos TM1 TurboIntegrator (TI) is a great ETL tool that allows you to bring in data to TM1. It[...]
In-Memory Cube Technology = Faster Performance!
Reporting, analysis, budgeting and planning solutions for many years have leveraged multi-dimensional cubes similar to Excel pivot tables but better. [...]
Cognos TM1 TI Processes Using Cubes for Dynamic Variables
4/28/15TM1 cubes can be used for more than calculations and analysis.  They can be utilized as parameter variables for TI[...]
Why Did They Add Cognos Scorecarding to TM1?
4/24/15​Why Did They Add Cognos Scorecarding to TM1?IBM Cognos Metric Studio has been part of the IBM Cognos BI solutions[...]
TM1 ARCHITECT – When Do You Need It?
4/16/15As business analytics coaches, it is our passion to discuss topics that make your job easier. Today we are going[...]
TM1 Operations Console Replaces TM1 Top
3/24/15As a Business Analytics Coach at Lodestar Solutions, it’s my passion and responsibility to enlighten you about functionality that is[...]
Cognos TM1 Reporting Capabilities with BI
As a Lodestar Solutions business analytics coach, clients often ask us to help decipher the intersection of  IBM Cognos licenses[...]
TM1Connect Extract Data From TM1 for Reporting & Analysis
As Business Analytics Coaches, it’s our passion and responsibility to find and talk about products that compliment your existing tool[...]
TM1 Performance Modeler – Creating Model Flow Charts
Using Performance Modeler to create TM1 Model Flow ChartsTwo of our recent blogs described the great benefits of TM1Compare and[...]
The TM1 Migration Process Understood; Can TM1Compare Help?
As Business Analytics coaches at Lodestar Solutions, it’s our passion and responsibility to find and talk about products that make[...]
Documenting TM1 with Pulse by Cubewise
Documenting TM1 with Pulse As Business Analytics Coaches, it is Lodestar Solution's passion and responsibility to find and talk about products[...]
Documenting TM1 with QUBEdocs
QUBEdocs - is this the solution to some of your TM1 challenges? One of the biggest challenges with TM1 has[...]
Publishing TM1 Aliases in Cognos BI
Have you been thinking about publishing TM1 aliases in Cognos BI?  It is completely unintuitive, but if you want to[...]
New Tool for Importing Cognos Planning into TM1
Are you using Cognos Enterprise Planning and looking to move to IBM Cognos Performance Management/TM1?  There is a new tool[...]
Cognos TM1 Sales Compensation Model
As much as sales people say, “I’m not a numbers person. I’ll just let you accountants handle that”, you start[...]
Do you need a TM1 application health check?
There can be many reasons a particular TM1 application has performance issues. TM1 gets its power from the in-memory engine.[...]
What is the IBM Cognos Analytics Server ICAS?
​What is the IBM Cognos Analytics Server ICAS?With all the massive changes by IBM to the IBM Cognos Licensing, people[...]
TM1 Period to Date Dimensions
As Business Analytics coaches for Lodestar Solutions, we come across questions that are asked of us by our clients. A common question[...]
TM1 10.2 Security Changes
With TM1 10.2 security changes, TM1 10.2 now allows you to share/re-use the same dimension in multiple approval applications.  This[...]
How to Improve Your Planning Model
For many in the Finance department, this is the time of year that you start your preparations for next year’s[...]
Cognos BI Reporting – TM1 Data Source, Levels & Alternative Options
Today, we are going to discuss Cognos BI reporting and how it relates to TM1. As many of you know,[...]
Make More Money with TM1 Skills
How can I make more money with TM1 skills?  Learning Cognos TM1 on-the-job can give you the opportunity to advance[...]
TM1 Action Button Merging Parameters
​TM1 Action ButtonTM1 Quirk of the Day: Parameters Merging in TM1 Action Button Properties When using TM1 10.2, I’ve encountered[...]
Cognos TM1 Turbointegrator Processes Quirks
As our Cognos Coaches come across little (and big) “gotcha’s”, we like to share them with you.  Below are a[...]
TM1 Scorecarding and Cognos Metric Studio
TM1 Scorecarding and Cognos Metric Studio, a look into the future!You might ask yourself, what do I need to know[...]
Saving TM1 rules takes too long!
"What's the deal with the TM1 rules I'm writing?!?!" Why does saving TM1 rules take a long time occasionally? I[...]
TM1 Principle Element Name Dilemmas
There are dilemmas when using Skeys, ID # and Name in conjunction with creating a Principal Element Name and/or  Aliases. [...]
Tricks to Working with DAYNO Function in TM1
Tricks to Working with DAYNO Function in TM1In case you encounter some frustration creating a TM1 rule to use the[...]
DimensionElementPrincipleName – What The Heck Is It?
I recently had an issue with a TM1 TI Process that did not want to load data where the data[...]
Exporting TM1 Dimensions
Exporting TM1 Dimensions - quick and easy way to do this through a TI processI was wondering aloud recently about Exporting[...]
TM1 Implementation Methodology for Success
All Cognos TM1 implementations require a strategy to deliver an appropriate reporting solution. This is the plan that will ensure[...]
Departmental Data Warehouse Options
Are you a data chaser?  Are you spending hours of your time grabbing data from various data sources, throwing the[...]
Migrating IBM Cognos Planning to TM1
Are you thinking about moving from IBM Cognos Planning to TM1? Well, let me tell you, for those of you who have been using Cognos Planning, moving to TM1 will be like going from having a pot-luck dinner to a catered gourmet dinner. The biggest difference is the cube sizes. TM1 can handle HUGE cubes of data. And yes, size does matter. If you currently have planning models where you created separate applications and use Admin Links to pull your data together because the models were too big for Planning, you will LOVE TM1. Lodestar has migrated a large number of Planning clients to TM1 and many have been able to consolidate their models to simplify maintenance. You still have the ability to limit users to what they need to view and edit, but it will all be in the same database.
TM1 Element Security Within a Dimension
SECURITY_ELEMENT Note: If }ElementSecurity_dimensionname Control Cube does not exist this must be executed first Assigns Security to Elements within a Dimension Creates/Updates }ElementSecurity_dimensionname
Use TM1 Cubes as Data Dictionary & Analysis Report Usage
For many companies, the same data/metric term has different meanings for different departments. An example of this would be “Customer”. To the sales department, it may mean they have a new customer that has been entered into the system and now has an account number. To Finance, “Customer” may mean that this person has been sent at least one shipment within the last 12 months.
Cascade Cognos TM1 Security from Parent to all Children
Turbo Integrator's security cascades from parent to all children and is dimension specific. After the initial security (access) is loaded, the level of the Dept variable (1st column in the text file) is checked. If the level is greater than zero, the elements in the Department dimension are cycled.
Unable to Launch Cognos TM1 Perspectives via Microsoft?
From time to time, Microsoft updates may cause an error and not allow you to use TM1 Perspectives. If you encounter this, please follow the instructions below:
Don't Drive Blind – Incorporate Planning Data with TM1
Every company is wrapped up in their own data. How do we capture our data? How do we analyze our data? How do we report on our data? It seems like the ultimate goal in many companies is how to report on their data and get through the monthly meetings with a pretty presentation.
TM1 Dynamic Subset with Dimensional Data
You create a dynamic subset with a specific consolidated item and the next time the dimension is updated, the subset takes on a mind of its own. . .and you recreate it. Been there, done that – right? Try the following:
Creating TM1 Dynamic Subset of Individual Elements
Most of us have been there - laboriously scrolling through a dimension containing dozens or hundreds of elements. The goal is to select 3 or 4 elements for creating that one of a kind subset. A few minutes of scrolling . . .success. Then you realize you left out one item. And so the scroll and click begins once again. Ick. Here’s how to replace scroll and click. . .
IBM Cognos Express Approval Hierarchy Considerations
The same strategy works when using TM1, or in this case, Planner, within IBM Cognos Express. Unlike the Enterprise version of Cognos where the administrator can create new TM1 server instances, only a single TM1 service is allowed in Express. An approval hierarchy can be used only once on a given TM1 server. Therefore, if a dimension is assigned to a Planner application as its approval hierarchy, that dimension cannot be used again as an approval hierarchy in any other application.
Cognos TM1 Data Security
The ability to access cube data in TM1 changes significantly once the TM1 application is deployed. While we are just starting off building our model in TM1 Performance Modeler, the cells within our cube are writable as long as they are not a calculated element. Below in our Revenue cube, we can manually enter data into any of the cells that appear white.
Copying a TM1 Service to Another Server
Sometimes retaining an image can be a nice trip down memory lane, but in the case of copying a TM1 service from one environment to another, it can create an error. We recently built a simple TM1 model and deployed the TM1 Web application for use as a demonstration model. We wanted to keep a backup copy of it on another server. We copied over all of the data and log files to the second machine, including the tm1.cfg file. We then added the new service instance to IBM Cognos Configuration for TM1.
Create TM1 Dimensions in Excel Worksheet
Though the preferred method for creating dimensions in TM1 is via a Turbo Integrator Process, sometimes you may want to do this with a dimension worksheet. With a dimension worksheet, you can incorporate Excel functionality to do concatenations that will populate your attributes. This works nicely for Aliases where you would like to use the element name with a description. The example below shows you how to do this for an Accounts dimension.
Creating A New TM1 Server
I could understand the frustration of a new modeler who could not gain access to the new TM1 server he had just created. He closely followed the set of instructions he had again and again to no avail. First, he had copied the tm1s.cfg file from an existing operating model on the same network, changing the ServerName, PortNumber, LoggingDirectory and DatabaseDirectory within that new file to reflect the name and location of the new server he was setting up. Second, he created a new server instance in IBM Cognos Configuration and edited the location to point to the directory from the tm1s.cfg file resided.
Different Methods to TM1 Model Development
TM1 allows for different preferences and with the release of version 10, offers the end user yet another way of developing a model. Performance Modeler is even more user friendly than the familiar modeling tools of Architect and the Excel add-in Perspectives. Performance Modeler is an excellent path to take for the beginning modeler. Its many built-in wizards will guide the end user through the building and populating of cubes. Experienced modelers may prefer to stick with the familiar environment of Architect, instead of learning the nuances of a new tool. Many finance folks are married to Excel and want to remain in a formula driven workspace.
Stored Procedures in TM1 Turbo Integrator
As many of you know, when you use Turbo Integrator to import data and metadata into TM1, you can bring it in from many sources. You can use comma-delimited ASCII files, other TM1 cubes and views, MSAS (Microsoft Analysis Service) and relational database tables that are accessible through an ODBC connection. For this blog, we are going to concentrate on relation database tables through SQL and what you may not know is that using multiple tables and writing multiple joins query in SQL using the Turbo Integrator query box can make the pull of the data very slow!
Migrating Planning to TM1
IBM Cognos TM1 is architected very differently than Cognos Planning. Plannings distributed architecture and cube structures lead to limitation in model sizes. IBM Cognos TM1 is a powerful 64-bit, in memory solutions that has tremendous power if model are designed considering TM1s power and functionality. For Cognos Planning clients to fully realize the power of TM1, Lodestar Solutions recommends a model “Reconstruction".
What's New: Cognos Insight 10.1.1
What’s New in IBM Cognos Insight 10.1.1? 1. Percentage of Total Calculations 2. Calculations in Parent 3. Time Dimension Import 4. Chart Interaction 5. Improved Usability Features a. Simplified Terminology b. Access to Formatting c. Keyboard Shortcuts 6. Guided Import 7. IBM Cognos Insight Trial Edition
Execute Multiple TM1 TI Processes with Parallel Loading
As a TM1 power user, you are aware that a Turbo Integrator process sets a lock to the cube, which is loaded in a dimension maintenance function. This lock can cause end users to wait until the TI process is complete and the lock has been released. If you have a large amount of data to load, this can be very annoying to the end users, seeing that they have to wait a long time. So, it is recommended that you separate the Metadata maintenance from the data maintenance in separate TI processes and run them as a chore.
TM1 Contributor Rights and Functionality Explained
TM1 Enterprise Planning Contributor is the primary licenses end users of TM1 utilize. It allows for end users to have read and write access to the TM1 models. Under the TM1 Contributor licensing, End users have the following access rights:
What is Cognos TM1 Performance Modeler?
IBM Cognos TM1 10 includes a component called the Performance Modeler. It’s a great step in the right direction for IBM to simplify the model building process in TM1. It’s a wizard based component that will allow less technical people to manage and maintain models. However, if you are an existing IBM Cognos TM1 user who is considering moving to IBM Cognos TM1 Performance Modeler, you should be aware of the following:
Consultant's Cognos TM1 Tricks (Part 2)
6. LEVERAGING CONTROL CUBES FOR SECURITY - Control Cubes can be useful to view attribute or security information. If migrating from Planning, it may even be possible to use an existing Excel spreadsheet to load security via a TI Process [into the Control Cube].
Consultant's Cognos TM1 Tricks (Part 1)
CREATING ALIASES - When creating aliases, use consistent attribute names. Name or Desc will usually suffice. (We frequently find that name is used on one dimension, desc is used on a couple others and description is used on a few more.) This reduces those “uh oh, what did I call that attribute” moments when creating a process or updating rules.
Incorporate TM1 Naming Conventions in Your Model Build
TM1 is great for organizing your planning models. Make the most of this and incorporate a naming convention for your dimensions, views and TI processes. This makes building, maintenance and training much easier in the long run, especially when you have more than one Admin. Design a simple blueprint of your naming convention at the beginning of your implementation and keep it in your application folder for quick reference. Another tip is to use the underscore in lieu of a space in titles, whether it be your cubes, dimensions or the title row of your data import. For example, when creating a dimension using TI, I prefer that the variable name to show up as “Element_Name”, instead of “v1” if the underscore is not used, as would be the case if the column title was “Element Name” vs “Element_Name”.
Moving from IBM Cognos Planning to TM1 Part 2
This is the second piece to the story on why an IBM Cognos Planning client would consider moving to IBM Cognos TM1. Power User Benefits- The power user license is the TM1 Modeler. The role is similar the Enterprise Planning Modeler in that this person typically is responsible for building, modifying and deploying the TM1 Planning model to the end users (Contributors).
Moving From IBM Cognos Planning to TM1 Part 1
There’s been a lot of discussion on why an IBM Cognos Planning client would consider moving to IBM Cognos TM1. So, I thought I would do a little brain dump on my experience with clients. Today, I’ll start with the End User. End User Benefits- End users can choose from a couple different user interfaces. I will focus on the TM1 Contributor user interface for TM1 as it closely resembles Planning Contributor. It includes some functionality we have been requesting for years.
8 Keys to Successful Cognos Dashboards
The purpose of Organizational Dashboards is to create clear visibility in your organization which leads to better decision making. So why do so many organization struggle with Dashboards? Effective Dashboards require: Reliable and timely source data Measures should motivate behavior to achieve Key Performance Indicators Clear definitions of measures – ie Does “Sales” include returns and allowances? continued ...
Integrating TM1 9.5 and Cognos BI – Part 2
​Integrating TM1 9.5 and Cognos BI – Part 2We continue from the previous Lodestar blog on Integrating TM1 9.5 and[...]
Integrating TM1 9.5 and Cognos BI – Part 1
IBM Cognos 10 greatly improves the performance of BI reports that use TM1 as a data source, but many clients aren’t leveraging the integration in their Cognos environment. Below are Lodestar guidelines:
Minimal IT for TM1 Implementation
We are constantly being asked by finance and other areas of the business if they can implement IBM Cognos TM1 without IT involvement and the answer is NO! However, with proper planning you can implement with minimal involvement. The key is to use IT strategically. They are very valuable to your project as they often have insight to your data structures, sources and potential data integrity challenges. When IT is actively involved in the project and partnering with the business, success is rapidly accomplished.
Parallel Interaction in TM1 9.5.2 Improves Write Performance
Looking for performance improvement in IBM Cognos TM1?  IBM added a new configuration in TM1 9.5.2 to improve write performance[...]
Improve TM1 Performance Via Cube Dependencies
During Server load, TM1 establishes cube dependencies based on feeder statements in rule files. Feeder statements that have data dependent cube references or rules files containing ATTRN or ATTRS functions will not establish Cube Dependencies during server load – rather they will be established during a first time query or data update invoking these cross-cube rules . The result can be that a query or Turbo Integrator Process could trigger a cube dependency where the system goes out and evaluates what the dependencies are required before giving results. If this happens during periods of user activity, this could block objects and cause contention issues for concurrent readers and writers. So, the user will complain the system is slow!
How to Kill a TM1 Operation – TM1 Top
The TM1 Top utility enables you to dynamically monitor the threads/operations running in an instance of the TM1 server. TM1 Top is a stand-alone utility that runs within a console (command) window on a Microsoft Windows system. It is designed to make minimal demands on the TM1 server and the supporting network and system. With the exception of a user-initiated login process, TM1 Top does not use any cube or dimension resources in the TM1 server, and does not use or interact with the data or locks on the TM1 server.

Why Backup Your TM1 Development Environment?


Backup Your TM1 Development EnvironmentHello… Atif Hameed here for Lodestar Solutions. As business analytics coaches, it is our passion to talk about things that make your job easier, save you time, and even more importantly, save you & your company money! Today, we are going to talk about the subject of your TM1 development environment and backing up your server. This subject seems trivial on the surface but if you are faced with a negative situation, it can be very costly and stressful.

Most production servers are backed up on a daily basis but some TM1 development environment servers are neglected when it comes to setting up daily or weekly backups. We can think of two reasons why it is important that our readers should consider setting up this backup. First, the development server has things changing and updating on a daily basis. There are solutions being created and some of these solutions have taken a long time to develop. Time is money and it is highly imperative that this development effort gets preserved. Loss of any progress can also be very demoralizing to the development team. Secondly, if there are multiple people developing in the server, you could be wiping out each other’s work if things are not coordinated properly. If there are periodic backups setup for your TM1 development environment, things can be restored and potential issues can be avoided.

Planning in advance and setting up a server backup using a simple TI that zips the data directory once or twice a day is a very simple but important suggestion. It can save a lot of headaches and help you sleep better knowing that you’ve got your bases covered.

For more information on why you should buy a Cognos BI or TM1 non-production environment, visit one of our previously written blogs HERE or simply contact us at

How to: Deleting or Adding Dimensions to TM1 Cubes


Adding Dimensions to TM1 CubesToday’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 or check out this link for the IBM Performance Modeler user guide:


Creating a Flexible TM1 Allocation Model


TM1 Allocation Model

This past week I had to script several TI Processes to make allocations as flexible as possible. This was done so that the users could pick and choose what they wanted to allocate like it was a buffet at Golden Corral. While doing the usual creating of subsets on the fly in the Prolog tab so that my source data is dynamically created by their choices, I realized their need to pick a consolidated element (i.e., all Departments), but they would also like to exclude a Department or two from the initial selection of all Departments.

I came across the TI function called SubsetGetElementName and thought I found the answer to my problem. The syntax in the User Guide shows it as SubsetGetElementName(DimName, SubsetName, ElementIndex);. Well that looked simple enough and I then used DIMIX( DimName, ElementName) in place of the ElementIndex and thought all was good. I was wrong.

Apparently, the ElementIndex is the position of the element within the subset and not the element’s index in the dimension. So my solution was to add extra coding to the Prolog tab.

Here’s the basic gist of what I did:

  1. Use the function AttrInsert to create a numeric attribute to house the subset position number for each element added to the subset.
  2. Create a subset of only the N level elements from the C level element input in a parameter cube for the department selection (ie Total Dept). This requires looping through its children and inserting them into a subset.
  3. Next, While through the subset and use the SubsetGetElementName and AttrPutN to populate your numeric attribute with the position number of each element in the subset.
  4. You can now loop through your elements that house your exclusion elements and use the SubsetElementDelete to remove those elements from the subset, since you are able to retrieve the element’s position number now that it is a numeric attribute of the element itself.
  5. Finally, on the Epilog tab, I got rid of the numeric attribute that was housing the elements’ positions in the subset using the AttrDelete function, thus allowing for it to be dynamic and update the element position the next time the process is run.

Hope that helps you! If you have any questions about this article or TI Processes in general, do not hesitate to contact Lodestar Solutions at where our Analytics Coaches can assist you.

Why to Upgrade From Excel to Cognos TM1 or Express


Upgrade from excel to Cognos

Most people have a love/hate relationship with Excel. They love what it can do but hate when it fails because we try force it to do what it was clearly not built for.  In this day and age, businesses are being overwhelmed by data and need a system in place to analyze it all quickly, provide accurate information yet nimble enough to drill down into the most minute details. This is why we suggest an upgrade from excel to Cognos TM1 or Express.

A 2008 analysis of multiple studies suggests that 88% of spreadsheet documents contain errors. The larger the spreadsheet, the more undetected errors there will be which is being used to make bad decisions or even inflate/deflate your company’s profitability. You can read more about errors at

How can Cognos TM1 or Express solve Excel problems?

Multi-User system – Excel is a single user tool which allows for only one person at a time to input or update data. Cognos allows everyone with a license and access to work in collaboration and at the same time.

Distribution is a breeze – Instead of creating/saving multiple versions of a spreadsheet for each user and affixing various security setting on specific cells per user, Cognos uses the security already built into the system. Everyone who should be able to see and update information does.  And with the embedded security in Cognos, confidential information remains that.  You don’t have to worry about the wrong document accidentally being forwarded around the office or outside the business.

Version Control – How many times have you created and sent out an Excel spreadsheet to many users only to realize there was an incorrect formula or vital information not included? You go back and make those changes and send out the latest version.  Depending on how complex your spreadsheets are and how many users it is being sent to, this cyclical problem quickly becomes an unending nightmare of confusion.  Which is the latest version?  I didn’t get a copy of THAT version, etc.  With Cognos, the changes and updates are made once and the business logic is updated across the board.

Linking – Data in Excel is often linked to other cells or even other worksheets. These links have a tendency to break causing data confusion & frustration to identify and fix.  When someone adds extra rows or columns to their version of the Excel document, it isn’t reflected in everyone’s document. With Cognos, changes are immediately shown (depending on access) to all users as well as built in functionality to check links. (not sure this sentence fits here)

Viewing data in different ways – If you have an executive that wants to view data one way while another wants to view it differently, this takes time and finesse to alter. However, Cognos makes it easy to view data from various perspectives and perform ‘what if’ analysis.

Saving Time and Money – Excel is a program that requires substantial manpower whereas a system like Cognos has more automation and built in user controls. What does that mean to the bottom line of the company?  Less hours are required by employees trying to find answers and maintaining the spreadsheets.  Cognos frees up their nights and weekends while allowing these employees to actually analyze the data instead of chasing it or putting a band-aid on it.

Excel is a great tool for the right situations. Using it for budgeting, forecasting or when multiple user access is required is just not one of them.

To learn more about Cognos and how it can get you out of “Excel Hell” with an upgrade from excel to Congos, email us at or call 813-248-2040.  You can read our blog on the differences between Cognos TM1 and Cognos Express here

Cognos TM1 TurboIntegrator – Database Table Manners


Cognos TM1 TurboIntegrator (TI) is a great ETL tool that allows you to bring in data to TM1. It also allows you to drill down to an ODBC connection. To get a more seamless “communication” between TM1 and your database, you want to keep a couple things in mind when building your database tables. Here are some database table manners tips:

Do not use spaces or TM1 restricted characters in your headers. If you do, when you get to the Variables tab in Cognos TM1 TurboIntegrator, you will then get an ambiguous variable name. So if your first column is called “Department Name”, you will get “v1” as your Variable name. Not much of a problem if you have only a few columns and the sample value for that column is easily recognizable as to what it is. This is because you do have the option to rename the variable in TM1. However, that is adding an additional step and if you are using the same table in several TI Processes, then the additional step multiplies into many additional steps. There are also some reserved words in TM1. Year and Month are examples, so it would be preferable to call those Years and Months, as the plural is not a reserved word.

Click here for an overview of TM1 Object Naming Conventions.

Cognos TM1 TurboIntegrator

Elements in TM1 dimensions are always considered text/string data. Therefore, format elements such as Year (ie 2015) as text. That way you do not have to convert numeric values to string with a user created variable. Likewise, if you are passing a TM1 element as a parameter in a drill to an ODBC database, TM1 will pass back a string value. This is unless you code it to convert to numeric. If drilling back to an Oracle database, you may need to add additional scripting in your TI Process to convert your parameters passed to be in Upper Case.

During a build phase, you may need to add additional information to your table. If you are the one that is updating the SQL code in the TI process, please add the addition columns in your query at the end of the existing list of columns. If you randomly insert the new columns, it requires the TM1 admin to rework the Variables tab due to the order change. Also, changing the format of an existing column can disrupt coding for the TM1 admin.

If you have any other questions about the Cognos TM1 TurboIntegrator or any other general concerns, do not hesitate to contact us at

TM1 ARCHITECT – When Do You Need It?


As business analytics coaches, it is our passion to discuss topics that make your job easier. Today we are going to talk about a subject that TM1 clients have recently mentioned in conversations.  The subject is about TM1 Architect. Many of our clients have models that have been built utilizing TM1 Architect.  Some of these clients are having difficulty either understanding the model logic or troubleshooting when issues arise. The question then becomes, how many models are being built with Cognos TM1 Performance Modeler? There is no data on the ratio of models built in PM vs Architect but it seems like most complex models are still being built utilizing TM1 Architect.  Even though these models are being built with the intent of being turnkey and maintenance free, the fact is that issues still come up. This requires you to have specific knowledge of how TM1 really works under the covers in order to fix these issues. Gaining a little bit of this knowledge goes a long way in troubleshooting minor issues. Especially if your models is legacy and pre PM days.

TM1 Architect

Here at Lodestar Solutions, we are working on developing a training class that will do just that. It will focus on everything related to TM1 Architect and enable you to effectively manage your TM1 Architect specific models. The curriculum of the class will include the following:

  • Basic and brief explanation of in-memory multidimensional modelling (You should know most of this from your PM training)
  • The anatomy of TM1
  • Elements – Figure out your Metadata
  • Dimensions – Create dimensions from your Metadata
  • Subsets – Create Subsets of Elements
  • Attributes – Create Attributes for the Dimensions
  • Cubes – Create Cubes from the Dimensions
  • Rules – Create Rules
  • Views – Create Views
  • TI processes – Use them in TI processes and create ETL
  • Chores – Schedule TI Processes

The underlying purpose of this class is that you will take away these items:

  • You can understand what your consulting partner is building and how to troubleshoot if any issues arise
  • You can build better models in PM because you get a deeper understand of the inner workings of the OLAP engine and its capabilities

How to Improve Your Planning Model

For many in the Finance department, this is the time of year that you start your preparations for next year’s budget.  If you are lucky enough to have a financial performance management software package, such as IBM Cognos TM1 or IBM Cognos Planning, you’re accommodating your model for the upcoming year.  Question is, How to Improve Your Planning Model?  Many times, we are so far into the process of just preparing a model for data input that we don’t spend the time to connect all the dots of that input.  This is especially true for companies that recently implemented a budgeting tool.  Most likely, you had a limited budget and an already lean staff that worked double-duty in building the model and taking care of their day job as well.  So you are finally out of the Excel world and have a budget that consolidates in real time.

Mission accomplished…not quite yet.  If you’re still a newbie with your software product, you may not truly know its capabilities.

“If I had asked people what they wanted, they would have said faster horses.” — Henry Ford

Sometimes you need guidance on what the future can be like.  Using your IBM Cognos product is a great improvement from what you had in Excel, but is your model truly driver based?  Do you have calculations that assess the viability of your inputs?  How about calculations that show the results of productivity for your estimates vs last year and vs a three year average?  What about variance analysis that shows how accurate your Operations Managers are at budgeting?

There still is only 24 hours in a day and that’s not ever going to change.  If your staff is already working at full tilt, Lodestar Solutions is here to help.  We can review your model with you and offer suggestions to improve actionable decision-making capabilities, as well as efficiencies of existing processes and additional automation.  From there, you can decide to do the building yourself or have one of our experienced coaches do the work for you.  Most times, these model improvements can be done remotely, saving you travel and expense costs.  With web meetings, we are able to do knowledge transfer with your staff to improve their skills and help them to be more self-sufficient.

Budgeting is not just a “season”, else you could hunt it down and shoot it.  It’s your future!