TM1 Forum

I have a GL Budget Adjustments cube feeding a GL Budget Balances cube. I am having trouble getting the feeders correct for the GL Balances cube. I am an accountant and very new to developing with TM1.

The business rules work fine with a small sample of data. I brought in my close balances in the GL for our full budget as a transaction using a TI process. Whenever I try to view the GL Balances cube, it tries to build the view for a long time, and then I get a not responding and have to use task manager to stop the process. I've tried this several times and have even gone back to a prior copy of the server before bringing in the GL accounts dimension and GL Budget opening transaction and get the same results.

Details on my test server follows:

cube: GL Budget Adjustments Revised

dimensions:

Fiscal Period
Budget Adjustment Number
Account Class (Revenue, Expense, or Statistics)
GL Accounts
Transaction Measures (ANN, APR, MAY,JUN...MAR-represents annual and period amounts for the budget adjustments)

business rule:

SKIPCHECK;

FEEDERS;
['ANN' ] => DB('GL Budget Balances Revised', !Fiscal Period, !Account Class, !GL Accounts, 'ANN', 'BUD Adjs');
['APR' ] => DB('GL Budget Balances Revised', !Fiscal Period, !Account Class, !GL Accounts, 'APR', 'BUD Adjs');
['MAY' ] => DB('GL Budget Balances Revised', !Fiscal Period, !Account Class, !GL Accounts, 'MAY', 'BUD Adjs');
['JUN' ] => DB('GL Budget Balances Revised', !Fiscal Period, !Account Class, !GL Accounts, 'JUN', 'BUD Adjs');
['JUL' ] => DB('GL Budget Balances Revised', !Fiscal Period, !Account Class, !GL Accounts, 'JUL', 'BUD Adjs');
['AUG' ] => DB('GL Budget Balances Revised', !Fiscal Period, !Account Class, !GL Accounts, 'AUG', 'BUD Adjs');
['SEP' ] => DB('GL Budget Balances Revised', !Fiscal Period, !Account Class, !GL Accounts, 'SEP', 'BUD Adjs');
['OCT' ] => DB('GL Budget Balances Revised', !Fiscal Period, !Account Class, !GL Accounts, 'OCT', 'BUD Adjs');
['NOV' ] => DB('GL Budget Balances Revised', !Fiscal Period, !Account Class, !GL Accounts, 'NOV', 'BUD Adjs');
['DEC' ] => DB('GL Budget Balances Revised', !Fiscal Period, !Account Class, !GL Accounts, 'DEC', 'BUD Adjs');
['JAN' ] => DB('GL Budget Balances Revised', !Fiscal Period, !Account Class, !GL Accounts, 'JAN', 'BUD Adjs');
['FEB' ] => DB('GL Budget Balances Revised', !Fiscal Period, !Account Class, !GL Accounts, 'FEB', 'BUD Adjs');
['MAR' ] => DB('GL Budget Balances Revised', !Fiscal Period, !Account Class, !GL Accounts, 'MAR', 'BUD Adjs');



cube: GL Budget Balances Revised

dimensions:
Fiscal Period
Account Class
GL Accounts
Period Distributions (ANN, APR, MAY,JUN...MAR)
Balance Measures (Open Bal, BUD Adjs, Close Bal)

business rule:

SKIPCHECK;

