Help & TM1 Support/Introduction

redbullshots · Post by **redbullshots** » Fri Sep 04, 2009 5:12 am

Hi guys,

I've started using this program and the manuals supplied, while helpful still don't fully explain all the features of TM1 and thus I'm having trouble bringing it all together.

Any materials online or books that you recommend that I should get?

Alan Kirk · Post by **Alan Kirk** » Fri Sep 04, 2009 5:36 am

redbullshots wrote: I've started using this program and the manuals supplied, while helpful still don't fully explain all the features of TM1 and thus I'm having trouble bringing it all together.

Any materials online or books that you recommend that I should get?

First of all, welcome to the TM1 community.

There aren't any known textbooks on TM1 at this time, nor do any of the usual on-line training sites (Lynda, Total Training etc) have a course on it as far as I know.

IBM conducts some though; how deep are your pockets?

http://www-304.ibm.com/jct03001c/servic ... Code=P6402

In truth, it's unlikely that you need to go to that extreme. As with any application, it's probably not a good idea to try to digest the whole thing at once. In Photoshop, you don't need to know how to do curves before you can crop an image. Similarly with TM1, you don't need to know how to write API code or MDX subsets to browse a cube.

If you have a Perspectives licence, the best advice is to set up a local server (as described in the User Guide), get a copy of the sample databases from the installation package, and... just play. Learn how to create subsets and views, slice data, then move on to creating new dimensions and cubes, then work through the Rules Guide to get a feeling for how to do that. The next step is probably learning TI.

Whichever way you do it, it'll take a while before you get up to speed on the overall package.

redbullshots · Post by **redbullshots** » Fri Sep 04, 2009 6:30 am

Hi Alan,

Thanks for the reply - I've got a server setup at work, and have been able to use the sample data included and have worked through some of the examples.

You're right though, at the moment, all the concepts seem to be in isolation and I'm having difficulty linking concepts together.

At the moment I'm on to rules and the examples are fairly helpful.

Cheers.

redbullshots · Post by **redbullshots** » Mon Sep 07, 2009 5:02 am

Question for Alan and others in the know:

I am continuing some TM1 work left from a colleague where we are automating our P&L in TM1. Essentially I would like to reallocate a bucket of costs to different categories based on a percentage I have allocated in an allocation cube.

Below are two code extracts - the first one works, whilst the second does not. Feeders do not seem to impact it - we have a feeder in there but without it. The only change I have made is to alter L93960 to L94000 (which is the line below in the P&L report).

Anyone have any suggestions as to what may be the problem?

Code that works
#Testing for Overhead allocation
['L93960' ] = N:
IF ( ATTRS('Profit Centre', !Profit Centre, 'Profit Centre Class') @= 'A',
DB('Trading Statement', !Date and Time Stamp, !Month, !Year, !Version, !Currency, 'PB008', 'All Accounts')*
(DB('Overhead Allocation', 'SSA %', !Year, !Month, !Profit Centre)\100),
STET);

Code that doesn't work
#Testing for Overhead allocation
['L94000' ] = N:
IF ( ATTRS('Profit Centre', !Profit Centre, 'Profit Centre Class') @= 'A',
DB('Trading Statement', !Date and Time Stamp, !Month, !Year, !Version, !Currency, 'PB008', 'All Accounts')*
(DB('Overhead Allocation', 'CSA %', !Year, !Month, !Profit Centre)\100),
STET);

Thanks.

Michel Zijlema · Post by **Michel Zijlema** » Mon Sep 07, 2009 5:22 am

redbullshots wrote: Below are two code extracts - the first one works, whilst the second does not. Feeders do not seem to impact it - we have a feeder in there but without it. The only change I have made is to alter L93960 to L94000 (which is the line below in the P&L report).

There is a second change in the rules you didn't mention: the first rule uses SSA% for the allocation, the second uses CSA% - is this as intended?
Do both percentage have a values?
From your question it is not clear to me whether you've actually tested this without Skipcheck&Feeders - does the the calculation work on a N-level cell (view without zero suppression) but does not consolidate? - in this case it is almost certainly a feeder problem.

Michel

redbullshots · Post by **redbullshots** » Mon Sep 07, 2009 5:25 am

Hi Michel,

Thanks for the speedy response. I'm fairly new to TM1 although have used other BI tools in the past so apologies for my lack of knowledge.

The difference between SSA % and other field is intended - apologies I didn't pick this up though when I copied the code into the post.

Skipcheck and Feeders are being used - however the feeders don't seem to make a difference (i.e. the first allocation code works with the feeders having incorrect information). I'll test it without and see how I go. As I'm still familiarising myself with the Rules Editor, should the code I provided also work by using the skipcheck and feeders arguments?

Thanks.

Michel Zijlema · Post by **Michel Zijlema** » Mon Sep 07, 2009 5:38 am

