Need help with feeder stmt...

[tcasey]

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

The business rules work fine when viewing a small sample of data. When I try a consolidated view of a much larger data set in the GL Actual Balances cube, it tries to build the view for about 5 minutes or so, and then I get the cube viewer/building view windows go white, and I have to kill the "not responding" message in task manager. I've tried this several times and have even gone back to a prior copy of the server and get the same results. It creates numerous 100 mb log files in the Logfiles directory named tm1server.log.1 to tm1server.log.20 or so with these contents:

3884 WARN 2012-01-04 13:54:56.812 TM1.Server.Memory al_OpenPool() outOfMemory Exception <<< MEMORY_SANDBOX_POOL_EXCEEDED >>> - threadID "3884" - apifunc# "327" - pool# "0" - poolsize "0.000000"

I am running this as a local server on a windows xp 32 bit desktop with 4 gb ram with details below:

Cube Dimensions:

1) GL Actual Transactions

Fiscal Period (e.g. Oct-11) (2 of 44 elements with data in OCT & NOV 11 - 16 kb memory used))
Journal (e.g. Payroll) (72 elements - 16 kb memory used)
Batch Date (e.g. 31/10/11) (420 elements - 68 kb memory used)
Batch Number (e.g. 10) (723 elements - 100 kb memory used)
Batch Transaction Number (e.g. 1-14) (8,891 elements - 964 kb memory used)
GL Report Area (e.g. DPT 17.71.1151000) (5,662 elements - 1,348 kb memory used)
GL Report Category (e.g. 171.71.1151000.3111011) (144,549 elements - 35,780 kb memory used)
Batch Transaction Measures (Batch Transaction Description, Net Amount {consolidation of Debit Amount and Credit Amount})

2) GL Actual Balances

Fiscal Period (e.g. Oct-11)
GL Report Area (e.g. DPT 17.71.1151000)
GL Report Category (e.g. 171.71.1151000.3111011)
Actual Balance Measures (Close Bal {consolidation of Net Amount and Open Bal})

Cube Rules:

1)#GL Actual Transactions Cube Rules:
SKIPCHECK;

FEEDERS;

['Net Amount'] => DB('GL Actual Balances', !Fiscal Period, !GL Report Area, !GL Report Category, 'Net Amount');



2) #GL Actual Balances Cube Rules:

SKIPCHECK;
['Net Amount' ] = N: DB('GL Actual Transactions', !Fiscal Period, 'ALL JOURNALS', 'ALL BATCH DATES', 'ALL BATCH NUMBERS', 'ALL BATCH TRANSACTION NUMBERS', !GL Report Area, !GL Report Category, 'Net Amount');
['Open Bal', 'Year 2011-12'] = C: ['Open Bal', 'Apr-11'];
['Open Bal' ] = N: DB('GL Actual Balances', ATTRS('Fiscal Period',!Fiscal Period,'Prior Period'), !GL Report Area, !GL Report Category, 'Close Bal');
['Close Bal', 'Year 2011-12'] = C: ['Close Bal', 'Mar-12'];

