Should we convert our Access & Excel FP&A to TM1?

[mnbv]

Hello,

I work for a company creating 120 management reports a month using just 1 excel template. The reports are in excel and are quite complicated - they contain many sheets and the user can use a user form to filter by cost centres, cost centre owners, countries, and display in local currency or Euros. The source data is downloaded from SAP, uploaded to Access, mapped in Access and then exported into the excel templates.

We also create 120 budget and template forecasts every 3 months using a 1 excel template. For each department we preload the previous year's actuals and the latest forecasts (or budget) for the current year. This is done by downloading data from SAP into Access, then creating forecast files in Excel. These are then sent back to us and we collate them into a single file before uploading to SAP (we use SAP FICO).

All in all this is a bit of a nightmare - there is a massive reliance on VBA which appears to be quite buggy and of course we have business continuity problems if a member of staff leaves. And for the people doing all this work (VBA/Access) it is a pretty deadend job, which again leads to business continuity problems due to high staff turnover.

I was wondering if the above sounds like we should be thinking about using Cognos TM1, do you think it would be suitable? (Also as we work for a very large bluechip firm we will struggle to make 'global' policy changes - if our department was to use Cognos TM1 would external departments (there are about 120 of them) without TM1 be able to read our cost centre reports and submit forecasts?)

Thanks,

mnbv

[jim wood]

For me the question should be: Should we move to TM1 or Cognos Express? To answer that question you need to look at the number of users you have. If you have less than 100 then Express is viable option for you and much cheaper and from the sounds of it, a better fit for what you're doing,

Jim.

PS. A you might have guessed, the answer to your original question is yes.

[tomok]