redbullshots wrote:Skipcheck and Feeders are being used - however the feeders don't seem to make a difference (i.e. the first allocation code works with the feeders having incorrect information). I'll test it without and see how I go. As I'm still familiarising myself with the Rules Editor, should the code I provided also work by using the skipcheck and feeders arguments?

To be able to determine whether the feeders will work you should supply the feeder code: this is the code somewhere below the FEEDERS; statement that targets ('=>') the ['L93960'] and ['L94000'] areas.

Michel

redbullshots · Post by **redbullshots** » Mon Sep 07, 2009 5:43 am

Hi Michel,

Thanks again for the reply.

I tried this code outside of skipcheck and feeders (i.e. above it) with no luck.

It's bizarre because the first statement works and then I try the second statement and it will not populate the rows with values. The only thing that has changed is the accounts.

Any other suggestions for what I'm doing wrong - perhaps it's the feeders?

SKIPCHECK;
#Testing for Overhead allocation
['L93960' ] = N:
IF ( ATTRS('Profit Centre', !Profit Centre, 'Profit Centre Class') @= 'A',
DB('Trading Statement', !Date and Time Stamp, !Month, !Year, !Version, !Currency, 'PB008', 'All Accounts')*
(DB('Overhead Allocation', 'SSA %', !Year, !Month, !Profit Centre)\100),
STET);

#Testing for Overhead allocation
['L94150' ] = N:
IF ( ATTRS('Profit Centre', !Profit Centre, 'Profit Centre Class') @= 'A',
DB('Trading Statement', !Date and Time Stamp, !Month, !Year, !Version, !Currency, 'PB008', 'All Accounts')*
(DB('Overhead Allocation', 'SSA %', !Year, !Month, !Profit Centre)\100),
STET);

FEEDERS;
['PB008', 'All Accounts'] =>DB('Trading Statement', !Date and Time Stamp, !Month, !Year, !Version, !Currency, !Profit Centre, 'L93960');
['PB008', 'All Accounts'] =>DB('Trading Statement', !Date and Time Stamp, !Month, !Year, !Version, !Currency, !Profit Centre, 'L94150');

Michel Zijlema · Post by **Michel Zijlema** » Mon Sep 07, 2009 7:18 am

Hi,

Based on the rules supplied both ['L93960'] and ['L94150'] should contain the same values as the rules (feeders) are now identical for both areas (also both using SSA% for allocation now).
Things that could interfere:
Is ['L94150'] a N level element and not a consolidation - in the latter case the rule will never be triggered (your rule is restricted to N level)?
Are there other rules in the rules sheet - if another rule has priority over the ['L94150'] rule while calculating an overlapping area the ['L94150'] rule wil never be triggered.

Michel

Steve Rowe · Post by **Steve Rowe** » Mon Sep 07, 2009 7:26 am

Hi,
One other thing to check is that the values for the second account are in Profit Centre class A, it may be that there is nothing to allocate.

To help debugging, navigate to the N level of the cube (all elements are level 0) and right click on the cell where you think there should be a result and select the rule tracer. This might help you resolve the problem.

Cheers,

redbullshots · Post by **redbullshots** » Tue Sep 08, 2009 1:15 am

Hi guys,

Thanks for your help.

I've worked it out.

Another issue I'm having now is that my data is not rolling up into the consolidated view level. Can anyone provide some tips on what I should do?

Thanks.

Gregor Koch · Post by **Gregor Koch** » Tue Sep 08, 2009 1:35 am

Hi rebullshots

It would be really nice if you would post what actually fixed it. Give and take.

Although your last question is (very) vague it still indicates that you might not have fixed your feeder issue.
My guess, if I understand you right is that either you have a (C:) rule on your consolidation or your feeders don't work.

Cheers

redbullshots · Post by **redbullshots** » Tue Sep 08, 2009 3:36 am

There wasn't really much to fix. The code I had in there worked. I had missed the fact that the rule had worked, however the consolidation didn't - which is addressed below.

When I am viewing through the cube viewer, on individual months (e.g. July) the data appears, however on the consolidated grouping (e.g. YTD July) it doesn't appear - which is what I'm trying to fix at the moment. The peculiar thing is that the consolidation works for the first part of my script but not the second.

I have no C level consolidation in my rules - they are all N level...

I'll play around with it some more - hopefully figure out what is going on and then will give everyone an update...

Have you got any other things I should try?

Alan Kirk · Post by **Alan Kirk** » Tue Sep 08, 2009 4:00 am

redbullshots wrote:There wasn't really much to fix. The code I had in there worked.

When I am viewing through the cube viewer, on individual months (e.g. July) the data appears, however on the consolidated grouping (e.g. YTD July) it doesn't appear - which is what I'm trying to fix at the moment. The peculiar thing is that the consolidation works for the first part of my script but not the second.

I have no C level consolidation in my rules - they are all N level...

I'll play around with it some more - hopefully figure out what is going on and then will give everyone an update...

