?
Solved

what is difference between Fix and IF?

Posted on 2009-07-08
4
Medium Priority
?
2,162 Views
Last Modified: 2012-06-21
what is difference between Fix and IF?
0
Comment
Question by:AJITPADHY
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 5

Accepted Solution

by:
garycris earned 1500 total points
ID: 24803865
Both are used to focus your calculation to a subset of data in your cube

The FIX&ENDFIX command block restricts database calculations to a subset of the database. All commands nested between the FIX and ENDFIX statements are restricted to the specified database subset.

This command is useful because it allows you to calculate separate portions of the database using different formulas, if necessary. It also allows you to calculate the sub-section much faster than you would otherwise.

So if I say
FIX ("Jan");
"Margin" = "Sales" - "COGS";
ENDFIX;

When I run this calc, Margin will only be calculated for the month of January.

'IF' Performs conditional tests within a formula. Using the IF statement, you can define a Boolean test, as well as formulas to be calculated if the test returns either a TRUE or FALSE value.

So similar to above, I can say

IF(@ISMBR("Jan"));
"Margin" = "Sales" - "COGS";
ELSE
"Margin" = #MISSING;
ENDIF;

In this case I test to see if the member being calculated is Jan, if it is then I run the Margin formula, otherwise I set Margin to Missing (NULL).

If your thinking they do the same thing, in some ways they do, but in other ways they allow you to do different things.  FIX assumes you know explicitly what you want to focus on, IF allows you to test conditions and then decide whether to calculate.  You might have a formula that calculates bonuses and you could have something like
IF ("Sales" > 2000000)
"Bonus" = "Sales" * .015;
ELSEIF ("Sales" > 1000000)
"Bonus" = "Sales" * .01;
ELSE
"Bonus" = #MISSING;
ENDIF;

In this case if Sales is not greater 1 million then there is no bonus. If it is greater than 1 Mil but less than 2 mil, then there is a 10% bonus and if greater than 2 mil, then there is a 15% bonus.

There are also performance considerations.
Using IF on a large Sparse dimension can be bad as it has to cycle through all the data blocks in the Index file to see if they meet the criteria.  Generally speaking a FIX is more efficient on Sparse dim.
IF can be more efficient on a Dense dim opposed to multiple FIX statements because the IF allows Essbase to evaluate the block once while it is in memory rather than have to move it in and out of memory multiuple times.
0
 

Author Closing Comment

by:AJITPADHY
ID: 31600995
thanks
0
 

Author Comment

by:AJITPADHY
ID: 24825577
thanks
0
 

Author Comment

by:AJITPADHY
ID: 24825580
thanks
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question