['BUD Adjs','ANN' ]=DB('GL Budget Adjustments Revised', !Fiscal Period, 'All Adjustments', !Account Class, !GL Accounts, 'ANN');
['BUD Adjs','APR' ]=DB('GL Budget Adjustments Revised', !Fiscal Period, 'All Adjustments', !Account Class, !GL Accounts, 'APR');
['BUD Adjs','MAY' ]=DB('GL Budget Adjustments Revised', !Fiscal Period, 'All Adjustments', !Account Class, !GL Accounts, 'MAY');
['BUD Adjs','JUN' ]=DB('GL Budget Adjustments Revised', !Fiscal Period, 'All Adjustments', !Account Class, !GL Accounts, 'JUN');
['BUD Adjs','JUL' ]=DB('GL Budget Adjustments Revised', !Fiscal Period, 'All Adjustments', !Account Class, !GL Accounts, 'JUL');
['BUD Adjs','AUG' ]=DB('GL Budget Adjustments Revised', !Fiscal Period, 'All Adjustments', !Account Class, !GL Accounts, 'AUG');
['BUD Adjs','SEP' ]=DB('GL Budget Adjustments Revised', !Fiscal Period, 'All Adjustments', !Account Class, !GL Accounts, 'SEP');
['BUD Adjs','OCT' ]=DB('GL Budget Adjustments Revised', !Fiscal Period, 'All Adjustments', !Account Class, !GL Accounts, 'OCT');
['BUD Adjs','NOV' ]=DB('GL Budget Adjustments Revised', !Fiscal Period, 'All Adjustments', !Account Class, !GL Accounts, 'NOV');
['BUD Adjs','DEC' ]=DB('GL Budget Adjustments Revised', !Fiscal Period, 'All Adjustments', !Account Class, !GL Accounts, 'DEC');
['BUD Adjs','JAN' ]=DB('GL Budget Adjustments Revised', !Fiscal Period, 'All Adjustments', !Account Class, !GL Accounts, 'JAN');
['BUD Adjs','FEB' ]=DB('GL Budget Adjustments Revised', !Fiscal Period, 'All Adjustments', !Account Class, !GL Accounts, 'FEB');
['BUD Adjs','MAR' ]=DB('GL Budget Adjustments Revised', !Fiscal Period, 'All Adjustments', !Account Class, !GL Accounts, 'MAR');
['Open Bal']=DB('GL Budget Balances Revised',DB('zTime Navigation',!Fiscal Period,'Prior Period'),!Account Class,!GL Accounts,!Period Distributions,'Close Bal');
['Close Bal' ] =['Open Bal' ] + ['BUD Adjs' ] ;

FEEDERS;

['Close Bal' ] => ['BUD Adjs' ] ;

['Open Bal'] => DB('GL Budget Balances Revised',DB('zTime Navigation',!Fiscal Period,'Prior Period'),!Account Class,!GL Accounts,!Period Distributions,'Close Bal');

The GL Accounts dimension has about 90,000 records for 3 entities with the following consolidations (CEO, EX, AE, DR, SUP, DPT). So my only transaction is the BUD Adjs representing the closing balance for period Jun-11 which is about 90,000 records (as I only brought into the GL Accounts dimension records that have a balance at Jun-11).

I've done a lot of reading on Skipcheck/Feeders and Check Feeders/Trace Feeders but I am having trouble with them. I appreciate any help with troubleshooting the proper feeders for this GL Budget Balances cube.
Please let me know if any further info is required.

Thanks,

...Tom

After a quick glance, the feeders on the cube GL Budget Balances Revised seem wrong.

Current: The elements making up the calculation (right side of equal sign) should be on the left side of the equal sign of a feeder.

A dynamic feeder for the Open Bal calculation is nearly impossible as you cannot reference an attribute on the left side.
The only method I have found it to hard code but that can be messy as you would have to specify each period.
Example:
Jan 11, Close Bal => Feb 11, Open Bal;

For an addition calculation ( like the 'Close Bal' ), you have 2 options, you can just have 1 of the elements making up the calculation drive the feeder or to have a proper feeder, you would have all elements from the calculation feed the calculated elment.
Example:
Open Bal => Close Bal;
BUD Adjs => Close Bal;

First, you have to make N: in your rules to calculate on leaf:

