Virtual Dimension Lookup D-Link equivalent in TM1 9.5x

[adaptive_tech]

Hi all

I'm trying to write the equivalent of a simple Virtual "Lookup" D-Link, where a real dimension in the Source cube is matched against a Virtual dimension in the Target cube.

In this case, the Target Cube has one Element of the Measures dimension which is a PickList -- the PickList is sourced from a real dimension.

I've included a pic which shows the behavior I'm looking for. Note that the source cube does not have the GeographicHierarchy, which is the Security dimension for the Contributor Deployment (not sure if that's going to cause a problem once Deployed).

At any rate, I'm having issues getting the data from one cube to the other. I've tried several ways of referencing the PickList in the target cube, but none seem to jive:

Variant 1:
['Base Monthly Salary Expense' ] = N:
DB('SalesTeamSalaryAssumptions',
ATTRS('SalaryCalcMeasures', 'Sale Team Type', 'PickList'),
!FY_Months);

Variant 2:
['Base Monthly Salary Expense' ] = N:
DB('SalesTeamSalaryAssumptions', DB('SalaryCalc', !Versions,
!GeographicHierarchy, !FY_Months, 'Sale Team Type'),
!FY_Months);

Variant 3:
['Base Monthly Salary Expense' ] = N:
DB('SalesTeamSalaryAssumptions',
ATTRS('SalaryCalcMeasures',
DB('SalaryCalc', !Versions,
!GeographicHierarchy, !FY_Months, 'Sale Team Type',
!FY_Months),
'PickList'),
!FY_Months);


So, my questions are:

1) What is the proper expression to reference the information I'm looking for?

2) What's the corresponding FEEDER statement, and what kind of overFEEDING issues should I watch for?

Thanks!

[tomok]

I no nothing of this "Virtual" versus "Real dimension. In TM1 there is a dimension, period. This must be a Cognos Planning thing. Here is your rule:

Code: Select all

['Base Monthly Salary Expense' ] = N:DB('SalesTeamSalaryAssumptions',!FY_Months,DB('SalaryCalc',!Version,!GeographyHierarchy,!FY_Months,'Sale Team Type');

The feeder would be:

Code: Select all

['Sale Team Type' ] => ['Base Monthly Salary Expense']; 

[mvaspal]

This must be a Cognos Planning thing

Virtual dimension is something like the picklist function in TM1 if the picklist is based on a dimension or a subset. In this case, the measure 'Sale Team Type' in the target cube is a 'virtual' dimension and 'SalesTeamTypes' in the source cube is a regular dimension, also serving as the picklist in the target cube - at least in Cognos Planning.

[JDLove]

Hi

This is pretty common as you would imagine, picklists are used to simplify the data entry in a similar way as EP.

The rule would go something like this

#Salary Calc Cube#

['Measure']=N:

IF(
!Sales Team@=DB( 'SalesTeamSalaryAssumptions',!FY_Months,'Sale Team Type'),
DB( 'SalesTeamSalaryAssumptions','Version','GeographyHierarchy',!FY_Months,!Sale Team Types),
CONTINUE
);

You would need to define the Version and Geography, you would generally have version on any assumption cube.

If you had more Picklists that referred to dimensions then just add then in the IF statement.
Dim1@=DB(......................................'Picklict Measure') &
Dim2@=DB(......................................'Picklist Measure') &
Dim3@=DB(......................................'Picklist Measure ') ,
DB(..........................................................),
CONTINUE);


From the source cube the feeder would be fairly simple.
#SalesTeamSalaryAssumptions Cube#
SKIPCHECK;
FEEDSTRINGS;


FEEDERS;
[{'Sale Team Type','Base Monthly Salary Expense'}]=>
DB( 'SalesTeamSalaryAssumptions','Version','GeographyHierarchy',!FY_Months,DB( 'SalesTeamSalaryAssumptions',!FY_Months,'Sale Team Type'));

Make sure you have the correct setting in the tm1s.cfg
ReevaluateConditionalFeeders=T

You question on feeders !
In simple Input or assumption cubes there is little risk of over feeding, however when you get into complex input assumption cubes with picklists that mapp to dimensions then this can get rather more nasty, in some of our cubes we have found it difficult to trigger the feeders in the way we would expect.
With some date type situations you may need to feed all months but generally we have not been forced to overfeed.

The other approach you can use is TI but we prefer to keep it all rules based and dynamic if at all possible.

I noticed your question earlier about EP and how TM1 compare for Accumulation and lookups etc, its a pretty deep question really as there is much to consider.
I think EP was also difficult in that you were forced into work arounds due to cube size issues that created many design issues, TM1 has much more flexibility and power in the rules and TI. I would say its a fairly steep learning curve and you can find a wealth of information here.

HTH
JD

[tomok]

['Measure']=N:

IF(
!Sales Team@=DB( 'SalesTeamSalaryAssumptions',!FY_Months,'Sale Team Type'),
DB( 'SalesTeamSalaryAssumptions','Version','GeographyHierarchy',!FY_Months,!Sale Team Types),
CONTINUE
);

