Feeder Issue - Unfed, Multi Cubes - PLZ Help!

[DeeDee]

Hi dear gurus,

I was trying to update GL cube measure 'Transaction' with Forecast cube measure 'Volume'. All the numbers arrive at the GL cube perfect at the N: level, there are 13 rows (Accounts) with numbers. Unfortunately, when I suppress zero, only first 2 rows (Account A and B) are left. When I unsuppress zero, all 13 Accounts come back. All 13 rows are at N base level. 'Check feeders' on Account A and B return rule f(x) on top, expected number in middle, nothing at bottom 'unfed'. 'Check feeders' on remaining Accounts return the rule f(x) on top, the expected number in the middle, and 'unfed' at bottom. Please help!

Here is the detail info:

1. GL cube (Company, CompanyPlant, Subdepartment, AccountGroup, Account, Period, Year, Time, Scenario, mMeasure)
2. Forecast (Forecast Version, UOM, Period, Year, Time, Company, Salesperson, Customer, Part, mMeasure)

3. Forecast cube Part dimension has Account as the Attribute.

4. On the GL cube, here is the Rule, 'Forecast - Business Inputs' is Scenario, 'Transactions' is mMeasure:

['Forecast - Business Inputs','TRANSACTIONS']=N:
DB('Forecasts','Forecast - Business Inputs','All UOM',!Period,!Year,!Time,!Company,'All Salespeople','Total All Customers',!Account,'Volume');

5. On the Forecast cube, here is the feeder, 'Forecast - Business Inputs' is Forecast Version, 'Transactions' is mMeasure:

['Forecast - Business Inputs','Volume']=>
DB('Finance GL Expenses',!Company,'DFT','Finance DFT 00-00','Net Sales Account Group',
attrs('Part',!Part,'GL Sales Account'),!Period,!Year,!Time,'Forecast - Business Inputs','TRANSACTIONS');

6. I did unload the cubes everytime I make changes to the rule.

Hopefully you can tell me that I made a silly mistake, cause I am at my wit's end.

Thank you so much!

[David Usherwood]

It's not really feasible to be exact without the system being available, but as a general principle, if the rule is are taking from a total value (and you are here), the feeder should feed from the same total value. Feeding from a total is shorthand for feeding from all the n level values below it.
Thus:

Code: Select all

['Forecast - Business Inputs','TRANSACTIONS']=N:
DB('Forecasts','Forecast - Business Inputs','All UOM',!Period,!Year,!Time,!Company,'All Salespeople','Total All Customers',!Account,'Volume');

Code: Select all

[
'Forecast - Business Inputs',
'Volume',
'All UOM',
'All Salespeople',
'Total All Customers'
]=>
DB('Finance GL Expenses',!Company,!Account,
!Period,!Year,!Time,'Forecast - Business Inputs','TRANSACTIONS');

The feeder destination may not be spot on as the dimensionality is'nt completely clear - but since (you say) !Account works in the rule then it will work in the feeder.

[DeeDee]

Hi David,

As you suggested, I did not touch the 'GL cube rule' statements, just changed the 'Forecast Cube feeder' to be what you suggested, that is, adding the 'All UOM','All Salespeople','Total All Customers' on the left side, and did not change the right side.

['Forecast - Business Inputs','Volume','All UOM','All Salespeople','Total All Customers']=>

DB('Finance GL Expenses',!Company,'DFT','Finance DFT 00-00','Net Sales Account Group',attrs('Part',!Part,'GL Sales Account'),!Period,!Year,!Time,'Forecast - Business Inputs','TRANSACTIONS');

Unfortunately getting the same results : Row A B stayed, suppress zero I lost the remaining 11 lines. I really need some help in this problem, any suggestion by anyone will be appreciated!!!

Tks.

DeeDee

[Alan Kirk]

I have to admit that I'm not really clear on how the rule, much less the feeder, is working at all. Specifically, with regard to the Part and Account dimensions. For this to return the right result:

Code: Select all

    
['Forecast - Business Inputs','TRANSACTIONS']=N:
  DB('Forecasts','Forecast - Business Inputs','All UOM',!Period,!Year,!Time,!Company,'All Salespeople','Total All Customers',!Account,'Volume');

There would have to be an element in the Part dimension which corresponds with an element in the Account dimension, whether in base name or alias. However the way you're describing the feeder:

Code: Select all

['Forecast - Business Inputs','Volume','All UOM','All Salespeople','Total All Customers']=>
DB('Finance GL Expenses',!Company,'DFT','Finance DFT 00-00','Net Sales Account Group',attrs('Part',!Part,'GL Sales Account'),!Period,!Year,!Time,'Forecast - Business Inputs','TRANSACTIONS');

the account doesn't seem to be an alias of the part at all, but rather a string attribute. (Which would make sense if multiple parts mapped to a single account.)

If that's the case, !Account is only going to work if the part's name (or one of its aliases) just happened to coincide with the name of an Account element.

Which does make me wonder whether (a) the calculation is only "working" for instances where that happens to be the case and (b) consequently the feeders aren't feeding to the locations where the calculation is actually occurring.

