Can you use an ATTRS within a DIMIX rules function?

Post Reply
tosca1978
Posts: 101
Joined: Thu Oct 20, 2011 6:53 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2007
Location: London, UK

Can you use an ATTRS within a DIMIX rules function?

Post by tosca1978 »

Hi,

I have a project budget cube in which an end user will put in a start and end date of a project and then input the budget phased monthly. I wish to write a rule that only enables the user to enter budget data into the months that fall within the start and end date of the project. Sounds simple enough!

The rule that I have written does not work (for info, the full rule is at the bottom of the post). So I have set about breaking my rule down and seeing if the components all work. I have written the following rule:

IF(DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,ATTRS('Project_Measures',!Project_Measures,'TimeMonth')))=0,3,2);

This DIMIX test = if the 'TimeMonth' ATTR of the Project_Measures dim DOES NOT exist as an element within the 'Time_Month' dim then 3, therwise 2. Within the cubes this returns "3"'s meaning that the DIMIX test failed - the ATTR do not exist. However, if I do the same test in excel with a DBRA and a DIMIX test I can prove that they do exist and the rule should return a "2".

I have tried making the element attribute text and numeric but I get the same result (note that I still used 'ATTRS' in the rule - as 'ATTRN' returned an error when trying to save the rule).

So my question is...can I use an element attribute as the element in a DIMIX test within a rule (as I am doing above) or does TM1 not allow this?

As always, any help or guidance would be much appreciated.


For info:

Within the small rule above I am just trying to determine the correct logic of teh rule. The full rule to achive what I wanted is this:

IF(
ELISCOMP('Project_Measures',!Project_Measures,'FY 2011')=1,
IF(
DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,'Original Start Month'))>DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,ATTRS('Project_Measures',!Project_Measures,'TimeMonth')))
0,
IF(
DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,'Original End Month'))<DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,ATTRS('Project_Measures',!Project_Measures,'TimeMonth'))),
0,
CONTINUE
)
),
CONTINUE);
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Can you use an ATTRS within a DIMIX rules function?

Post by declanr »

Do you not simply want to check if the Attribute returned exists as an element?

If so the code would be as follows (without the DB):
IF(DIMIX('Time_Month',ATTRS('Project_Measures',!Project_Measures,'TimeMonth'))=0,3,2);
Declan Rodger
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Can you use an ATTRS within a DIMIX rules function?

Post by lotsaram »

tosca1978 wrote:IF(DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,ATTRS('Project_Measures',!Project_Measures,'TimeMonth')))=0,3,2);
There is no issue with using ATTRS in a rule, or any function for that matter which you expect to return a string and embedding the function into a DB(). This should work, ... provided that the string exists a a member of the dimension at the appropriate location in the DB formula.

The ATTRN would fail with an error inside a DB as an element reference as elements by definition are defined as strings and the DB therefore expects string references only. My assumption would be that your "TimeMonth" attribute is actually defied as numeric type not string. Using ATTRS to call it is probably giving you a "silent fail" and returning a blank string as there is no string or alias type "TimeMonth" attribute defined (note that's a guess, albeit an informed one as I haven't explicitly tested this!). The reason this probably isn't failing in Excel is that the DBR formula automatically take the text value of any numeric arguments from a DBRA or another DBR and so are handling your type mismatch without you realizing.
Christopher Kernahan
Community Contributor
Posts: 147
Joined: Mon Nov 29, 2010 6:30 pm
OLAP Product: Cognos TM1
Version: 10.1
Excel Version: Office 2010

Re: Can you use an ATTRS within a DIMIX rules function?

Post by Christopher Kernahan »

This is a legal operation.
DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,'Original Start Month'))>DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,ATTRS('Project_Measures',!Project_Measures,'TimeMonth')))
I'm confused by the cube setup, but here are some items to look at.

Are the month's inputted as numbers or words?
Are the element names in the Time_Month dimension numbers or words?
Is the attribute a string or a text? Are you comparing strings with strings and numbers with numbers?

To diagnose your issue, why not split your formula into further constituent parts and see if they work independently, then join them together step-by-step. i.e if they value returned by Attrs is invalid, then it won't work in any formula regardless.
tosca1978
Posts: 101
Joined: Thu Oct 20, 2011 6:53 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2007
Location: London, UK

Re: Can you use an ATTRS within a DIMIX rules function?

Post by tosca1978 »

Hi Lotsaram/Chrostopher,

Thanks to you both for your replies. Christopher is some more info to help you understand teh cube set up (please be aware that I did not set up this cube - a consultant did):