Have you got any other things I should try?

The symptoms that you describe are the hallmark of a feeder problem. The N levels calculate correctly, but consolidations don't in a zero suppressed view.

Feeders are a bit of a dark art but the general concept is fairly simple. The feeder for a rule should be the one cell that will ALWAYS have a non-zero value whenever the rule itself is supposed to return a value.

When you don't see the consolidations, you know that the calculation isn't being fed properly. It's slightly misleading since the problem isn't really the consolidation (feeders work at N level); that's just the symptom.

Gregor Koch · Post by **Gregor Koch** » Tue Sep 08, 2009 4:17 am

Hi

If you don't have 'C: - Rules' then either your Feeders don't work or the weighting of the elements in that consolidation is zero. The former being more likely unless you changed the weighting.

In the cubeviewer right click on a cell with YTD July and choose 'Check Feeders...'.
If the rule tracer comes back 'Not fed' you know what is wrong.

Cheers

redbullshots · Post by **redbullshots** » Wed Sep 09, 2009 5:46 am

Hi,

I resolved the feeder issue and got the consolidation going by hard coding the profit centre, switching from !profit centre to a hard coded value. Is this best practice?

Also, In the overhead allocation, I have two cubes (Overhead Allocation & Overhead Adjustment), one to allocate overhead based on a % allocation, and another cube for a manual over-ride when we decide to tweak the accounts. When they consolidate they will net off to give the desired result.

Trading Statement Cube Rules:
['N93905' ] = N:
IF ( ATTRS('Profit Centre', !Profit Centre, 'Profit Centre Class') @= 'A',
DB('Trading Statement', !Date and Time Stamp, !Month, !Year, !Version, !Currency, 'PB009', 'All Accounts')*
(DB('Overhead Allocation', 'SSA %', !Year, !Month, !Profit Centre)\100),
STET);

#Overhead adjustment
['N93906' ] = N:DB('Overhead Adjustment', 'SSA $', !Year, !Month, !Profit Centre);

['PB009'] =>DB('Trading Statement', !Date and Time Stamp, !Month, !Year, !Version, !Currency, 'ISOA01', 'N93905');

In the overhead adjustment cube I have the following rule (feeding to Trading Statement cube):

FEEDERS;
['SSA $' ] => DB('Trading Statement', !Month, !Year, 'ISOA01', 'N93906');

In this example, I am again having a feeder issue where the consolidation is not rolling up for the value that I have referred from the Overhead Adjustment cube. The rules guide suggested feeding from the Overhead Adjustments cube however I can't help but think that I need to feed in the Trading Statement cube but am unsure how to do this.

Any suggestions?

Steve Rowe · Post by **Steve Rowe** » Wed Sep 09, 2009 7:16 am

The DB to the trading statement cube in the last feeder looks to be missing several dimension references and so will be failing (silently)...

DB('Trading Statement', !Month, !Year, 'ISOA01', 'N93906');
DB('Trading Statement', !Date and Time Stamp, !Month, !Year, !Version, !Currency, 'PB009', 'All Accounts')
You need to give a reference for every dimension in a DB statement for it to work
HTH

redbullshots · Post by **redbullshots** » Wed Sep 09, 2009 7:54 am

Unfortunately it's not working.

I am referencing a value in the Allocation Adjustment cube and pulling that into the Trading Statement cube. So, the value displays but does not consolidate.

Should the dimensions in the allocation adjustment cube be the same as the trading statement cube (e.g. year and month are the same - but the allocation adjustment cube omits other information such as !version, !currency etc.).

Thanks - I'm still a little confused on rules but I'll get there!

redbullshots · Post by **redbullshots** » Thu Sep 10, 2009 12:21 am

I got around that issue by killing off the idea of the second cube. I made a view of our P&L cube, sliced into Excel, and then hard coded the value and pressed Shift F9 to upload the value back into the cube so that it consolidates...

Kudos goes to a colleague of mine which suggested this approach.

TM1 Forum

Help & TM1 Support/Introduction

Help & TM1 Support/Introduction

Re: Help & TM1 Support/Introduction

Re: Help & TM1 Support/Introduction

Re: Help & TM1 Support/Introduction - Overhead Allocation

Re: Help & TM1 Support/Introduction - Overhead Allocation

Re: Help & TM1 Support/Introduction

Re: Help & TM1 Support/Introduction

Re: Help & TM1 Support/Introduction

Re: Help & TM1 Support/Introduction

Re: Help & TM1 Support/Introduction

Re: Help & TM1 Support/Introduction

Re: Help & TM1 Support/Introduction

Re: Help & TM1 Support/Introduction

Re: Help & TM1 Support/Introduction

Re: Help & TM1 Support/Introduction

Re: Help & TM1 Support/Introduction

Re: Help & TM1 Support/Introduction

Re: Help & TM1 Support/Introduction

Re: Help & TM1 Support/Introduction