This IF statement logic is totally unnecessary and inefficient. My example posted above does the same thing and faster.

[adaptive_tech]

Hi all!


Tomok:
Thank you for the fast response! Turns out your solution works great -- ironically, it was a direct match to the "Variant 2" I had tried earlier, except it I just seemed to have my dimension order mixed up. As a side note, I find the lack of warning messages in TM1 for this kind of problem to be very disturbing.


JDLove:
Thank you for your input. I saw what you were aiming for here, although I think there are a few minor syntax errors (I couldn't get it to work, even with some minor tweaking). Even though Tomok's method is simple and direct, I think your conditional approach would have great value if someone wanted to have additional conditional logic built into the link -- for instance, if they wanted to bring in different values based on other conditional variables in the cube. In those instances, I think your conditional approach would work well.

As a side note, I noticed you used a few explicit references to dimension names in single quotes:

Code: Select all

['Measure']=N:

IF(
!Sales Team@=DB( 'SalesTeamSalaryAssumptions',!FY_Months,'Sale Team Type'),
DB( 'SalesTeamSalaryAssumptions','Version','GeographyHierarchy',!FY_Months,!Sale Team Types),
CONTINUE
);

For example, you use:

Code: Select all

DB( 'SalesTeamSalaryAssumptions','Version',

Is this expression supposed to refer to the entire set of elements in the Version dimension?

[tomok]

As a side note, I noticed you used a few explicit references to dimension names in single quotes:

Code: Select all

['Measure']=N:

IF(
!Sales Team@=DB( 'SalesTeamSalaryAssumptions',!FY_Months,'Sale Team Type'),
DB( 'SalesTeamSalaryAssumptions','Version','GeographyHierarchy',!FY_Months,!Sale Team Types),
CONTINUE
);

For example, you use:

Code: Select all

DB( 'SalesTeamSalaryAssumptions','Version',

Is this expression supposed to refer to the entire set of elements in the Version dimension?

No. I'm sure this is an oversight on his part. You use the single quotes to surround a specific element in a dimension and the bang (!) to denote all elements in the dimension.

[JDLove]

Hi Again

Very sorry for the confusion, my mistake it was friday night and I was half out the door...I got it all messed up !

However, please don't let that get in the way of progress because it leads to some interesting modelling. What I was trying to express was the common scenario where you are mapping from a source cube that has multiple Picklist (virtual Dimensions).
as an example a Capex input form with Capex Type, Month, as picklist virtual dimensions, the calc cube will have !Month and !Capex Type as dimensions and this cube calculates depreciation etc.
To mapp to these Picklist Virtual Dimensions;

#Calc Cube
['Capex_Value']=N:

!Month @= DB('SourceCube',!Year,!CostCenter,'Purchase_Month') &
!Asset Type@= DB('SourceCube',!Year,!CostCenter,'CAPEX_Asset_Type'),
DB('SourceCube',!Year,!CostCenter,'Capex_Value');

#Source Cube
SKIPCHECK'
FEEDSTRINGS;



FEEDERS;

[{'Purchase_Month','CAPEX_Asset_Type','Capex_Value'}]=>
DB('CalcCube',!Year,
DB('SourceCube',!Year,!CostCenter,'Purchase_Month'),
DB('SourceCube',!Year,!CostCenter,'CAPEX_Asset_Type'),
!Asset Type,'Capex_Value');


In your exampe you have the virtual dimension in target cube so a simple rule as presented by Tomok is all thats needed.

This example in the below link provides a really clear example of the rules and feeders. Its an interesting read !
You can download the whole TM1 model and look through the rules and feeders which are very clearly explained in the comments.

http://blog.flowolap.com/post/2012/08/0 ... esign.aspx

Regards
JD

PS if you want to see other examples or have any question on the example let me know !

[David Usherwood]

Hmmmm.....
I think you are using the picklists to drive the feeders...
If so, when the users change the picklist content you'll need to refire the feeders, either manually, or via CubeProcessFeeders in a TI, or via a restart.
Which approach have you adopted?

[lotsaram]

Hmmmm.....
I think you are using the picklists to drive the feeders...
If so, when the users change the picklist content you'll need to refire the feeders, either manually, or via CubeProcessFeeders in a TI, or via a restart.
Which approach have you adopted?

Well no, unless I've missed something. Cells filled by picklists by their nature hold string values. And as we all know one of the quirks of TM1 is that numeric cells fire feeders only when the value changes from null to a non-null value whereas a string cell will fire a feeder upon each change to a non-empty value.

[Duncan P]

In fact you can use a picklist on a numeric cell and if it contains only strings representing valid numbers you are fine. If you choose an item that does not resolve to a number it treats it as a data-entry error, reporting "Data Spread Failed".

In fact in the light of this and another recent thread I have just tried a drop-down consisting of cell spread commands which work fine. This might be a way of restricting what spreads are available.