FEEDERS;
['Close Bal' ] => DB('GL Actual Balances', ATTRS('Fiscal Period',!Fiscal Period,'Next Period'), !GL Report Area, !GL Report Category, 'Open Bal');


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 Actual Balances cube. I'm struggling with whether this is a resource issue or a feeders issue. I've done something similiar with GL Budget Transactions and GL Budget Balances cubes which work fine on this same server which leads me to believe it is a feeders issue. (previous post on GL Budget cubes http://www.tm1forum.com/viewtopic.php?f=3&t=6022)

Please let me know if any further info is required.

Thanks,

...Tom

[David Usherwood]

<Deep breath, where to start....>
Your GL Transactions cube has four more dimensions than the GL Actual Balances cube (as it should given it has all the journal detail).
Your _rule_ pulls in the totals of those four dimensions, but your _feeder_ doesn't refer to them at all. I _think_ that this will feed from every cell in the Transactions cube to every cell in the Balances cube - but I'm not brave enough to try .
If I'm right, then I'm not surprised about the memory explosion and consequent error messages.
The correct feeder would be

Code: Select all

['Net Amount'
 'ALL JOURNALS', 'ALL BATCH DATES', 'ALL BATCH NUMBERS', 'ALL BATCH TRANSACTION NUMBERS'
] => DB('GL Actual Balances', !Fiscal Period, !GL Report Area, !GL Report Category, 'Net Amount');

I would suggest, if you are able, you should consider some other design approaches:

a Load both the transactions and the balances from your GL, using the appropriate GROUP BYs;
b Placing your transactions in a relational source (or leaving them in the GL) and accessing them through a drillthrough. TM1 (and other OLAP engines) don't play that well with transactions.

HTH

[jydell]

I agree with David,

I recomend you consider building your GL Actual Balances cube from scratch rather than make it Feeder / rule based. Use a similar method you use to populate the GL Actual Transaction cube (I assume this is populated by TI) Alternatively ASCII export a predefined view of the 'GL transcations cube' at the appropriate level and import it via TI to the 'GL Balances cube'. (these can be scheduled to run just after your GL Transactions cube is populated so no manual involvement required and generally no time lag)

Using the above method I estimate the slice that previously took you 5 minutes before blowing up will finish in under 1 second. I have found uploading data where practical is significantly less taxing on the system than rules / feeders.

Regards

[tcasey]

Thanks David/jydel for your assistance. I tried David's revised feeder rule resulting in only a slight improvement in performance. Instead of crashing after 5 minutes or so, it completes ok after 5 minutes or so. Again this is a filtered view at one of our EX levels which is a consolidated view of about 1/10th of all records.

I could use some more advice once I give more background info. First of all it is disappointing that a couple times now it has been said that TM1 should not be used for transaction processing, as we were assured that it could be used for that purpose when we bought it.

We are a health care corporation in Canada that has the Meditech system as our clinical/financial software package. The Meditech system is great for processing actuals, but the Budgeting system is abysmal (not transaction based, not self-balancing and no tools to process budget adjustments, they have to be calculated manually), so we need another system for budgeting, but need to marry budgets with actual on a real-time basis, not just after month-end.

I have put a lot of programming hours into building a front-end using VBA and Excel to process budget adjustments that posts budget transactions to a GL Budget Adjustments and Payroll Budget Adjustments cube in TM1. They then feed into Payroll Budget Balances and GL Budget Balances cubes. This so far is working well. I am doing this with one of 6 Budget Analysts on a test basis for 3 months now - and bring the month-end budget close balances back into Meditech using a scripting program.

What we need is to be able to process budget adjustments during a month, but also review variances and do budget adjustments based on those variances and in some cases make budgets match monthly actuals. Actual changes have to be brought in daily, and in the last day or two before month-end close, several times during the day. So we need real-time balances.

I could change my approach and forget transactions (and dimensions like Journal, Batch date, Batch number, Transaction number) and just bring in revised close balances for the GL accounts hit in the transaction batches into a GL Balances cube using a TI process. So it will just keep overwriting the close bal for GL accounts in each transaction batch during the month, so at any point in time the TM1 GL Actual Balances cube close bal will equal the close bal in the Meditech system.

Then I want to feed the GL Budget Balances cube and the GL Actual Balances cube into a GL Variances cube, where budget analysts can review variances and make budget adjustments as required. This is an interim measure as we want to use the Cognos Report Writer to report off TM1 cubes, but that is not possible yet until we get Cognos 8 (32 bit) to talk to TM1 (64 bit). The holdup is the authentication process for managers using Cognos 8 and dashboards.

So does this seem a reasonable revised approach? Should feeding Actual Bal cube and Budget Bal cube into a Variance cube, result in a system that gives responsive views for the Budget Analysts?

I appreciate any advice on this approach or a suggested better approach.

Regards,

...Tom

[Martin Ryan]

TM1 should not be used for transaction processing, as we were assured that it could be used for that purpose when we bought it

At the risk of sounding weasely, those two statements aren't actually contradictory. TM1 shouldn't be used for transactions, but it can be. If the salesperson said it ticks the box then they weren't lying, just avoiding the full story. If they said it was good at it, then they were wrong.

I think David gave you a good answer to your question, whereas jydel gave you a solution to your problem. Given that you are already importing the data into the transactional cube it will be fairly trivial to create another TI process that loads the balances cube at the same time.

I'd suggest you give that a crack. It shouldn't really take too much time either. Kill the rule and feeder (after backing up of course) and create a new TI process to copy the values across once the transactional cube has been loaded. Or, if you're already loading to the transactional cube via TI process, just add an extra couple of lines to this TI process to update the balances cube at the same time.

[Martin Ryan]

So does this seem a reasonable revised approach? Should feeding Actual Bal cube and Budget Bal cube into a Variance cube, result in a system that gives responsive views for the Budget Analysts?

Oops, missed the meat of your post. For mine, I'd hold onto the transactional cube, because it may well be useful and it's easier to drill from one TM1 cube to another than back to source. But then (as stated above) stick with a balances cube that is populated via TI, not rules.

As to the actual/budget/variance question, I would definitely be trying to get all of that into one cube (the balances cube). Otherwise the reporting is far more complicated and more rules are required.

[tcasey]

Martin...

Thanks for weighing in. Your sound advice in the past and now are greatly appreciated and has been extremeky helpful.

I could use some elaboration on drilling through to transaction cube or using tranactions ti to update balances cube. I am not familiar with drilling thru and don't know how processing transaction ti can update a balances cube as transaction is a net amount for a batch and how that can update a close bal in a bal cube? Please excuse my ignorance as I am a new and sole developer, and just an accountant that can program in vba and given the impression I was capable to implement this TM1 application, and try to give me enough info to take it and run with it, but I need some things spelled out to get it.

Best Regards,

...Tom

[Martin Ryan]

I've only actually done drill through a couple of times myself as I've never needed it. I just stumbled my way through the documentation. Take a look through that and if you have specific questions I'm sure others with more drilling experience can help out.

To get a transaction source to update a balances cube is a two step process. First clear out the cube in the prolog, then load each transaction on top of its predecessors.

Code: Select all

### PROLOG ###
### EMPTY THE CUBE ###
# Define the cube that we're working with
sCube='BalancesCube';
# come up with a generic name to call the views and subsets.  I like to make them unique to the
# process I'm working with, so if things go pear shaped it's easy to work out the culprit
sObjName='sys_' | getProcessName();
# If there is already a view with this name for this cube then remove it
if(viewexists(sCube, sObjName)=1); viewdestroy(sCube, sObjName); endif;
# create a new view for the cube with the name defined earlier.  By default a
# newly created view contains the entire cube in it, so further down the code
# we have to narrow down what we're working with
viewcreate(sCube, sObjName);

# Create the sDim and sElem variables and populate them appropriately
sDim='Month';
# Use month supplied as a parameter, or look it up from a parameters cube
sElem=pMonth;
# Reusable code that uses the dimension and elements defined above.  Makes for easy copy and pasting
# If the subset doesn't already exist (it shouldn't) then create it
if(subsetexists(sDim, sObjName)<>1); subsetcreate(sDim, sObjName); endif;
# Just in case the subset did already exist, empty it out
subsetdeleteallelements(sDim, sObjName);
# insert the element defined earlier
subsetelementinsert(sDim, sObjName, sElem, 1);
# assign the subset to the view created earlier, this narrows things down
ViewSubsetAssign(sCube, sObjName, sDim, sObjName);
# end reusable

# Comment same as section above
sDim='FinYear';
# Use year supplied as a parameter, or look it up from a parameters cube
sElem=pFinYear;
# Begin reusable
if(subsetexists(sDim, sObjName)<>1); subsetcreate(sDim, sObjName); endif;
subsetdeleteallelements(sDim, sObjName);
subsetelementinsert(sDim, sObjName, sElem, 1);
ViewSubsetAssign(sCube, sObjName, sDim, sObjName);
# end reusable

##View is now created and narrowed down to one specific month and year

# Clear out the view, i.e. make everything in it zero
ViewZeroOut(sCube, sObjName);
# Delete the view we created as it was a one time view and we don't want it hanging around
ViewDestroy(sCube, sObjName);
# Delete the subsets as they were one time use and don't need to hang around
SubsetDestroy('FinYear', sObjName);
SubsetDestroy('Month', sObjName);
### CUBE EMPTIED ###

Code: Select all

### DATA ###
# Retrieve the existing value in the cube
nOldVal=cellgetn(sCube, vYear, vMonth, vAccount);
# insert the existing value + the transaction value from the source system/file/cube.  This will increment the balance
cellputn(value+nOldVal, sCube, vYear, vMonth);

Doesn't matter if the transaction source is a database or another TM1 cube, the logic is the same.

just an accountant that can program in vba

I think a fair few TM1 admins started out that way, it's a good grounding.

[rmackenzie]

First of all it is disappointing that a couple times now it has been said that TM1 should not be used for transaction processing, as we were assured that it could be used for that purpose when we bought it.

At the risk of sounding weasely, those two statements aren't actually contradictory. TM1 shouldn't be used for transactions, but it can be. If the salesperson said it ticks the box then they weren't lying, just avoiding the full story. If they said it was good at it, then they were wrong.

Totally agree with Martin's point on that one. Having said that, I've successfully done a system with transactional granularity in TM1 simply because users could (hypothetically) access journal information where drill-through to the source system was unavailable (flatfiles out of SAP R/3->BW). However, if you look at TM1 as a general-purpose analytics engine then modelling the transactions often has little or no value for users especially outside of GL-based systems.

I am curious about why you want the budget held transactionally. Let's say for some account, that if the initial budget was $100, but then became $120, then $110 and then $115, do you want to know the history of the +$20, -$10, +$5 movements on that position? Or, is it OK to know that the budget of $120, right now at this point of the reporting period, is comparable to an actual of, for example, $130? When you say:

I could change my approach and forget transactions (and dimensions like Journal, Batch date, Batch number, Transaction number) and just bring in revised close balances for the GL accounts hit in the transaction batches into a GL Balances cube using a TI process. So it will just keep overwriting the close bal for GL accounts in each transaction batch during the month, so at any point in time the TM1 GL Actual Balances cube close bal will equal the close bal in the Meditech system.

Perhaps you could keep the transactional detail of the actuals in TM1 and use it for a good purpose but you don't have to do budget by transaction just because that's how the actuals are stored. You are already bringing the balances into the summarised actuals cube (yes, try the TI-based solution) - why not store the budget at that level of detail?

Then I want to feed the GL Budget Balances cube and the GL Actual Balances cube into a GL Variances cube

Typically, people include a Version or Scenario dimension in a 'balances' cube and then have a negatively weighted consolidation to automatically calculate the variance. Check the sample databases that come with TM1 for an example - or I think this is covered in the documentation as well. You don't need three cubes (one for actuals, one for budget, one for variance) - you just need to include the Version dimension in a single 'balances' cube.

HTH
Robin

[tcasey]

Robin thanks for your reply...

Just to clarify, the reason we went with TM1 was to overcome weaknesses in the Meditech Budgeting system - being no history of changes, not-self balancing, no way to automate adjustments - so all manual calculations and overwriting of previous balances. Budget Analysts make numerous budget changes per month and the last few days is when most changes occur. We do a backup each night of the budget and run a report against it to see if we are still in balance. When we are not, it can take a few hours to find a problem and fix it.

We are 100% government funded and have to react to about 50 to 100 budget adjustments during a fiscal year involving many million dollars. We not only have to explain why the budget for certain category/location has changed during the current year, but over the past number of years as well, so history of changes is important. To change a position from one dept to another during the year, or add positions, can involve changing hundreds of fields manually - Payroll Budget Hours and Dollars by period, GL Budget Hours and Dollars by period, and then document before and after changes as backup. So you can see the importance of needing a system that has history of changes, self-balancing, with a front-end that can calculate changes like adding or transferring positions automatically. With my excel template an Analyst can now do position changes in a manner of minutes instead of a half day of work.

The problem is we have to be able to run reports(or a view) of Actual/Budget/Variance in real-time and make budget changes or initiate actual journal entries upon variance review. Once we seperate systems for Actuals (Meditech) and Budgets (TM1), then we need a way to marry the two on an ongoing real-time basis. I've tried bring in closing balances of budget changes into Meditech, but that has it's complications. Bringing Actual Batches into TM1 would be much easier. Then use TM1 for reporting/viewing variances for Budget Analysts as they process budget changes each month.

So that brings me to where I am today. There has been some great help and advice given that I have to wrap my head around and test some things, however I admit I am unsure of some of the detail of how to implement what was suggested.

I use the Meditech Report Writer to output a csv file of actual transaction batches and bring that into the TM1 GL Actual Transactions cube using a ti process. That works very well. I believe I have two choices here -

1) Instead of bringing in the transaction amount into a transactions cube, bring the net amount mtd for the period for the GL accounts in the transaction batch into a balances cube - so during the month as the same account may have numerous transactions, this will overwite the account's Net Amount with the revised Net Amount for the month after the transaction.

