Error "the formula for derived metric is incorrect"

Oct 4, 2013 at 3:48 AM
Hello,
I've installed both the components below on a machine that has iserver+desktop
  1. RScript Functions Installer from http://rintegrationpack.codeplex.com/
  2. MicroStrategyR Package for R from http://CRAN.R-project.org
I'm using a simple example from tutorial - Monthly Revenue Forecast report. I was able to successfully generate the metric definition using SeasonalForecasting.R script

Once I insert the definition below into the derived metric editor, it complains about the formula being incorrect. I've tried using both desktop and web.

RScript<_RScriptFile="C:\Program Files (x86)\R Integration Pack\RScripts\SeasonalForecasting.R", _InputNames="target, trend, season", StringParam9="SeasonalForecast">([Revenue], [Month Index], [Month of Year])

I believe after installing RScript functions installer, MSTR should be able to recognize the RScript function. Is an R function supposed to be populated in the f(x) list in derived metric editor or could I be missing something else?

Once we get it working in tutorial (with knowledge of all components required), we plan to deploy this in our enterprise solution.
Your help is appreciated!
Rishika
Coordinator
Oct 4, 2013 at 6:03 PM
Hi Rishika,

I suspect you may not have followed the post-installation instructions for the RScripts Functions installer, starting on page 6:
Post-installation requirements for MicroStrategy projects
Once the installation process has been completed, the R script functions are automatically added to newly created projects.
If you have MicroStrategy projects that existed prior to the installation of the R script functions, you must upgrade the projects using the MicroStrategy Configuration Wizard to support the following...
Adding these functions to existing projects requires (a one-time) addition of new functions to the project's metadata as well as a schema update (since functions are schema objects). The installer cannot do this for you since it might affect other users on the system. I suspect from your comments that you may have missed this step.

Can you verify and reply?

Thanks,

Rick
Oct 4, 2013 at 8:15 PM
Thanks Rick.
I was successfully able to add the RScript functions to my project after upgrading it. However, I see no data returned for the derived metric in desktop (the save action on web hangs)

Here's the error in DSSErrors.log:

[Analytical Services][Error][0x80042425] Analytical Engine Error. Apply function failed. Internal error.
[Analytical Services][Error][0x80002434] Analytical Engine plug-in function error: [CRFPCommonNNGEN::ExRScript: R script execution failed: cannot open the connection]

I was getting this "Cannot open connection" error when trying to hit "save" in the R console as well, after previewing the header block.
However, since I was using the sample script SeasonalForecasting.R and did not make it any changes to it, I captured the metric definition as is.

RScript<_RScriptFile="C:\Program Files (x86)\R Integration Pack\RScripts\SeasonalForecasting.R", _InputNames="target, trend, season", StringParam9="SeasonalForecast">(target, trend, season)

Please let me know your thoughts on this error.

Thanks!
Coordinator
Oct 4, 2013 at 8:41 PM
Hi Rishika,

Unfortunately, you've hit a bug with that script since it tries to automatically install the pmml package. In that sample script, there's this code segment:
    if(is.na(match("pmml", installed.packages()))) {       #package installed?
      install.packages("pmml")                             #  install package
    }
    library(pmml)                                          #Load pmml package       
If the the pmml package is not installed, the script tries to load it with install.packages. The bug is that, if you don't have a default CRAN Mirror specified, R will popup a window on the machine prompting you to select a CRAN Mirror. Unfortunately, since that window pops up on the server and not your Web client, I suspect your script is hung waiting for a CRAN Mirror to be selected since there's no one to notice/respond the pop-up window on the server. The fix to this bug is a change to the script replacing the install.packages code with this one:
    if(is.na(match("pmml", installed.packages()))) {       #package installed?
      install.packages("pmml", repos="http://cran.rstudio.com"))  #  install package
    }
    library(pmml)                                          #Load pmml package       
I recommend using the RStudio mirror because it automatically re-directs (for details, see http://blog.rstudio.org/2013/06/10/rstudio-cran-mirror).

Other workarounds include:

1) Use the R Console to manually install any package dependencies on the Intelligence Server machine (or Desktop Client for derived metrics in 3-tier or if your running 2-tier),
2) Specify a default CRAN Mirror in the .RProfile file on that machine using this code "options(repos = c(CRAN = "http://cran.rstudio.com"))" , or
3) if you're not interested in the PMML, simply comment out or remove the code associated with it from the script.

Finally, be sure to restart your Intelligence Server to eliminate all sides effects of the hang.

Sorry for your troubles. This will be fixed in the next release which we're working to get out later this month. Please reply back on whether this solves your problem.

Thanks,

Rick
Oct 5, 2013 at 2:53 AM
Changing the script to include a default CRAN mirror helped resolved the issue.

Thanks a lot for your prompt help Rick!
Nov 26, 2013 at 7:12 AM
I did everything written in the previous messages but my problem is still continue.

How can i solve the problem?

Probably something is missing but in where?
Coordinator
Jan 7, 2014 at 9:39 PM
deniz_s: Sorry for the delay, if you're still having problems, please provide more details, including:
  • What is the nature of the problem you're seeing.
  • The RScriptErrors.log or DSSErrors.log file with relevant entries associated with this problem.
  • The analytic signature for your script
  • The Client you're using and whether your metric is derived or not.
  • The operating system for I-Server and Desktop Client
  • Versions of MicroStrategy and R
  • Verify that your user and the system user used by MicroStrategy have administrator rights or sufficient rights to access the file system locations you're using.
  • Anything else you think might be helpful (please err on providing too much info since we don't know your application or environment).
Thanks,

Rick
May 8, 2014 at 12:10 PM
I am doing the same as "rishikamahajan" was doing , but i am getting following error while pasting the metric expression

"formula for derived metric is incorrect "

Here's the error in DSSErrors.log:

[PID:4880][THR:6772][ParserXgi][Error][0x80041A03] Searching for 'Target' resulted in no matches.


Can anyone please help me to resolve the problem.

thanks in advance.
Coordinator
May 8, 2014 at 3:40 PM
Sorry you're having trouble but the solution here is simple: "Target" is the name of the R variable, you need to replace it with the name of the metric in your environment that you want to be the target of the calculation. For more details, see the example in the User's Guide (Page 33 in version 6), in particular Step 4a where you have to replace the inputs with your metrics:

4 Map the three inputs to MicroStrategy metrics using the following steps:
  a Highlight Target in the expression and replace it with Revenue from the Report Objects.
Hope this helps,

Rick
May 13, 2014 at 7:55 AM
Hi rpechter,

Thanks for your support

I have tried the above solution , but still it is not working

it gives below error in Insert New Metric Wizard-

"the formula for derived metric is incorrect"

DSSErrors.log has same error :

"[PID:4880][THR:6772][ParserXgi][Error][0x80041A03] Searching for 'Target' resulted in no matches."

thanks in advance,
mahesh
Coordinator
Jul 31, 2014 at 9:30 PM
While I appreciate you believe you tried the above solution, which replaces Target in the metric expression with the name of an actual metric from your project, that error message indicates that you're still including Target in the metric expression. You have to replace Target with the name of the metric you want to forecast. Until you do, you will continue to get this error.
Feb 4, 2015 at 1:26 AM
Try for this,

RScript<[_RScriptFile]="SeasonalForecasting.R", [_InputNames]="Target, Trend, Season">(Revenue, [Month Index], [Month of Year])

Also please upgrade the MSTR project to add R script.