['BUD Adjs','APR' ]=N:DB('GL Budget Adjustments Revised', !Fiscal Period, 'All Adjustments', !Account Class, !GL Accounts, 'APR');
['BUD Adjs','MAY' ]=N:DB('GL Budget Adjustments Revised', !Fiscal Period, 'All Adjustments', !Account Class, !GL Accounts, 'MAY');
['BUD Adjs','JUN' ]=N:DB('GL Budget Adjustments Revised', !Fiscal Period, 'All Adjustments', !Account Class, !GL Accounts, 'JUN');
['BUD Adjs','JUL' ]=N:DB('GL Budget Adjustments Revised', !Fiscal Period, 'All Adjustments', !Account Class, !GL Accounts, 'JUL');
['BUD Adjs','AUG' ]=N:DB('GL Budget Adjustments Revised', !Fiscal Period, 'All Adjustments', !Account Class, !GL Accounts, 'AUG');
['BUD Adjs','SEP' ]=N:DB('GL Budget Adjustments Revised', !Fiscal Period, 'All Adjustments', !Account Class, !GL Accounts, 'SEP');
['BUD Adjs','OCT' ]=N:DB('GL Budget Adjustments Revised', !Fiscal Period, 'All Adjustments', !Account Class, !GL Accounts, 'OCT');
['BUD Adjs','NOV' ]=N:DB('GL Budget Adjustments Revised', !Fiscal Period, 'All Adjustments', !Account Class, !GL Accounts, 'NOV');
['BUD Adjs','DEC' ]=N:DB('GL Budget Adjustments Revised', !Fiscal Period, 'All Adjustments', !Account Class, !GL Accounts, 'DEC');
['BUD Adjs','JAN' ]=N:DB('GL Budget Adjustments Revised', !Fiscal Period, 'All Adjustments', !Account Class, !GL Accounts, 'JAN');
['BUD Adjs','FEB' ]=N:DB('GL Budget Adjustments Revised', !Fiscal Period, 'All Adjustments', !Account Class, !GL Accounts, 'FEB');
['BUD Adjs','MAR' ]=N:DB('GL Budget Adjustments Revised', !Fiscal Period, 'All Adjustments', !Account Class, !GL Accounts, 'MAR');

['Open Bal']=N:DB('GL Budget Balances Revised',DB('zTime Navigation',!Fiscal Period,'Prior Period'),!Account Class,!GL Accounts,!Period Distributions,'Close Bal');
['Close Bal' ] = N:['Open Bal' ] + ['BUD Adjs' ] ;

this feeder is useless: ['Close Bal' ] => ['BUD Adjs' ] ;

Then in feeders, be very careful if you feed consolidated elements, it will feed all the descendents:
['ANN' ] => DB('GL Budget Balances Revised', !Fiscal Period, !Account Class, !GL Accounts, 'ANN', 'BUD Adjs');
where I suppose 'ANN' is the consolidation of the months.
So you feeder will make

'APR' => 'APR'
'APR' => 'MAY'
'APR' => 'JUN'
(x12 x all others leaf element combinations)
...
'MAY' => 'APR'
'MAY' => 'MAY'
'MAY' => 'JUN'
(x12 x all others leaf element combinations)
etc.

You got to remove this one and the rule with 'ANN' too.
You server must explose your RAM with this code.

I though you can feed just by

['All Adjustments' ] => DB('GL Budget Balances Revised', !Fiscal Period, !Account Class, !GL Accounts, !Transaction Measures, 'BUD Adjs');
or
[] => DB('GL Budget Balances Revised', !Fiscal Period, !Account Class, !GL Accounts, !Transaction Measures, 'BUD Adjs');

instead of the 12 feeders.

rozef/ajain86 thanks for the responses.

To clarify, ANN is not a consolidation of the 12 periods APR-MAR, It is the annual budget amount for the transaction which is the total of the periods, but in this case it is no different than the 12 period amounts.

I've cleared the Bud Adjs transaction of 90,000 records to set up the Budget Open Balances and replaced it with 2000 records to test my rules/feeders. I am trying a number of things, but still cannot figure out how to get it right so the preformance of the Budget Balances cube is reasonable.

I appreciate any further suggestions/advice.

Regards,

...Tom

I don't see why this is taking so long unless you've neglected to mention some other rules? However while your rules are fine, they could certainly be cleaned up a bit. Also, your feeder for the opening balance is wrong (actually this might be slowing things down a bit). I've made a few modifications (highlighted). Even if you report that it doesn't speed it up it should at least make it a bit clearer what's going on for further analysis.

cube: GL Budget Adjustments Revised

SKIPCHECK;
FEEDERS;
[] => DB('GL Budget Balances Revised', !Fiscal Period, !Account Class, !GL Accounts, !Transaction Measures, 'BUD Adjs');

cube: GL Budget Balances Revised