My gut instinct tells me that the problem lies somewhere in there.

[DeeDee]

Hi Alan,

Actually the Part dimension has one rollup like this

DIM PART:
GL Part Groups -> level 1
Parts by GL -> level 2
Account xxx -> level 3
PartA
PartB

So the GL Rule Account is able to connect with the Parts, with !Account, as a result, the GL Rule worked, all base N level are ruled with the correct information, 13 rows showed up.

GL Rule:

['Forecast - Business Inputs','TRANSACTIONS']=N:
DB('Forecasts','Forecast - Business Inputs','All UOM',!Period,!Year,!Time,!Company,'All Salespeople','Total All Customers',!Account,'Volume');

Looks like the problem is in the Feeder, I changed the Feeder to the following, and the similar behaviour happen again, that is, 2 rows left everytime when I zero suppress, when I 'Check Feeder' on the 2 rows, I do not get 'Not Fed'. If I turn off zero suppress, all 13 rows comes back. When I 'Check Feeder' on the disappearing 11 rows, I got 'Not Fed'. Can this be the Part dimension being too big?

Forecast Feeder:

['Forecast - Business Inputs','Volume','All UOM','All Salespeople','Total All Customers']=>
DB('Finance GL Expenses',!Company,'DFT','Finance DFT 00-00','Net Sales Account Group',elpar('Part',!Part,3),!Period,!Year,!Time,'Forecast - Business Inputs','TRANSACTIONS');

Quite confusing, any suggestions will be appreciated. Tks.

[Steve Rowe]

If you think you have your feeder pointing in the right direction then the prime candidate for issues would be your elpar in the feeder.

elpar('part'!par,3) gives the immediate parent of part in the 3rd hierarchy that is defined in the dimension, use the formula in excel with the L0 of part and see if you get what you want. You need to take great care with elpar in the rules since the index number of the hierarchy you are referencing can change without warning and can be different for different elements in the same piece of hierarchy. IMO it's not robust enought to use in feeder and you should use an attribute populated with the account element.

Cheers,

[DeeDee]

Hi gurus,

I now realize that I was using the wrong Attributes, and now I have to create a new attribute to hold the Parent Account, can someone give me some suggestion of how to quickly do that, either thru rule or TI? Like what function to use such that as I traverse to the lowest element, I can back track and put the parent value into the Attribute . I created this new attribute call 'GL Parent Account'

Example in the Part Dimension:

'TOTAL PARTS' <- This hierarchy is not what I want

'GL PART GROUPS' <- This is the hierarchy that I want, Level 1
GROUP PART A <- Level 2
Account X <- Level 3
Part 1
Part 2

Account Y
Part 3
Part 4

So the attribute of Part1 and Part 2 should read Account X and
attribute of Part 3 and Part 4 should read Account Y...

Read something like that

['GL Parent Account'] = S:
If (ElIsAnc ('Part','GL Part Groups', !Part) = 1, elpar ('Part',!Part,3 ),'xx');

But the result is not correct, only sometimes correct.

Tks.

[Steve Rowe]

OK so some issues

You are counting your levels in the wrong direction for TM1, the lowest level is level 0 (part) and the next is level 1 (account) from your example.

The second is your use of the elpar function, the numeric on it refers to the notional index of the hierarchy you are searching, not the level of the element you want returned. Elpar always supplies the immediate parent. Say you have the element Dec - 2010 that rolls up to the totals Year 2010 and Dec 2010 YTD. The heirarchy Year 2010 could be hierarchy "1" and Dec 2010 YTD could be hierarchy "2". The problem with this approach for naming hierarchies is that for Nov-2010 the heirarchy Year 2010 could be hierarchy "2" and Dec 2010 YTD could be hierarchy "1". This means that your elpar will not provide consistent results for all elements.

This leads us to the rule below in order to ensure that you have the correct parent, you need to ensure that you have as many Ifs as you have hierarchies in the dimension.

['GL Parent Account'] = S:
If (ElIsAnc ('Part','GL Part Groups', elpar ('Part',!Part,1 )) = 1, elpar ('Part',!Part,1 ),
If (ElIsAnc ('Part','GL Part Groups', elpar ('Part',!Part,2 )) = 1, elpar ('Part',!Part,2 ),
If (ElIsAnc ('Part','GL Part Groups', elpar ('Part',!Part,3 )) = 1, elpar ('Part',!Part,3 ),
If (ElIsAnc ('Part','GL Part Groups', elpar ('Part',!Part,4 )) = 1, elpar ('Part',!Part,4 ),'XX'))));

What you should do if it is practical is to write a TI that copies the result of this rule into a another attribute 'GL Parent Account Text' and reference this in your feeder, this makes the feeder more efficient since it does not have to evaluate the above elpar logic every time the feeder fires.

HTH

[DeeDee]

Dear all,

The problem now became a non-issue cause the user decided to do the rule with a totally different concept. Thanks for all of the help!!! You guys are much better than IBM...

DeeDee

[Alan Kirk]

You guys are much better than IBM...

Talk about damning us with faint praise...

(You're welcome, come back any time. It's especially good to get detailed questions like that.)