The Original Budget cube consists of:
Project Code dim
Budget Line Item Dim
Project_Measures Dim (Start Date/End Date/Start Month/End Month/Profile (picklist)/Phased cost by month (Jan-11/Feb-11 etc)

The idea is that the end user types Start Date and End Date into Active Form eg 01/03/2011 and 01/06/2011. The Start Month is rule derived and produces "201103" and "201106". Now I only want the end user to be able to enter budget data into the months within the start and end dates of the project.

So, in the Project_Measures dim I have deleted my attributes that I build earlier (I had one as a text attribute and one as a numeric attribute to test if either worked), and I have created an alias called "TimeMonthValue". So against the numeric element 'Jan-11', the alias is '201101'.

In the Time_Month Dim the numerical element is '201101' and I have also created an alias called "MMM-YY". So against the numeric element '201101' the alias is 'Jan-11'.

I know that I only needed to create one alias but I doubles up to see if it helped.

My dimix test rule is:

IF(DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,!Project_Measures))=0,3,2);

This still places "3"'s in the cube so the dimix test is returning false. I have also tried:

DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,ATTRS('Project_Measures',!Project_Measures,'TimeMonthValue')))=0,3,2);

Still "3"'s so dimix test is still failing.

I must be missing something very simple!

Any further suggestions would be welcomed.

Many Thanks
tosca1978
Posts: 101
Joined: Thu Oct 20, 2011 6:53 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2007
Location: London, UK

Re: Can you use an ATTRS within a DIMIX rules function?

Post by tosca1978 »

Declanr,

thanks for your post. Your suggestion of dropping the DB() worked. I thought that I was taking the "safer" approach using DB(). It was my understanding that whilst some rules require you to refer to internal elements within the DB() to work as opposed to the square brackets - this was not true of the opposite. I thought that the only downside of using DB() to refer to internal elements was that the rule would take longer to perform - but it was a safer option.

Anyway - I was clearly wrong as this DIMIX test using the ATTRS only worked when I removed the DB().

The full that I have used to stop the end user from entering budget data into any month outside of the project start and end date is below. It is not fully dynamic however! Because the Project_Measures dim has a mixture of set up elements and time elements I have used ELISCOMP to apply the rule to the months that have 2011 as a parent. I will have to repeat the rule for each year (not a big deal as trhere is only 10 years in the budget but still not tidy).

The parent of 2011/2012/2013 etc is "Phased_Cost". However, if I apply the ELISCOMP rule to Phased_Cost it does not apply to all n levels under the full years.

If anyone has a tip to get around this I would be very gratefull. If not, then I can live with 10 rules.

Declanr/Lotsaram/Christopher - many thanks for all of your help - it's much appreciated.
tosca1978
Posts: 101
Joined: Thu Oct 20, 2011 6:53 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2007
Location: London, UK

Re: Can you use an ATTRS within a DIMIX rules function?

Post by tosca1978 »

All,

sorry I forgot to add the ful rule that I used at the bottom of my post - here it is:

[]=N:
IF(
ELISCOMP('Project_Measures',!Project_Measures,'FY 2011')=1,
IF(
DIMIX('Time_Month',ATTRS('Project_Measures',!Project_Measures,'TimeMonthValue'))<DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,'Original Start Month')),
0,
IF(
DIMIX('Time_Month',ATTRS('Project_Measures',!Project_Measures,'TimeMonthValue'))>DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,'Original End Month')),
0,
CONTINUE)
),
CONTINUE
);
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Can you use an ATTRS within a DIMIX rules function?

Post by declanr »

With the ELISCOMP function not doing quite what you want, are you aware there is an ELISANC function which relates to ancestors rather than components so you could apply it even with 2 levels of separation.
Declan Rodger
tosca1978
Posts: 101
Joined: Thu Oct 20, 2011 6:53 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2007
Location: London, UK

Re: Can you use an ATTRS within a DIMIX rules function?

Post by tosca1978 »

Declanr,

Thank you VERY much - I'm now using ELISANC and it works great. Rule is:

[]=N:
IF(
ELISANC('Project_Measures','Phased_Cost',!Project_Measures)=1,
IF(
DIMIX('Time_Month',ATTRS('Project_Measures',!Project_Measures,'TimeMonthValue'))<DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,'Original Start Month')),
0,
IF(
DIMIX('Time_Month',ATTRS('Project_Measures',!Project_Measures,'TimeMonthValue'))>DIMIX('Time_Month',DB('Original Budget',!PAAR_AFE,!T3,'Original End Month')),
0,
CONTINUE)
),
CONTINUE
);


Thanks again!
Post Reply