SKIPCHECK;
['BUD Adjs']= N: DB('GL Budget Adjustments Revised', !Fiscal Period, 'All Adjustments', !Account Class, !GL Accounts, !Period Distributions');
['Open Bal']=N: DB('GL Budget Balances Revised',DB('zTime Navigation',!Fiscal Period,'Prior Period'),!Account Class,!GL Accounts,!Period Distributions,'Close Bal');
#DELETE: ['Close Bal' ] =['Open Bal' ] + ['BUD Adjs' ] ; This should be done via a consolidation, not a rule

FEEDERS;
#DELETE ['Close Bal' ] => ['BUD Adjs' ] ; Not required if this is done via consolidation rather than rule
['Close Bal'] => DB('GL Budget Balances Revised',DB('zTime Navigation',!Fiscal Period,'Next Period'),!Account Class,!GL Accounts,!Period Distributions,'Open Bal');

While your DB reference to the zTime Navigation is perfectly fine, the more usual way of doing it is setting up an attribute in the Fiscal Period dimension and referencing that via an attrs reference. Technically it's six of one half a dozen of the other, but it looks a little tidier and means one less cube.

Martin

Thanks for your response Martin. I've made all the changes you suggested and it is really snappy now.

However, my only problem is my fiscal year consolidation of the periods for the Open Bal and Close Bal is a sum of the 12 months, and not what the Open Bal and Close Bal should be for the fiscal year. The BUD adj's consolidation is right.

I believe this is caused by using your suggestion of changing Close Bal to a consolidation of Open Bal and Bud Adjs.

What is a way to fix this so I can show the sum of BUD Adjs for a fiscal year but have it show the correct Open Bal and Close Bal for that fiscal year.

OK I think I have it - I added the following consolidation level rule ['Close Bal' ] =C:['Open Bal' ] + ['BUD Adjs' ] to get the correct Open/Close Bal for the consolidated Fiscal Year totals as in my revised rules below:

cube: GL Budget Balances Revised

SKIPCHECK;
['BUD Adjs']= N: DB('GL Budget Adjustments Revised', !Fiscal Period, 'All Adjustments', !Account Class, !GL Accounts, !Period Distributions');
['Open Bal']=N: DB('GL Budget Balances Revised',DB('zTime Navigation',!Fiscal Period,'Prior Period'),!Account Class,!GL Accounts,!Period Distributions,'Close Bal');
['Close Bal' ] =C:['Open Bal' ] + ['BUD Adjs' ] ;

FEEDERS;
['Close Bal'] => DB('GL Budget Balances Revised',DB('zTime Navigation',!Fiscal Period,'Next Period'),!Account Class,!GL Accounts,!Period Distributions,'Open Bal');

My question to finalize this is "Is this the recommended way to do this so that it won't introduce a performance hit?

Next I will bring back in the 90,000 record transaction for the full Open GL Bdget Balance at JUN 11 and see if the performance remains snappy.

Much thanks,

...Tom

tcasey wrote:My question to finalize this is "Is this the recommended way to do this so that it won't introduce a performance hit?

No, it's not. If 'Close Bal' is a consolidation of 'Open Bal' and 'BUD Adjs', you dont have to edit a rule which make it.
The problem is you must feed 'Close Bal' as it is now a consolidated element.

Add in feeders:
['BUD Adjs' ]=>['Close Bal' ];
['Open Bal']=>['Close Bal' ];

That's should fix it properly.
Regards

Thanks rozef for your response. However, I tried your suggestion and it does not give the correct value for Open/Close Bal, they are still a total of the 12 periods Open/Close Bal.

Both your solution and mine (['Close Bal' ] =C:['Open Bal' ] + ['BUD Adjs' ] ; ) revives my problem of the processor running at 100% and memory error log files (about 10 of 100 mb each!) get created and my system crashes with the larger transaction data (~90,000 records).

Is this a situation where a year consolidation on periods doesn't make sense. Would you just snapshot out your 12 period BUD Adjs and do a sum. Or is there some other design solution that would work and keep the performance snappy?

Thanks,

...Tom

Leave it as a consolidation (therefore will get fed automatically) and use this rule instead

['Open Bal', 'All Periods'] = C: ['Open Bal', 'Jan'];
['Close Bal', 'All Periods'] = C: ['Close Bal', 'Dec'];

No need for any more feeders than in my last post.

Martin

Thanks Martin, that works perfectly. Your knowledge, experience and time is greatly appreciated.

For the benefit of anyone else using this thread, this is the two extra rules I added in the GL Budget Balances Revised cube as my fiscal year-end is March:

['Open Bal', 'Year 2011-12'] = C: ['Open Bal', 'Apr-11'];
['Close Bal', 'Year 2011-12'] = C: ['Close Bal', 'Mar-12'];

I will have to add more similar rules for each other fiscal year consolidations.

With these changes the performance of the cube is just as quick.