Feeder for asymmetric cubes

[rodrigosborges]

hello,

i am having a problem with the feeder of the following rule.

I have a 3 cubes structure:



The cube expense have the value of the expense by cost center and account.
The cube volume have neither cost center or account.

Must allocate the value of the cost center by the ratio of the volume of production in relation to the total.

The question is, how to make a feeder (by volume, in which I have no account and cost center, or by expense), without using consolidators.

[Wim Gielis]

Hello

If I understand you correctly, in the dimension "Metric Production Volume" you have 2 measures: Volume and the allocated Value from the Expenses cube.
Then feed from Volume to the other measure, no? A 0 volume will lead to 0 expense, hence no feeder is wanted for that combination.

[rodrigosborges]

Wim Gielis,

Thanks for your response.

in the Production Volume Cube i have only one measure, the Volume.

I don't have in the model this measure allocated by Cost Center.

[tomok]

How can you allocate expenses to cost center, based on volume, when you don't have the volume by cost center? Something tells me there is a lot more information you're not telling us. It's hard to help when you're not totally forthcoming with all the facts.

[rodrigosborges]

Hello Tomok, thanks for the reply and sorry for the miscommunication .

I will try to contextualize .

I have a cube for input expense. This input is done at the level of account and cost center .

Another cube has my production planning, input of the production volume .

The combination of cost center and account leads to a line of the income statement , which is the third cube.

My cube of income statement have various dimensions of the production planning cube, to distribute the expense by
the volume of production by factories , regions and SKUs.

The distribution is done by Production Volume / Total Volume Production rule.

Occurs that some specific cost centers have different rules for apportionment , which obliges me to have the dimension cost center in the income statement cube.

Thus my rule is being:

['income statement line x '] = N : ( [ 'Volume' ] / DB Total Volume) * DB ( Value Expense ) ;

I have a conditional to check the apportionment criteria of cost center. The criteria can be production Volume of a specific Region or Brand.

The rule is quite complex .

Thanks for the help.

[rmackenzie]

Are you trying to allocate overheads (e.g. IT) to production (e.g. factory)? This sort of rule would imply that there should be a volume, and therefore contribution % of production, attributable to a production cost centre that would apply to an overhead cost centre. So when tomok says:

How can you allocate expenses to cost center, based on volume, when you don't have the volume by cost center?

He means volume by cost centre where that cost centre is the production (e.g. factory) element, and not the expense cost centre (e.g. IT). Without that linking information, it's not clear how you can achieve what you describe.

[tomok]

The rule is quite complex .

If that's the case then how can you expect someone to provide meaningful assistance without all the facts? We can't read your mind. I realize you have probably spent countless days working on the logic behind these rules but no one here has. All we have to go in is what you tell us. This right here:

['income statement line x '] = N : ( [ 'Volume' ] / DB Total Volume) * DB ( Value Expense ) ;

Is not your actual rule, is it? Where's the conditional to check the apportionment of cost center? If the rule is quite complex then it is highly likely the solution to your feeder problem will also be. Without specific information no one can see exactly what it is you are doing. Without knowing exactly what it is you are doing, I can't provide any meaningful assistance.

[rodrigosborges]

Robin , Tomok ,

Using the term that Robin said, I 'm trying to allocate overheads (e.g. IT), using the volume of production as driver. I 'm a portuguese speaker, so I need to learn more business terms in english my fault.

Here is an example of that:

He means volume by cost centre where that cost centre is the production (e.g. factory) element, and not the expense cost centre (e.g. IT).

I thought the term cost center concerned only the dimension cost center.

I'll try to give more details.

My production volume is available for Factory , Region , SKU , etc.
In Volume Production cube, I have the value of the volume produced, not a value of the ratio of the volume % relative to the total. This % value i tried to demonstrate in the previous post with the [ 'Volume' ] / Total Volume DB rule. This is because in this rule I have to deal with some exceptions in relation to overheads , because some groups of cost centers has a different allocation rule, where the ratio is not calculated by the total volume, but a part of him.

Showing the rule I created to illustrate better.

Code: Select all

# Income Statement Lines
# ATL Expenses
[ { '42 ' , '43' } ] = N : (
DB ( 'Production Volume', !Year , !Version, !Month , !Freight , !Channel, !Factory, !Region, !SKU, 'Volume HL' ) \
IF ( DB ( 'map.cost_center ', !cost_center , 'Driver' ) @ = 'SKU',
DB ( 'Production Volume', !Year , !Version, !Month , 'Total Freight', 'Total Channel', 'Total Factory' , 'Total Region' ,
DB ( 'map.cost_center', !cost_center , 'Value Driver' ) ,
 'Volume HL'),
DB ('Production Volume', ! Year , !Version, !Month , 'Total Freight', 'Total Channel', 'Total Factory' , 'Total Region' ,
'SKU by Total Brand',
 'Volume HL' ) )
*
DB ('Cube Expense', !Year , !Version , !Month , !cost_center , !Income Statement Line , 'Value (R$)' ) ;

So , in the above rule I have an expense of ATL ( Expense Cube ), and this expense must be allocated according to the ratio of volume of production, the conditional indicates what kind of driver I use. In the example I test if the driver is SKU, if so I do the just proportion of the total volume of the brand that the sku belongs.

My dimension has 3 levels, Total SKU by Brand, Brand and SKU, the value of my Driver Value in this case would be a Brand.

I think this demonstrates what I'm trying to do, but let me know if you need any other information.

Thanks.

[tomok]

Thanks for the additional info, I can now see what you are doing. You are using a SKU attribute of the cost center to map between the two. This works great in your rule in the Income Statement cube. The problem you are running into is that the driver for whether or not there is any expense in a particular cost center is the volume in a SKU and your lookup only goes one way. Ideally, your feeder would look like this:

Code: Select all

['Total Freight' , 'Total Channel' , 'Total Factory' , 'Total Region', !SKU, 'Volume HL'] =>
DB('Expenses', !Year, !Month, !Version, ATTRS('SKU', !SKU, 'CC'), '42')

If you can add a CC attribute to the SKU dimension then this will work and will be the absolute best and easiest way. Anything other than this is going to be somewhat complicated and need either new cubes created, or, at a minimum, new measures added to hold dummy values for feeding. I would not recommend that when simply adding a single attribute to the SKU dimension will fix it.

[rodrigosborges]

Thanks for the reply Tomok, but what i need is a feeder for the Income Statement Cube, not the Expenses Cube.

The Expenses cube and the Production volume cube are input.

The rule is on the Income Statement Cube.

The problem is that i can't make a feeder for the Income Statement without feeding the 'Total Cost Center'.

Do you think that it is possible to make a feeder to the Income Statement cube without overfeeding

[tomok]

You didn't provide any information about an Income Statement cube. I've already spent too much time trying to dig information out of you. Perhaps someone else can help you now.

[rodrigosborges]

I think that all the necessary information to understand the technical and the business problem is on this thread.

I have a cube for input expense. This input is done at the level of account and cost center .

Another cube has my production planning, input of the production volume .

The combination of cost center and account leads to a line of the income statement , which is the third cube.

In the table on the first post of the thread i have 3 cubes, one called Profit and Loss, which is the same thing.

Anyway, thanks for trying to help.

[tomok]

In the table on the first post of the thread i have 3 cubes, one called Profit and Loss, which is the same thing.

That's the one I gave you the solution for. Just substitute "Profit and Loss Cube" for "Expenses" in the code.