Fedder between Cubes

[Willi]

Hi,

this is a Problem I have in TM1 9.5.2

I have 2 Cubes with different but similiar dimensions Product and Product_MPL. The Dimension "Product" has alle Products of a Company and Product MPL only Consolidated Elements of it. For example:

Product:
Element 1
Element 1.1
Element 1.2
Element 1.3
Element 1.4
Element 2
Element 2.1
Element 2.2

Product_MPL:
Element 1
Element 2

Now I want to set "Element 1" and "Elment " in the Cube with Product_MPL to the value of the corresponding consolidated Element of Product. But how to write the Feeder? I currently only have the idea to write a Feeder for every Element in Product_MPL. But I'm pretty sure this ist not the best solution!?

Thx and regards

[declanr]

Code: Select all

['Value']=>DB('Cube2', !Dim1, !Dim2, ElPar ( 'Product',!Product, 1), 'Value');

Normally I wouldn't use elpar in a feeder due to the possibility that your products have more than 1 parent and I would instead use an attribute. Either way though the concept of feeding to a higher level granularity dimension is fairly straight forward.

[Alan Kirk]

Code: Select all

['Value']=>DB('Cube2', !Dim1, !Dim2, ElPar ( 'Product',!Product, 1), 'Value');

Normally I wouldn't use elpar in a feeder due to the possibility that your products have more than 1 parent and I would instead use an attribute. Either way though the concept of feeding to a higher level granularity dimension is fairly straight forward.

Am I misreading? I got the impression that Willi was reading the consolidated value from the more detailed cube into the summarised cube. In such a case all that would be needed is the bang operator on the product dimension (assuming that the names were identical).

