Creating Metrics in Bulk
Here’s a quick Command Manager script I wrote this morning when I had to create 60 metrics off of 60 Facts. They were all simple Sum(Fact) Metrics.
//list all metrics in the project
String sProjectName = "PROJECT NAME";
String sFactFolder = "\Schema Objects\Facts\Folder";
String sMetricFolder = "\Public Objects\Metrics\Folder";
ResultSet oFacts = executeCapture("LIST ALL FACTS IN FOLDER '" + sFactFolder + "' FOR PROJECT '" + sProjectName + "';");
oFacts.moveFirst();
while (!oFacts.isEof() )
{
//get name and path of this metric to list properties
String sFactName = oFacts.getFieldValueString(DisplayPropertyEnum.NAME);
//get properties of each metric
execute("CREATE METRIC "" + sFactName + "" IN FOLDER "" + sMetricFolder + "" EXPRESSION 'sum([" + sFactName + "])' ON PROJECT "" + sProjectName + "";");
oFacts.moveNext();
}
To run this script, fire up Command Manager and click New Procedure. Paste it in, update the variables at the top, and save some time. In order to run it, you have to enter your connection information in the Test Information tab on the right side of the procedure editor.
Using this script I was able to create all 60 metrics in about 10 seconds!
Hi Bryan,
Please could you explain it in detail
Which part? What it does or how to run it in Command Manager?
Hi Bryan,
We need to know if there is a way we can rollback the changes made by command manager script in case of any exception.
For example : If I have created the attributes and facts using command manager script and if there is any error I have to rollback, how this can be achieved?
Thanks in advance..
This comment has been removed by the author.
Bryan
I stumble upon one of your brilliant post on Microstrategy forum about SDK customization and wonder if you can give me a hand in guiding me to the right documentation in .NET environment? sorry if this is the wrong place to post. Let me know the right place and I would delete this and repost. I saw your post here https://resource.microstrategy.com/forum/ReplyListPage.aspx?id=8821
I would like to implement an ASPX page to dynamically create/update/delete user account and then redirect them into Microstrategy Web with the right session. But I could not find good .NET documentation. The thing that frustrate me the most is how to I even get to know and import WHAT library of Web SDK into the project or ASPX page. MSDL is full of Java documentation, and those limited .NET samples are wayyyy different from what Java documents show. You can see my post at https://resource.microstrategy.com/Forum/ReplyListPage.aspx?id=31832
For example, Your post above used code “Dim mstrws As New MSTR.MSTRWS”. How and where do I know MSTRWS is a package or class that even exist?
Thanks again Bryan
thanks for this, a nice addition would be if it could add the metric name as the metric column alias, no idea how though.
Hi Erin-Jane-Peachey
You can tweak
execute(“CREATE METRIC “” + sFactName + “” IN FOLDER “” + sMetricFolder + “” EXPRESSION ‘sum([” + sFactName + “])’ ON PROJECT “” + sProjectName + “”;”);
statement and add additional commands in it from below syntax.
CREATE METRIC “” IN [FOLDER] “” EXPRESSION “” [DESCRIPTION “”] [LONGDESCRIPTION “”] [HIDDEN (TRUE | FALSE)] [ALLOWSMARTMETRIC (TRUE | FALSE)] [REMOVEREPORTFILTERELEMENTS (TRUE | FALSE)] [TOTALSUBTOTALFUNCTION (AVERAGE | COUNT | DEFAULT| GEOMETRICMEAN | MAXIMUM | MEDIAN | MINIMUM | MODE | NONE | PRODUCT | STANDARDDEVIATION | SUM | VARIANCE)] [DYNAMICAGGREGATIONFUNCTION (AVERAGE | COUNT | DEFAULT| GEOMETRICMEAN | MAXIMUM | MEDIAN | MINIMUM | MODE | NONE | PRODUCT | STANDARDDEVIATION | SUM | VARIANCE)] [COLUMNALIAS “”] FOR PROJECT “”;
Wonderful. Thanks much
Have Copied the same code and tried to create metrics in MicroStrategy Tutorial Project:
But i could not run the script properly.
I got error Like: Syntax Error at line ‘2’, column’1′. Expected: ADD, ALTER, APPLY, APPEND etc…..
This is the script,
String sProjectName = “MicroStrategy Tutorial”;
String sFactFolder= “\ Schema Objects\Facts\Folder”;
String sMetricFolder= “\Public Objects\Metrics\Folder”;
ResultSet 0Facts= executeCapture(“LIST ALL FACTS IN FOLDER'”+sFactFolder’+” ‘ FORPROJECT’ “+sProjectName+”‘;'”);
0Facts.moveFirst();
while( !0Facts.isEof() )
{
//get name and path of this metric to list properties
String sFactName = 0Facts.getFieldValueString(DisplayPropertyEnum.NAME);
//get properties of each metric
execute(” CREATE METRIC””+sFactName+””IN FOLDER””+sMetrcFolder+”” Expression ‘SUM( [ ” + sFactName+” ] )’ ON PROECT “”+sProjectName+ “”;”);
0Facts.moveNext();
}
Please help me out
Make sure you do New Procedure and run it there. The default view you get when you open Command Manager is for individual commands, whereas Procedures let you run java code to batch multiple commands.
Thank you so much Brian, that looks very promising.
The error I get when I run this procedure is:
Line 3: illegal escape character
…as is it didn’t like the backslash in the path
and other issues with the create metric itself
Line 12: not a statement
Line 12: ‘;’ expected
Line 12: illegal start of expression
although I really pasted your code : execute(“CREATE METRIC “” + sFactName + “” IN FOLDER “” + sMetricFolder + “” EXPRESSION ‘sum([” + sFactName +”])’ FOR PROJECT “” + sProjectName + “”;”);
Also for anyone interested in pushing this a step forward here is a great tech note detailing metric expression (mine needed to be non aggregatable…)
http://community.microstrategy.com/t5/Architect/TN46842-How-to-interpret-dimensionality-symbols-in-the-metric/ta-p/196488
Hi Bryan,
I want to replace the filter of all conditional metrics available in particular folder through command manager, all these metrics uses same filter. could you please tell me how to write the script for that.
The following execute(” CREATE METRIC””+sFactName+””IN FOLDER””+sMetrcFolder+”” Expression ‘SUM( [ ” + sFactName+” ] )’ ON PROECT “”+sProjectName+ “”;”);
code gives
Line 12: not a statement
Line 12: ‘;’ expected
Line 12: illegal start of expression
This worked for me:
//list all metrics in the project
String sProjectName = “PROJECT NAME”;
String sFactFolder = “\\Schema Objects\\Facts”;
String sMetricFolder = “\\Public Objects\\Metrics”;
ResultSet oFacts = executeCapture(“LIST ALL FACTS IN FOLDER ‘” + sFactFolder + “‘ FOR PROJECT ‘” + sProjectName + “‘;”);
oFacts.moveFirst();
while (!oFacts.isEof() )
{
//get name and path of this metric to list properties
String sFactName = oFacts.getFieldValueString(DisplayPropertyEnum.NAME);
//get properties of each metric
execute(“CREATE METRIC \”” + sFactName + “\” IN FOLDER \”” + sMetricFolder + “\” EXPRESSION ‘sum([” + sFactName + “])’ ON PROJECT \”” + sProjectName + “\”;”);
oFacts.moveNext();
}
Hi Bryan,
Please could you explain METRICS it in detail
This last bite of code gives me a compilation error: ‘)’ expected not a statement ‘;’ expected. Not sure how to resolve this. Any help would be greatly appreciated.
//get properties of each metric
execute(“CREATE METRIC ” + sFactName + ” IN FOLDER “+ sMetricFolder + “EXPRESSION ‘sum([” + sFactName + “])’ ON PROJECT “” + sProjectName +””;”);
@David, while building CM command remember about escape character before “. Also your syntax seems to be wrong. At least in 9.4.1 it should be something like:
CREATE METRIC “” IN FOLDER “” EXPRESSION “” FOR PROJECT “” ;
so correct syntax in your example will be:
execute(“CREATE METRIC \””+ sFactName + “\” IN FOLDER \””+ sMetricFolder + “\” EXPRESSION \”sum([” + sFactName + “])\” FOR PROJECT \”” + sProjectName +”\”;”);
Hi,
You can try below code.I have modified it slightly in line no 3,4 and 12.It is working fine for me for version 9.4.1
//list all metrics in the project
String sProjectName = “PROJECT NAME”;
String sFactFolder = “\\Schema Objects\\Facts\\Folder”;
String sMetricFolder = “\\Public Objects\\Metrics\\Folder”;
ResultSet oFacts = executeCapture(“LIST ALL FACTS IN FOLDER ‘” + sFactFolder + “‘ FOR PROJECT ‘” + sProjectName + “‘;”);
oFacts.moveFirst();
while (!oFacts.isEof() )
{
//get name and path of this metric to list properties
String sFactName = oFacts.getFieldValueString(DisplayPropertyEnum.NAME);
//get properties of each metric
execute(“CREATE METRIC ‘”+ sFactName +”‘ IN FOLDER ‘”+ sMetricFolder +”‘ EXPRESSION ‘sum([” + sFactName + “])’ ON PROJECT ‘”+ sProjectName + “‘;”);
oFacts.moveNext();
}