2) Keep the transactions cube and using ti update a balances cube with the new Net Amount that results from applying the transaction amount to the current Net Amount for the account, triggered when the ti runs for the transaction cube. {This is the one I'm not sure how to do. From Martin's code in his post above, I don't understand how it changes the existing value to a new value for the month based on the effect of the current transaction. The Data tab appears to add the transaction value to the old value after it get's the old value - but I'm confused as the Prolog appears to empty the cube so will there be any values available then to get? I also don't understand the Prolog code and what it is doing. If somebody could provide more detail explanation of how Martin's code works that would be great as I don't understand it and don't know how to code something similar for myself.

Once I figure out how to do option 2, which I believe is the preferred option, I also have to create a scenario/version dimension with Variance as a consolidation of Actual and Budget and give Actual a weight of -1. So this will leave me with one balances cube (probably will call it GL Variance Cube) that gives me everything.

So again if somebody could explain in detail how Martin's ti code works, that would help me with what I feel is the toughest part to figure out. Gone to delve deeper into ti coding....

Regards,

...Tom

[tomok]

No disrepect intended, I applaud your willingness to jump right into things, but you are talking about some very complex issues here that someone new to TM1 developing should probably seek some help on and I don't mean forum help. Get yourself an experienced developer to look over your shoulder, at least for this first design phase, to get you pointed in the right direction. It will be well worth the $. After you both feel comfortable the design is good, you can code things to your hearts content.

I'm guessing an experienced TM1 developer might need in the neighborhood of two to three months to build something like this so I hope you've got six months of free time because that's how long it would probably take a newbie to do it.

[tcasey]

Thanks tomak - that's my sentiment exactly, and I just recently was informed our Director managed to secure some consulting funding! Hoping to build off the good advice here.

I very much appreciate the helpfulness of this forum and would like to thank all who contribute here. I look forward to the day I become knowledgeable enough to help out.

Regards,

...Tom

[Martin Ryan]

So again if somebody could explain in detail how Martin's ti code works, that would help me with what I feel is the toughest part to figure out. Gone to delve deeper into ti coding...

I've edited my code and stuck in a whole bunch of comments that will hopefully explain what it's doing.

You could also take a look at bedrocktm1.org.. There's a lot of sample TI code that will help you get started.

[tcasey]

Thanks Martin... your code comments and link to bedrocktm1 is very helpful... Tom