How to bring the total of elements in to other cube?

[dharav]

Hello All

I want to know, how could we bring total of conditionally selected all cell in to a cell of another cube.

Scenario:

Cube 1:

Cube 1.JPG (16.49 KiB) Viewed 8725 times

Here User would input data. For each number they would select either XYZ or ABC and enter data in to respective month (suppose Jan)

Cube 2:

Cube 2.JPG (14.33 KiB) Viewed 8725 times

Here, The sum of all ABC assets should go here. So it should be 60 at ['JAN','ABC'] on the basis of above cube.

How could i sum up individual element choosen in assets type. The sum of all ABC AND XYZ based on input of users.

Let me know if further information required.

Thanks for your time and input!

Dharav

[jim wood]

Topic moved as it was posted in the wrong sub forum,

Jim.

[Wim Gielis]

Hello

Create a TI process to fill the target cube. A rule will not do the trick.

[tomok]

Sorry, it's not possible via rule under your given scenario. Instead of having users enter the data into an element of the Account dimension, you are having them enter a string value that represents an element of the Account dimension. Because of this, you can't use the Asset No dimension to sum up the total of all elements in each Account. You would need to add the Asset No dimension to Cube 2 and then you could have a conditional rule to pull in the amounts from Cube 1:

[] = N:IF(!Account@=DB('Cube 1',!Asset_No,'Asset Type'),DB('Cube 1','!Month),0);

[declanr]

If you want it to be completely rule driven you need an extra mapping cube which is exactly the same dimensions as cube 1 but with the "Asset Type" dimension also from cube 2 (in addition to this you will need to add a "Total" to cube 1's record dimension - the one you have hidden that ends with "No")

Then in your mapping cube you have a rule along the lines of:

Code: Select all

['Amount']=N: If ( !Asset Type @= DB ( 'Cube 1', !Dim1, !Dim2, ..., 'Asset Type' ), DB ( 'Cube 1', !Dim1, !Dim2, ..., 'Amount' ), 0 );

Then your cube 2 would have a rule along the lines of:

Code: Select all

['Amount']=N: DB ( 'Mapping Cube', !Dim1, !Dim2, 'Total Records', 'Amount' );

[java_to_tm1]

As Declan very kindly pointed out, you CAN do this purely using rules only by adding a cube which has the extra dim.

From having tried both approaches, I am firmly of the belief that the effort involved in writing the rules and feeders, especially if you want to go from a source cube with Dims, 1, 2, 3, 4 to a target cube with Dims 1, 3, 5,6, 7 ( via an intermediate cube that has Dims 1,2,3,4,5,6,7 ) is just not worth it.

The larger and larger your cube gets, (and the more dimensions it has) the more I'm inclined towards TI process to achieve this rather than Rules.
Unless the amount of data is small enough (small enough being defined very amBIGuously) I'd suggest that you go the TI route.

The time taken to write a quick script
+ the time taken to publish a worksheet with an action button
+ the user discomfort of having to hit an action button to get the data flowing (as compared to seamless rule-calc)

is next to nothing, especially when weighed against
the performance hit taken by the rule-calculated cubes
+ the time spent investigating broken feeders.

[declanr]

From having tried both approaches, I am firmly of the belief that the effort involved in writing the rules and feeders, especially if you want to go from a source cube with Dims, 1, 2, 3, 4 to a target cube with Dims 1, 3, 5,6, 7 ( via an intermediate cube that has Dims 1,2,3,4,5,6,7 ) is just not worth it.

The larger and larger your cube gets, (and the more dimensions it has) the more I'm inclined towards TI process to achieve this rather than Rules.
Unless the amount of data is small enough (small enough being defined very amBIGuously) I'd suggest that you go the TI route.

The time taken to write a quick script
+ the time taken to publish a worksheet with an action button
+ the user discomfort of having to hit an action button to get the data flowing (as compared to seamless rule-calc)

is next to nothing, especially when weighed against
the performance hit taken by the rule-calculated cubes
+ the time spent investigating broken feeders.

I disagree on the concept of always taking one approach over the other. The rules and feeders for an approach like this are really quite simple so there should be no issue with someone who has a basic grasp of rules and feeders being able to accomplish it. In terms of performance overheads of using rules, there will of course always be a hit as opposed to using TI but any good model only has rules applying to active versions; with all those that are stored for historic reasons being STET'd and then TI's back in... so I wouldn't expect a significant performance hit at all.

I have applied the rules based approach on a number of occasions and never found a significant hit on memory and never had to wait for the rule to calculate. In those models if I had told the users that they had to press an action button after every input; YES they would have done it and the model would have worked but if I gave them both options then they would have chosen the rules every time.

The above however does not say that I would ALWAYS use the rules based approach; which brings me back to the original point that every scenario is different.
If users expect to see cube 2 update automatically then you need to look into the rules based approach, after all there is no point in developing something that "does a job" but does not alleviate the users stress levels... unhappy users = very unhappy developers.

The basic concept of this scenario however is no different to any other when it comes to transferring data within TM1, prioritised as below:

If Automatic Update is needed (after user input):
1/ Rules win
2/ Only use TI if there is absolutely no way to get rules to perform efficiently (however automatic update after a user input normally suggests a forecasting model, if the forecasting model is big enough to cause slow performance then it's likely being forecast at too granular a level for the benefits of the detail to outweigh the forecasting time anyway.)

If Automatic Update isn't needed (after user input or any sort of input e.g TI data loads)
1/ TI every time.


If a rule is the best approach to meet a projects requirements then every effort should be made to see if it is possible to get one working, I consider a working rule to be one that:
1/ Gets the numbers from point A to point B correctly
2/ Performs at an appropriate speed
3/ Doesn't cripple the system on RAM (but if point 2 is met then it's unlikely it will)
4/ Doesn't require the developer to go back and change it after it's implemented

[java_to_tm1]

I disagree on the concept of always taking one approach over the other.

Yup. I was not suggesting the TI process approach as a 'one size fits all' or that I was stating gospel truth. I put the "my belief" disclaimer up front.

In the specific example DJ gave, the requirement was a simple consolidation to one of two elements. In fact the case was trivial enough to be actually have been solved without either the mapping cube approach or a TI process: It could have been solved with

1. * a couple of extra elements in the measure of the source cube (ABC_Jan, XYZ_Jan)
   * a rule in the source cube with an if condition. ['ABC_Jan'] = N: IF ( DB ( <srcCubeName> , xxxxxx_No , 'AssetType' ) @= 'ABC' , ['Jan'] , 0 );
   * a consolidatedElement for the dim xxxx_No : 'Total of xxxx_No'
   * a single rule in the TgtCube ['Jan'] = N: DB (<srcCubeName> , 'Total of xxxx_No', !Account | '_Jan' ) ;

But real-world requirements are way more complicated:

• You might have to reference element parents (source Cube has city dim, target Cube has Country dim)
  You might have to reference an element attribute (Source Cube could have a manufacturing plant dim whose attribute could be a sales region. This sales region could be one of the dimensions in the targetcube).
  You might have to refrence a third cube ( which has perhas % allocations across different sales regions (tgt Cube dim) for a given Manufacturing Plant (source Cube dim) )
  The structure of the source cube dims or the attributes of the elements might change and each change will require the Feeders to be re-processed in a rule-based implementation.

The basic concept of this scenario however is no different to any other when it comes to transferring data within TM1, prioritised as below:

Ah! this is where you and I disagree fundamentally. IMHO: The movement of data between two cubes with different granularity is not necessrily a 50-50 choice between processes and Rules. It is, in my opnion, skewed: towards TI: the skewness increasing as the complexity and/or volume of the data transfer increases.

[dharav]

Thank you all for your valuable feedback.

I would implement those today and will update you.

[dharav]

If you want it to be completely rule driven you need an extra mapping cube which is exactly the same dimensions as cube 1 but with the "Asset Type" dimension also from cube 2 (in addition to this you will need to add a "Total" to cube 1's record dimension - the one you have hidden that ends with "No")

Then in your mapping cube you have a rule along the lines of:

Code: Select all

['Amount']=N: If ( !Asset Type @= DB ( 'Cube 1', !Dim1, !Dim2, ..., 'Asset Type' ), DB ( 'Cube 1', !Dim1, !Dim2, ..., 'Amount' ), 0 );

Then your cube 2 would have a rule along the lines of:

Code: Select all

['Amount']=N: DB ( 'Mapping Cube', !Dim1, !Dim2, 'Total Records', 'Amount' );

Works perfect for me. It had also solved the problem of one more complicated view.


I want to know can we filter a subset subset on the basis of element selected in another subset.

I have a view where i have to establish following functionality:

Cube A

[img]

Filter.JPG (51.82 KiB) Viewed 8510 times

[/img]

Here, If some one select New Jersey from drop down than it should show only NJ1, NJ2,NJ3 only in the raw dimension.

Can we utilize MDX Statement , assign attributes to the elements and apply in to MDX statement? i am not familiar with MDX statements.

Thank you all once again for your Valuable Inputs and time spend on it.

[BariAbdul]

This might help:
http://www.tm1forum.com/viewtopic.php?f=3&t=5933

[dharav]

This might help:
http://www.tm1forum.com/viewtopic.php?f=3&t=5933

Thank you for Input. I couldn't able to do it when there is consolidation element in the Relation subset. Anyidea about the consolidation element along with leaf (Level 0 element) element.


Thanks

Dharav

[BariAbdul]

Thanks to Wim ,Pls go through below examples:

• Leaf-level descendants of a given member:
{TM1SORT( {TM1FILTERBYLEVEL({DESCENDANTS([FIN_Account].[EBIT]) }, 0)}, ASC)}
or:
{TM1SORT( {TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER({[FIN_Account].[EBIT]},ALL,RECURSIVE)}, 0)}, ASC)}

Leaving out the filter by level will return the consolidated element(s) as well. So, DESCENDANTS and TM1DRILLDOWNMEMBER both include the parent element that was drilled upon.
• Leaf-level descendants of 2 consolidated members:
{TM1FILTERBYLEVEL( { UNION ({ DESCENDANTS( [FIN_Account].[EBIT] ) }, { DESCENDANTS( [FIN_Account].[Taxes] ) } ) }, 0)}
• Leaf-level descendants of a given consolidated member, that consolidation included:
{UNION( {FIN_Account.[EBIT]}, {TM1FILTERBYLEVEL({DESCENDANTS(FIN_Account.[EBIT]) }, 0)} )}
or:
{FIN_Account.[EBIT], {TM1FILTERBYLEVEL({DESCENDANTS(FIN_Account.[EBIT]) }, 0)} }
Note: The Union function can be shortened to the + operator (an element is listed only once):
{FIN_Account.[EBIT]} + {TM1FILTERBYLEVEL({DESCENDANTS(FIN_Account.[EBIT]) }, 0)}
or, now EBIT is at the bottom of the list, the order of the elements in the expression is retained left to right:
{{TM1FILTERBYLEVEL({DESCENDANTS(FIN_Account.[EBIT]) }, 0)}, FIN_Account.[EBIT] }

[dharav]

This might help:
http://www.tm1forum.com/viewtopic.php?f=3&t=5933

Hello All/ BariAbdul

Thanks for your time and inputs.

I got the solution of it. I did following step

There are 2 dimension 1-> Source Selection dim 2-> Target Change Dim

1) Created attributes in both dimension for the same elements but different level.

Dim1

California (Level1)

Las Vegas (Level 0)
San Jose (Level 0)
San Fransisco (Level 0)


Dim2

California (Level 2)

Las Vegas (Level 1)
A
B
C
San Jose (Level 1)
D
E
F
San Fransisco (Level 1)
G
H
I

2) Now in dimension 2 - apply mdx statement with filter where you can get the values by equating two dimension with their attributes.

3) Modify the above mdx statement with TM1DRilldownMember.

4) !Bing0 -> Required result is obtained.