(Also as we work for a very large bluechip firm

You may not qualify for Cognos Express since there are limits to company size. CX is aimed at mid-market companies and IBM doesn't want to sell it to companies that could afford full blown TM1. In the end, only your IBM Sales Rep is going to know the answer, but yes, TM1 and CX are perfect for the scenario you've described. The key is going to be getting some qualified help. You get what you pay for when it comes to TM1 consulting. Going the cheap route is only going to cost you more money in the long run in implementation delays and bad design decisions.

[mnbv]

Great, thank you both.

The problem is that we have 120 'customers' that we send reports to and receive forecasts from - and there is no chance they'll all agree to pay for licences, and no chance of organizing/persuading IT to implement software across a dozen different entities (or arranging some king of secure web access solution)... so is there a solution that has us paying for the 'parent' software and then lets us somehow send the reports (or forecast submission files) out (e.g. distributed through Microsoft Sharepoint?) or do the 'customers' need to be fully implemented into the solution (with TM1/Express installed on their computer and individual user licences?)

Thanks very much for your help,

mnbv

p.s. regarding the 'Express' option, we might be able to limit to 100 users but our company has a whole has 100,000's of employees - would that be OK?

[jim wood]

Tomok is correct. Your company size will be too big. You'll have to use TM1 corporate edition. As for deploying this out to customers, you could use TM1 Web. You can set this up so people outside your firewall can access it. Then create a user id for each customer. They will then logon and access reports. Within TM1 you have the option of either deploying cube views or you can also deploy reports designed in Excel.

There will obviously be some overhead to this. You'll need to manage the ID's and the security access for each customer but the reports will then be self service. Being a large company and the nature of the information I'm guessing your security / IT department will want to review. This is something that will hit you regardless if you choose a self service approach.

You'll then obviously have to buy a license in accordance with IBM's licensing model, but this way your customers don't have to install or buy any software.

You could also purchase Cognos BI. If you access the cubes via BI you'll be able to set it up so it automatically emails PDF versions of the reports to the customers. Again you have the over head of setting things up and also maintaining the email setup but this is less of a risk from a security point of view.

The choice is yours,

Jim.

[tomok]

The problem is that we have 120 'customers' that we send reports to and receive forecasts from - and there is no chance they'll all agree to pay for licences, and no chance of organizing/persuading IT to implement software across a dozen different entities (or arranging some king of secure web access solution)... so is there a solution that has us paying for the 'parent' software and then lets us somehow send the reports (or forecast submission files) out (e.g. distributed through Microsoft Sharepoint?) or do the 'customers' need to be fully implemented into the solution (with TM1/Express installed on their computer and individual user licences?)

Yes, it is technically possible to do this and quite easy if sending range-value reports. TM1 has a built-in Print Report feature than you can use to create in batch, a series of Excel sheets with range-valued data. When it comes to accepting those forms back it's a little more complicated. You have to add some DBS formulas (Excel formulas that send data to TM1) and/or add some VBA coding. Technically possible but not "easy" on the writeback. I wouldn't attempt this without some competent help from someone that has done this before.

[stephen waters]

Tomok is correct. Your company size will be too bigJim.

Jim,
Can I clarify\correct this. The Cognos Express licensing is limited, not by the size of the organisation, but by the number of users. From IBM licencing viewpoint it is perfectly acceptable to have a departmental CX system in a large multi-national and we have several customers like that.

If you approach your IBM sales rep they may not tell you this (they would prefer to sell you the Enterprise edition at a much higher price!) but if you talk to one of your local partners who specialise in TM1, they will be able to help.

Current CX user limits are,
- maximum of 100 named users per CX instance.
- of those 100 users, a maximum of 50 can be "Performance Management" ie TM1 read\write users.
The processor poser is also now limited to 560 PVU per instance, eg 2 x Quad core processors.

Some people may not be aware that the CX licensing was simplified last year with the release of C X10.2.1. You now have either "Performance Management" or "Reporting" (effectively TM1 or BI) users rather than messing around with Planner\Advisor etc. Upgrading to Enterprise version is also now easier and simpler.

Coming back to the OP, as others have said, TM1\CX would be perfect for you problem, a classic application. With under a 100 users you could use CX to generate Excel\Web\Full BI reports, giving you access to eg Active reports on iPad which look great. With more than 100 users you could either go Enterprise or, as others have suggested, use the TM1 report generation option without the need for licenses.

[mnbv]

Great, thank you all.

Please could I ask a few more questions?
1. If we go the CX route, can I just double check that below is the situation for receiving data from end users for forecast sumbissions and that there deffinately isn't an easier way [like just dropping all the received forecast submissions files into a folder and pressing a magic "collate" button! ]:

When it comes to accepting those forms back it's a little more complicated. You have to add some DBS formulas (Excel formulas that send data to TM1) and/or add some VBA coding. Technically possible but not "easy" on the writeback. I wouldn't attempt this without some competent help from someone that has done this before.

2. How much does CX cost for a single installation? (I think TM1 is about $15k plus consulting costs, but I can't find info on CX costs?)

3. If we buy 2 installations (but only use 1) would we then have 200 (100 x 2) user licences?

Thank you everyone,

mnbv

[declanr]

Great, thank you all.

Please could I ask a few more questions?
1. If we go the CX route, can I just double check that below is the situation for receiving data from end users for forecast sumbissions and that there deffinately isn't an easier way [like just dropping all the received forecast submissions files into a folder and pressing a magic "collate" button! ]:

When it comes to accepting those forms back it's a little more complicated. You have to add some DBS formulas (Excel formulas that send data to TM1) and/or add some VBA coding. Technically possible but not "easy" on the writeback. I wouldn't attempt this without some competent help from someone that has done this before.

2. How much does CX cost for a single installation? (I think TM1 is about $15k plus consulting costs, but I can't find info on CX costs?)

3. If we buy 2 installations (but only use 1) would we then have 200 (100 x 2) user licences?

Thank you everyone,

mnbv

1/ Yes; if you spit out a csv file for each submission into a folder (name them all as Department_125.csv) etc - you can then use a TI that while loops through all CSV files and initiates a second process that will use the csv file as a datasource and pass the name of the file through so it knows what destination it goes against.

2/ If you mean using TM1 properly (i.e. on a server) then your estimation is way too low, if you mean just sticking it on a single PC then its probably too high (not sure on the exact details in dollar terms however.) You can contact any business partner or IBM and they should be willing to give you a good estimate of list price in your country.

3/ No. If you want more than 100 users to connect to the 1 server (that's only 50 write users and 50 read) you need to go full fat and Cognos Express wouldn't cover it.

[tomok]

1. If we go the CX route, can I just double check that below is the situation for receiving data from end users for forecast sumbissions and that there deffinately isn't an easier way [like just dropping all the received forecast submissions files into a folder and pressing a magic "collate" button! ]:

Yes, you could have your users export the contents of their file to CSV and put it in a special folder. That was the "and/or VBA" part of my answer. You'll want VBA for this because you don't want to leave that task to your users. You'll want the CSV file structured properly, meaning skipping all the parts of the "report" that aren't actually part of it and to make sure the data and the metadata are all normalized and in columns. If not, it can get real messy. If you leave this up to the users, i.e., making them cherry pick the right stuff and then saving as CSV, they'll hose it up beyond belief. If you don't lock down the sheets and program in the "save to CSV" part of the process you'll have all sorts of issues.

[rmackenzie]

1. If we go the CX route, can I just double check that below is the situation for receiving data from end users for forecast sumbissions and that there deffinately isn't an easier way [like just dropping all the received forecast submissions files into a folder and pressing a magic "collate" button! ]:

When it comes to accepting those forms back it's a little more complicated. You have to add some DBS formulas (Excel formulas that send data to TM1) and/or add some VBA coding. Technically possible but not "easy" on the writeback. I wouldn't attempt this without some competent help from someone that has done this before.

Perhaps I am a little delirious but I seem to recall a rather large, multinational financial institution implementing this sort of thing and perhaps there are mixed opinions about the real value of running this sort of solution. As Tom says, it is possible but the 'not easy' bit deserves a costing exercise comparing the ongoing cost versus paying for enterprise TM1. If you don't have the 100 max user limit then you can better consider a robustly architected and properly delivered distributed solution that doesn't rely on CSV/ VBA/ offline spreadsheets.

If you're in the market for TM1 then any quality IBM partner/ consulting outfit should be able to help you with reference sites (i.e. other companies that purchased and implemented TM1) that will enable you to get some real-life feedback about this sort of implementation, and that will guide you as to whether this makes sense for you in the mid- to long-term.

[jim wood]

Can I clarify\correct this. The Cognos Express licensing is limited, not by the size of the organisation, but by the number of users. From IBM licencing viewpoint it is perfectly acceptable to have a departmental CX system in a large multi-national and we have several customers like that.

If you approach your IBM sales rep they may not tell you this (they would prefer to sell you the Enterprise edition at a much higher price!) but if you talk to one of your local partners who specialise in TM1, they will be able to help.

Current CX user limits are,
- maximum of 100 named users per CX instance.
- of those 100 users, a maximum of 50 can be "Performance Management" ie TM1 read\write users.
The processor poser is also now limited to 560 PVU per instance, eg 2 x Quad core processors.

Some people may not be aware that the CX licensing was simplified last year with the release of C X10.2.1. You now have either "Performance Management" or "Reporting" (effectively TM1 or BI) users rather than messing around with Planner\Advisor etc. Upgrading to Enterprise version is also now easier and simpler.

Coming back to the OP, as others have said, TM1\CX would be perfect for you problem, a classic application. With under a 100 users you could use CX to generate Excel\Web\Full BI reports, giving you access to eg Active reports on iPad which look great. With more than 100 users you could either go Enterprise or, as others have suggested, use the TM1 report generation option without the need for licenses.

Stephen It would indeed depend on the IBM rep. The Reps over here would as you say push the enterprise route. As a TM1 partner we would be actively encouraged to sell the enterprise option in this case, if the user base was below 100. I'm also sure that IBM would dress the deal up in a way that would make it very attractive as well. Also keep in mind that the IT department within most blue chip companies will look at scalability. The 100 user limitation even with an upgrade path would call Express in to question. Keep in mind (apologies to my colleagues) that business is done slightly differently over here. A lot of the time common sense flies out of the window. The "We are big so we must act this way" kind of scenario.

[mnbv]

If you don't have the 100 max user limit then you can better consider a robustly architected and properly delivered distributed solution that doesn't rely on CSV/ VBA/ offline spreadsheets.

Sorry I'm a bit lost What would that be exactly?

Thanks,

mnbv

[tomok]

If you don't have the 100 max user limit then you can better consider a robustly architected and properly delivered distributed solution that doesn't rely on CSV/ VBA/ offline spreadsheets.

Sorry I'm a bit lost What would that be exactly?

Thanks,

mnbv

i fully-licensed version of TM1. If all the data contributors were licensed you wouldn't have to do the CSV workaround to get their data.

[mnbv]

i fully-licensed version of TM1. If all the data contributors were licensed you wouldn't have to do the CSV workaround to get their data.

Thank you

I just found out today that we had TM1 full implementation before I arrived, but the resulting consultant fees resulting in it being removed (!) So I guess politcally full TM1 will be a non starter but I'm not sure they've heard of express so maybe I can propose that as a standalone solution. (although I'm not sure it's that efficient for the forecast submissions as mentioned by others, so maybe this would be for reporting only)

Thanks,

mnbv

[jim wood]

Have you stopped you support payments for the software? If you haven't then you will be able to login to the IBM website, create a login and download the software. I would speak to the powers that be. Just because you're no longer using it, it doesn't mean you can't use it again,

Jim.

[Alan Kirk]

i fully-licensed version of TM1. If all the data contributors were licensed you wouldn't have to do the CSV workaround to get their data.

I just found out today that we had TM1 full implementation before I arrived, but the resulting consultant fees resulting in it being removed (!)

If consultant fees were an issue, then you're doing it wrong. If you have skilled and talented in house TM1 admin(s) then the only time you might need consultants would be if you were setting up a completely new system, and one which is large enough to exceed the time resources of your in house people, or maybe to do a health check on your system from time to time. It certainly shouldn't be an extensive, ongoing, year on year cost.

The proviso here is of course that you make sure that the people you hire actually have the skills that they claim to have. That one can be problematic to determine until you set them to work. The other proviso is that your company doesn't do something stupid like some of the job ads that I see down here.

"10 years experience in TM1, degree qualified, preferably with a masters, PhD would be nice because selecting on academic transcripts is so much easier than looking for real world skills that match the position, and for this we're offering a package of $AUD 80K. Oh what the heck, we'll make it $82K if we like the look of your face. You'll be working with an exciting new group of people every 6 months because that's the average length of our employee churn, and then for some reason we have to place this ad again, go figure".

Everybody wants something for nothing, but other people have to make a living too... including IBM which is why you won't be getting this software at a bargain basement price, no matter what you do. (Which is not to say that I think the price they charge is proportional, but that's a different discussion. IMHO TM1's price should be more comparable to SQL Server's since both are basically just a development tool and don't do much out of the box. Cognos BI is a different conversation again, admittedly.)

Where I think IBM may be killing the golden goose is in the maintenance fees and the rises that they try to force through every year. Those, I think, are going to cause the product to get thrown out of some places far more than consultants' fees will.

As Jim alluded to, even if you go off maintenance you can go back on to get the newer versions... but I am told by those who know that going off maintenance and then back on is a distinctly painful experience.

[mnbv]

Very interesting thread... I'm a bit lost on pricing and still waiting for an IBM sales rep to call me back...

When I google Cognos Express prices I'm getting prices between $1,500 and $30,0000 - does anyone know the price I should expect? is there a price list or do they negotiate on every sale? - for the time being I'm just talking about installing it on 1 PC and having a couple of users being able to access it.

Thanks,

mnbv

[tomok]

for the time being I'm just talking about installing it on 1 PC and having a couple of users being able to access it.

it's not nearly that simple. Cognos Express is not a stand-alone product like Office. You license it as a system, which means purchasing the server component and then licenses for the clients. So, at a minimum you have to buy the CX Performance Management Server, which has a list price of $8,000, at least one Admin license, which is $2,250, and then a $1,100 license for each user that is not an administrator. I don't know what the minimum # of client licenses you are allowed to purchase and I also can't tell you how much IBM would be willing to come off list price but for your scenario you are looking at somewhere between $10K and $15K.

[mnbv]

Great, thanks Tom