This theoretically feeds from the detailed consolidation to the summarised N elements. (Though in practice of course it's effectively be a feeder from all of the N level elements from each consolidation in the detail cube to the N elements of the summary cube.)

[declanr]

Code: Select all

['Value']=>DB('Cube2', !Dim1, !Dim2, ElPar ( 'Product',!Product, 1), 'Value');

Normally I wouldn't use elpar in a feeder due to the possibility that your products have more than 1 parent and I would instead use an attribute. Either way though the concept of feeding to a higher level granularity dimension is fairly straight forward.

Am I misreading? I got the impression that Willi was reading the consolidated value from the more detailed cube into the summarised cube. In such a case all that would be needed is the bang operator on the product dimension (assuming that the names were identical).

This theoretically feeds from the detailed consolidation to the summarised N elements. (Though in practice of course it's effectively be a feeder from all of the N level elements from each consolidation in the detail cube to the N elements of the summary cube.)

My assumption was that a rule exists in the less detailed cube:

Code: Select all

['Value']=N: DB ( 'Cube Detailed', !Dim1, !Dim2, !Product_MPL, 'value' );

In which case the feeder (in more detailed cube):

Code: Select all

['Value']=>DB('Cube2', !Dim1, !Dim2,!Product, 'Value');

Wouldn't work since it just feeds from the n-level detailed elements that don't exist in 'Cube2' (less detailed) hence needing to do something to deduce the consolidation of the N-level element (which is itself the n-level element in Cube2).

[Alan Kirk]

My assumption was that a rule exists in the less detailed cube:

Code: Select all

['Value']=N: DB ( 'Cube Detailed', !Dim1, !Dim2, !Product_MPL, 'value' );

In which case the feeder (in more detailed cube):

Code: Select all

['Value']=>DB('Cube2', !Dim1, !Dim2,!Product, 'Value');

Wouldn't work since it just feeds from the n-level detailed elements that don't exist in 'Cube1' (less detailed) hence needing to do something to deduce the consolidation of the N-level element (which is itself the n-level element in Cube2).

Yes, you're right; your method is effectively feeding from the consols in the more detailed cube. Sorry, it's been a long week.

[declanr]

Did have to edit though since I wrote cube 1 where I meant cube 2.... 50/50 chance so I'm bound to get it wrong, long week here as well...

[Willi]

Hi Folks,

thx for the answers. But unfortunately the Problem is that in the detailed cube the Elements in questions has more than one Level of childs. So in my understanding it's not enough to just use ELPAR!?

[Willi]

I get a more detailed description of the 2 dimensions:

Code: Select all

Product:
All_Elements
 Element 1
  Element 1.1
   Element 1.1.1
   Element 1.1.2
   Element 1.1.3
  Element 1.2
   Element 1.2.1
   Element 1.2.2
    Element 1.2.2.1
    Element 1.2.2.2
   Element 1.2.3
  Element 1.3
  Element 1.4
 Element 2
  Element 2.1
   Element 2.1.1
   Element 2.1.2
  Element 2.2

Product_MPL:
Sum
 Element 1
 Element 2

The elements I want to feed have the same names in the 2 dimensions.

[tomok]

But unfortunately the Problem is that in the detailed cube the Elements in questions has more than one Level of childs. So in my understanding it's not enough to just use ELPAR!?

Doesn't matter. The answer declanr gave you is still the correct solution.

[Willi]

Hm, I'm sorry, but it doesn't work

[declanr]

See that bit in my original reply where I said I'd use attributes... put an attribute against your product dimension, for the n-level element populate it with the corresponding element name in the summary dim (this can be done in the TI that builds the dimensions).

Once you have that you just stick ATTRS in your feeder and its all sorted.

[Willi]

Thx, I think 'll give that a try. But Do I have to put this on all N-Elements or only on the Elements I have in the Destination-Dimension?

[declanr]

Thx, I think 'll give that a try. But Do I have to put this on all N-Elements or only on the Elements I have in the Destination-Dimension?

It would be the equivalent of a lookup in excel, so you only need to have attributes on the N-level elements that would have a corresponding element in the other dimension, if you are doing it by your TI that builds the dims though it would be just as easy to do it to all of them.


If you are unable to implement it within your initial dimension building TI processes the following code should create attributes in the detailed dimension for those that have ancestors which also exist in the summarised dimension:

Code: Select all

sDimDetail = 'prod_detail';
sDimSummary = 'prod_summary';
sSubset = 'temp_subset';
sAttribute = 'feeder_product';
AttrInsert ( sDimDetail, '', sAttribute, 's' );

iCount = 1;
iMax = DimSiz ( sDimSummary );
While ( iCount <= iMax );
	sElement = DimNm ( sDimSummary, iCount );
	If ( DType ( sDimSummary, sElement ) @= 'N' );
		sMDX = '{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {([' | sDimDetail | '].[' | sElement | '])}, ALL, RECURSIVE )}, 0)}';
		SubsetCreateByMDX ( sSubset, sMDX, sDimDetail );
		nLast = SubsetGetSize ( sDimDetail, sSubset );
		sLast = SubsetGetElementName ( sDimDetail, sSubset, nLast );
		SubsetElementDelete ( sDimDetail, sSubset, nLast );
		SubsetElementInsert ( sDimDetail, sSubset, sLast, nLast );
		iSubCount = 1;
		iSubMax = nLast;
		While ( iSubCount <= iSubMax );
			sSubElement = SubsetGetElementName ( sDimDetail, sSubset, iSubCount );
			AttrPutS ( sElement, sDimDetail, sSubElement, sAttribute );
			iSubCount = iSubCount + 1;
		End;
		SubsetDestroy ( sDimDetail, sSubset );
	EndIf;
	iCount = iCount + 1;
End;

Note that you obviously have to rename the variables and more importantly I have just written this code in the forum without testing it and you should always be careful implementing any untested TI that contains a loop.... otherwise it leads to that embarrassing moment when you realise that you have locked the server

[Willi]

Thx, I know These situations very well

But my question regarding the N-Elements was not specific enough because I did not mentioned that the elements of the detailed Dimension are C-Elements and the corresponding elements in the other dimensions are N_Elements.

[declanr]

But my question regarding the N-Elements was not specific enough because I did not mentioned that the elements of the detailed Dimension are C-Elements and the corresponding elements in the other dimensions are N_Elements.

That's the whole reason you have to do this, a feeder goes FROM an n-level element, even if you specify the feeder to be from a c-level element it actually feeds from ALL of the n-level element below it. So you need to put the attribute against the n-level element NOT against the c-level. The attribute will say which C-level element above the n-level element also exists in the summarised dimension.

[Willi]

I currently have the Feeders defined manually like this:

Code: Select all

['Element 1','Value']=>DB('Cube2', !Dim1, !Dim2, 'Element 1', 'Value');
['Element 2','Value']=>DB('Cube2', !Dim1, !Dim2, 'Element 2', 'Value');
...

Where the Elements are C-Elements in Cube 1 (Source of Feeding). And this works. But I don't want to Change the Rule every time a new C-Element is defined. So I thought the Attribute at the C-Elements would be enough!?

[declanr]

I currently have the Feeders defined manually like this:

Code: Select all

['Element 1','Value']=>DB('Cube2', !Dim1, !Dim2, 'Element 1', 'Value');
['Element 2','Value']=>DB('Cube2', !Dim1, !Dim2, 'Element 2', 'Value');
...

Where the Elements are C-Elements in Cube 1 (Source of Feeding). And this works. But I don't want to Change the Rule every time a new C-Element is defined. So I thought the Attribute at the C-Elements would be enough!?

Your current code works because you have manually specified the summarised element in the right hand side, so in effect every n-level element under "element 1" feeds across to the summarised "element 1."

If you use that TI to populate the attribute against all n-level elements and add 1 single feeder line:

Code: Select all

['value'] => DB ( 'cube2', !dim1, !dim2, AttrS ( 'product', !product, 'feeder_attribute' ), 'value' );

It will work for everything, you just need to re-run the TI when new elements get added to make sure all attributes are populated.