Automate System Manager Workflows

by

February 26, 2017

2:40 pm

4 Comments on Automate System Manager Workflows

MicroStrategy, System Manager

, , , , , , , , , ,

So you have created and tested your new MicroStrategy System Manager workflow and are thinking.. now what? I mean obviously running a workflow manually for testing purposes is fine but for production, you are probably expecting there to be a special System Manager scheduler where you can now schedule this workflow to run automatically. Right? Wrong.

MicroStrategy System Manager is a great tool but unfortunately there is no built in scheduler. However, luckily we have our trusty Windows Task Scheduler to help us schedule our new workflow. The process is pretty straight forward. Let’s say we have created a System Manager workflow named “My_Workflow.smw” in the folder C:\Workflows. The steps to automate this workflow are as follows:

1. First, we will create a batch file that will call the command to execute the workflow. On the same machine as the workflow, open a text editor like notepad and add the following text:
MASysMgr.exe -w C:\Workflows\My_Workflow.smw “UserName=Administrator” “Password=”

2. Be sure to replace the workflow path and username/password with your appropriate values. Save the file as “My_Workflow.bat” or another name of your choosing. The file should look similar to below.

Task Batch File Command

3. Go to Control Panel->Administrative Tools->Task Scheduler

4. Click “Create Basic Task…” from the menu on the right.

create basic task

5. Give the task a name and click next.

6. Choose the frequency. For this example I will choose Daily.

7. Enter the task start time. I will enter 9:00AM.

8. Choose the option “Start a program”.

9. Browse to the batch file we created in the first step. Also, I will usually populate the optional “Start in” text box with the same directory that my batch file is located. See my example below. My batch file is in the G:\temp folder so I enter that in the “Start in (optional)” field.

10. Click next and then Finish. You should now see your new schedule appear in the list of scheduled tasks.

That’s it! Your new workflow will now run every day at 9:00AM!

Please leave comments below!

Automation using MicroStrategy System Manager

by

February 13, 2017

10:37 pm

4 Comments on Automation using MicroStrategy System Manager

MicroStrategy, System Manager

, , , , , , ,

Let’s face it, every shop can benefit from automation. Automation with MicroStrategy System Manager improves reliability and efficiency. What do I mean? Well, let’s say you have very important executive reports that need to be sent every Sunday but you do not want to send the reports in the event that a data load fails because then they will be wrong. In addition, if the data load fails and the reports do not go out, you would like the BI team to be notified. This is a very common type of requirement in the wonderful world of BI. So, what should you do? Develop a custom process outside of MicroStrategy? No, this is a perfect scenario that can be handled by System Manager.

Let’s get started developing a process with System Manager to fulfill this requirement.

Step 1. Open a new workflow in System Manager

Open System Manager and click File->New Workflow

Step 2. Determine if the current day is Sunday

In System Manager in the upper left, click the question mark icon and then click anywhere on the empty white space in our new workflow to create a new decision object. Now, click the white mouse icon to the right of the question mark; other wise every time you click inside the workflow designer a new decision object will be created.

This is an important object because it will help you control the flow of the process. This object can check for files, days of the week or other parameter values. For now we are going to use this to check if the current day of the week is Sunday.

Now, select the new decision object and a list of properties should appear on the right. Choose the option “Weekday(s) Check” and check the option for “Sunday”. Also, rename this object “Sunday” by right clicking and choosing rename.

Step 3. Determine if ETL data load succeeded or failed

Usually in BI data loads are performed by an ETL tool with processes that are developed by the ETL team. It is common practice to set some sort of flag upon successfully loading the data. In my current environment our ETL team will create what we call a “trigger file”. This is simply an empty file the ETL process will create in a specific directory after the data is successfully loaded. The presence of this file lets other processes know that the database is up to date with the correct data. Another approach is to set a database value in a table after the data is loaded. Either approach will work with System Manager. For now, let’s assume the ETL process creates an empty file upon successfully loading the data.

Now, in System Manager in the upper left, click the question mark icon and then click anywhere on the empty white space in our new workflow to create a new decision object. Next click the white mouse icon to the right of the question mark; other wise every time you click you will keep creating new decision objects.

Next select the new decision object and a list of properties should appear on the right. Choose the option labeled “File/Directory Check” and enter the full path to the trigger file. Also, make sure the drop down box labeled “File/Directory Check Condition” has the value “Exists”. This means that if the file exists this object will return SUCCESS, otherwise it will return FAILURE. Rename this object “Check flag”.

Finally, click the green arrow button in the upper left menu, then click the “Sunday” decision object and drag your mouse pointer over to the “Check Flag” decision object. This will create a link between the two objects where if the Sunday decision object returns SUCCESS, the process will then move on to the next step and execute the Check Flag decision object. Your process should look similar to below.

It is also worth mentioning that if the file is available via FTP, we could use the File Transfer Download object instead. Also, if the ETL process sets a database value after loading the data instead of creating an empty file, we could use the Execute SQL task to determine if the data load was successfully. The point is that there are many ways to handle this part of the process. It really depends on your environment, however, the methods I have pointed out are the most common I find.

Step 4. Execute the reports

To execute the reports for this example we are going to use a Narrowcast Server object. In the left tree menu under MicroStrategy Products->Command Manager click “Narrowcast Server (Windows Only)” and drag it over to the workflow designer to create a new object. Next enter the logon info for your Narrowcast meta data and choose the option to “Execute script statements” and enter the script command to execute the Narrowcast job you would like to run. See below example.

Next click the green arrow button, then click the Check Flag decision object and drag your mouse pointer to the new Narrowcast object. Your workflow should now look similar to below.

At this point our workflow will check if the current day is Sunday and if it is it will check that the data has been loaded based on the existence of a file and if the file exists, the process will execute the executive reports via Narrowcast.

5. Alert BI team if data load fails

Currently the workflow will execute the report job if every works but what if the data load fails and the trigger file does not exist? Now we need to add logic to handle failures.

In the left tree view under System Tools folder, click the “Send Email” object and drag it over to the workflow. Next click the red arrow and click the “Check Flag” decision object and drag your mouse pointer over to the new “Send Email” object. Rename the Send Email object “Alert BI Team”. Finally, enter the send email properties such as From, To, Body, Subject and you will probably need the SMTP server info for your companies SMTP server. The finished workflow should look similar to below. To run the workflow, click the lightning bolt located in the top menu.

Finally, save the workflow with a name of your choosing and that’s it! Now, this exact solution may not work for everyone because I have assumed that you send reports using Narrowcast or that your ETL process sets some sort of flag if it fails or even that you have an SMTP server to send email. A lot of this stuff is pretty standard but the main point of this post is to introduce you to the kinds of things that can be handled by System Manager to make your environment a little more manageable and efficient.

In my next post I show you how to schedule this System Manager workflow to run automatically.

Please share your comments!

MicroStrategy SDK Part 2 – How to execute a report and return the results as XML using URL API

by

January 23, 2017

10:36 pm

3 Comments on MicroStrategy SDK Part 2 – How to execute a report and return the results as XML using URL API

MicroStrategy, SDK

, , , , , , , , , , , , , , , ,

In the previous MicroStrategy SDK post I showed you how to setup your Eclipse Java development environment, create a new Dynamic Web Project and add the MicroStrategy JAR files that are required to communicate with the MicroStrategy Intelligence Server.

In this post I will show you how to use the MicroStrategy URL API to execute a report, retrieve the results as XML and display the results to the user from a custom Java web application. So let’s get started.

Step 1 – Create a new Dynamic Web Project by following the instructions from step 6 in the previous post.

Step 2 – Project Setup: Create a new package, servlet class, helper class & default JSP page

a. Expand the newly created project in the Project Explorer then expand Java Resources and right click the src folder and choose New -> Package. Give the package a name like com.mstrcustom.pkg and click Finish. A package is simply like a container.

b. Right click the newly created package and choose New -> Servlet. Enter a name for the new servlet class like MSServlet in the field labeled Class name and click Next and Next again on the following screen. On the next screen make sure doGet and doPost methods are checked, then click Finish. The result will be an empty servlet class that should look similar to the below screenshot.

c. Right click the newly created package and choose New -> Class. Enter a name for the class such as MSHelper and click Finish. This class will contain all of the helper methods we will create to connect to MicroStrategy, execute reports and return the results as XML.

d. Right click the WebContent folder and choose New -> JSP File. Name this file index.jsp. Note: There is a reason we are choosing this file name. In a nutshell, if a user navigates to our site but does not specify a specific page in the URL. For example, if a user enters www.AnySite.com, the server knows to look for default pages with a certain name like index.jsp and will display this page by default. This way the user does not have to enter the full URL www.AnySite.com/index.jsp. These default page names are contained in the WEB-INF/web.xml file and this is how the server knows to use this page by default if the user does not specify a particular page.

Step 3 – Add code to the helper class.

  1. Add the following import statements at the top of the helper class:
import com.microstrategy.web.objects.*;
import java.io.*;
import java.net.*;
import javax.xml.parsers.*;
import javax.xml.transform.*;
import org.xml.sax.InputSource;
import org.w3c.dom.*;
import org.w3c.dom.CharacterData;

2. Add the following code to your MSHelper class so it looks similar to below. In this step we have simply added some string properties and a default constructor method that will set the default MicroStrategy connection properties when the class is instantiated.

public class MSHelper {
String serverName;
String projectName;
String userName;
String userPassword;
String baseURL;
String sessionState;
String styleName;
String currentReportURL;
 
public MSHelper()
{
// Default MicroStrategy Connection Info
serverName = "";
projectName = "";
userName = "";
userPassword = "";
baseURL = "http:///MicroStrategy/asp/taskAdmin.aspx?"; // ASP Version of Mstr Web
styleName = "CustomXMLReportStyle"; // This is a Mstr plugin
 
}
}

3. Add the below method GetContentXML to the MSHelper class. The function accepts the final API URL as a parameter and will return an XML document object.

private Document GetContentXML(String sUrl1) throws Exception
{
URL uURL;
StringBuffer sResult;
sResult = new StringBuffer("");
 
// Get XML report results from MicroStrategy
try {
uURL = new URL(sUrl1);
BufferedReader oReader = new BufferedReader(new InputStreamReader(uURL.openStream()));
String sInputLine = null;
 
while((sInputLine = oReader.readLine()) != null)
{
// Skip task response start/end tags
if(!sInputLine.contains("taskResponse"))
{
System.out.println("XML Line: " + sInputLine); // DEBUG
sResult.append(sInputLine);
}
}
 
oReader.close();
} catch (MalformedURLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
 
// Process XML document
String strXML = sResult.toString();
Document returnDoc = null;
try {
DocumentBuilderFactory dbf =
DocumentBuilderFactory.newInstance();
DocumentBuilder db = dbf.newDocumentBuilder();
InputSource is = new InputSource();
is.setCharacterStream(new StringReader(strXML));
System.out.println("Got char stream (is)"); // DEBUG
System.out.println("strXML: " + strXML); // DEBUG
 
Document doc = db.parse(is);
System.out.println("Got parsed document (doc)"); // DEBUG
 
NodeList nodes = doc.getElementsByTagName("mstr-report");
System.out.println("Got mstr-report elements"); // DEBUG
 
// Iterate the report
for (int i = 0; i < nodes.getLength(); i++) {
Element element = (Element) nodes.item(i);
 
NodeList name = element.getElementsByTagName("report-name");
Element line = (Element) name.item(0);
System.out.println("Report Name: " + getCharacterDataFromElement(line));
 
NodeList title = element.getElementsByTagName("report-id");
line = (Element) title.item(0);
System.out.println("Report ID: " + getCharacterDataFromElement(line));
}
returnDoc = doc;
}
catch(Exception e){
e.printStackTrace();
}
return returnDoc;
}

4. Create the below function getCharacterDataFromElement. This function accepts an XML element as a parameter and returns the tags contents as a string. This is basically just a helper method.

private static String getCharacterDataFromElement(Element e) {
Node child = e.getFirstChild();
if (child instanceof CharacterData) {
CharacterData cd = (CharacterData) child;
return cd.getData();
}
return "?";
}

5. Create the below method GetReportXMLDocument. This is the main function that will be called by our servlet to execute the report and return an XML document object. The method takes the MicroStrategy report id as a parameter.

public Document GetReportXMLDocument(String reportID) throws TransformerException
{
// Construct report URL
String mstrURL = this.baseURL;
mstrURL += "taskId=reportDataService&taskEnv=xml&taskContentType=xml";
mstrURL += "&server=" + this.serverName;
mstrURL += "&project=" + this.projectName.replace(" ", "+");
mstrURL += "&userid=" + this.userName;
mstrURL += "&password=" + this.userPassword;
mstrURL += "&styleName=" + this.styleName;
mstrURL += "&reportID=" + reportID;
 
// Save report URL to property
this.currentReportURL = mstrURL;
 
// Execute report and get results as XML document
Document reportXMLDoc = null;
try {
// Get URL as string
URL uMstrURL = new URL(mstrURL);
String sMstrURL = uMstrURL.toString();
 
// Get XML document
reportXMLDoc = this.GetContentXML(sMstrURL.toString());
} catch (MalformedURLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
 
return reportXMLDoc;
}

Step 4 – Add code to the servlet class

  1. Add the following import statements to the top of the new servlet class.
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import org.w3c.dom.*;
import org.w3c.dom.CharacterData;

2. Add the same function getCharacterDataFromElement.

private static String getCharacterDataFromElement(Element e) {
Node child = e.getFirstChild();
if (child instanceof CharacterData) {
CharacterData cd = (CharacterData) child;
return cd.getData();
}
return "?";
}

3. Add the below function outputReportHTML. This function will accept the XML document object as a parameter and display the results in the browser as an HTML table. (I apologize the below code is an image and cannot be cut and paste but the HTML tags in the code were causing issues with the post.)
outputReportHTML Function

4. Add the below code to the servlet’s doGet method. This is the method that will be triggered by the users browser. The function uses our helper class to retrieve the report XML and then parses the XML output and displays in the browser as HTML output.

Step 6 – Add HTML code to the index.jsp page

Finally, modify the file index.jsp like the below screenshot. All we are basically adding is a header and a form with an input box for the report id and a submit button to run the report. Make sure the action attribute in the form tag contains the name of the servlet class you created in step 2. I also linked to a simple style sheet in my example below but you do not need to do this, so you can omit the link tag in the head section from your jsp page.

Step 7 – Install the MicroStrategy Custom XML plugin

a. Install the plugin located in your MicroStrategy install directory at

C:\Program Files\MicroStrategy\SDK\CustomizationPlugins\AdvancedScenarios\RetrievingReportXMLCustomFormat

b. To install this plugin extract the contents to the “Web ASPx\plugins” folder of your MicroStrategy web install which by default is located at C:\Program Files (x86)\MicroStrategy\Web ASPx\plugins

c. You may need to restart your web server for the changes to take effect. This plugin will enable us to retrieve the report results as XML.

Step 8 – Run the project

a. Run the project by right clicking the root project folder and choosing Run As -> Run on Server.
b. You may have to choose your local Tomcat v7 server as in the below screenshot but it should be selected by default. Now click Finish.

c. If the application asks you to reset your Tomcat server choose Yes.
d. Next the application should build and eventually display the contents of our index.jsp page similar to below.

e. Next enter the report id of a MicroStrategy report and click Run Report. (Note: Make sure the report id is for a grid report and not a document/dashboard) Finally, you should see results similar to below. Congratulations you have just integrated the MicroStrategy SDK into a custom Java web application!

MicroStrategy SDK – How to setup your Eclipse Java development environment

by

January 20, 2017

8:55 pm

8 Comments on MicroStrategy SDK – How to setup your Eclipse Java development environment

MicroStrategy, SDK

, , , , , , ,

When it comes to working with the MicroStrategy SDK there is a lot of resources out there, however, when it comes to getting started and setting up your devlopment environment, you may find that the instructions are often lacking and disjointed.

I find there are primarily two different buckets developers fall into when it comes to SDK development. First, there are those that want to modify the existing MicroStrategy web application and second, there are those that want to incorporate MicroStrategy into their own existing custom web applications. The main focus of this post is the latter.

When setting up my development environment using Eclipse I ran into several issues that had me pounding my fists against the desk. I scoured the internet only to find that the documentation was either limited or dated. It seems like I was fighting through hurdle after hurdle until finally I was able to get everything working.

I decided to create this post to help others that would like to get started with MicroStrategy SDK development.

So let’s jump right in and get started.

Step 1 – Install Java SDK 8 from here (Make sure you install JDK and not just JRE)

Step 2 – Download and extract the Eclipse Java IDE from here. I’m currently working with MicroStrategy 10.2 and use Eclipse Mars.

Step 3 – Open Eclipse and click Help->Install new software…

In the “work with” drop down choose “http://download.eclipse.org/releases/mars” and check the following packages to install:

  1. Eclipse Java Web Developer Tools
  2. JST Server Adapters
  3. JST Server Adapters Extensions
  4. Eclipse Java EE Developer Tools
  5. WST Server Adapters

Step 4 – Download and install Apache Tomcat from here (I use Tomcat version 7). We are installing Tomcat to your development environment but if you have experience developing Java web applications you may want to skip this step however, this is for beginners so I assume nothing.

After Tomcat is installed go to the install directory for example “C:\Program Files (x86)\Apache Software Foundation\Tomcat 7.0\conf” and edit “tomcat-users.xml” file and add users and roles. See the example screenshot below.

Step 5 – Configure the Tomcat run time with Eclipse

  1. In Eclipse click Window-Preferences->Server->Runtime Environment
  2. Click Add->Click Apache Tomcat v7.0->Click Next
  3. Click Browse… and choose C:\Program Files (x86)\Apache Software Foundation\Tomcat 7.0
  4. Choose java8 (JDK) in the JRE dropdown
  5. Click Finish then OK

Step 6 – Now for the fun part where we create a new web project and integrate with MicroStrategy SDK.

  1. In Eclipse Click File->New->Project->Web->Dynamic Web Project->Click Next
  2. Enter project name and choose runtime module version 2.5. See screenshot below for an example. (Note: Target runtime should default to Apache Tomcat v7.0)

3. Click next, then next again, then finish

4. Go to Project->Properties->Choose “Java Build Path” from left tree view

5. Click Libraries tab and click Add external jars

6. Add MicroStrategy JAR files. These files can be obtained from an existing MicroStrategy installation. In Windows they can be found in “C:\Program Files (x86)\Common Files\MicroStrategy” for example. NOTE: Make sure you ONLY add the files that are named like Web*.jar and the file JavaWebAPI.jar

7. Click Apply and OK

8. After you created the project next go to Project->Properties->Deployment Assembly and add the MicroStrategy jar files. NOTE: THIS IS REQUIRED OR THE DEPLOYED APP WILL FAIL TO FIND THE MICROSTRATEGY JAR FILES AND THE APP WILL NOT WORK!

Congratulations! You now have a MicroStrategy development environment and are ready to begin developing custom Java applications that integrate with MicroStrategy.

In Part 2 of this post I show you how to connect to MicroStrategy, run a report, retrieve the results as XML, parse the results and finally display the report results to a user from a custom Java web application. For that post I will of course be using a development environment that was setup with these very same instructions.

Python PDF Splitter – Split PDF files for free with Python

by

January 17, 2017

2:19 pm

6 Comments on Python PDF Splitter – Split PDF files for free with Python

BI Utilities

, , , , , , , , , , , , ,

If you work with a BI tool such as MicroStrategy, I’m sure at some point you have come across the need to split large PDF files / reports into smaller separate files.

I have searched far and wide and the only solutions I have found require you to purchase a tool. One such tool is PDF Splitter. It is not very expensive but if you need to split a lot of large files, it can be slow.

I have developed a script in Python that I use in a production environment and it is much faster than an application like PDF Splitter, not to mention free.

You will need to install the Python 3 interpreter which is a free download from www.python.org. You will also need the PyPDF2 package which you can download from here. To install the PyPDF2 package first extract the contents, then browse to the directory from a command prompt and run the following command: python setup.py install.

You can download the PDF Splitter Python code from my GitHub repository here.

The Python script takes 4 parameters.

  1. Source PDF File: This is the source PDF file that you want to split
  2. Output Directory: This is the output directory where the split files are placed
  3. Output Name Prefix: This is a naming prefix that will be appended to the beginning of each file. The script automatically names the files after the bookmarks. So for example if your PDF file has a bookmark labeled “EAT_AT_JOES_STORE_1” and you enter an output prefix of “TEST_RUN – “, the splitter will name this file after it has been split “TEST_RUN – EAT_AT_JOES_STORE_1.pdf“.
  4. Delete Source File: This is a True/False Boolean value that will delete the source file if set to True

I have this script running automatically in a production environment using Windows Task Scheduler. I create the task, pass the appropriate parameters and schedule it to run accordingly and it works great!

If you need help scheduling this to run automatically in Windows, here is how I did it:

  1. Create a batch file (for example Execute_Split_PDF_Reports.bat)
  2. Add the following line of code to the batch file: “C:\Program Files (x86)\Python35-32\python.exe” <Path to the Python script>\Split_PDF_Reports.py %1 %2 %3 %4 (Note: The values %1, %2, etc. are the parameters that will be passed from the Scheduled Task we will create in later steps)
  3. Replace the underlined portion of the above code with the path to where you saved the Split_PDF_Reports.py Python script. (Also, if you installed Python interpreter to anywhere other than the default directory, you may need to edit that path as well)
  4. Create a new Scheduled Task by going to Start->Control Panel->Administrative Tools->Task Scheduler
  5. Under the general tab make sure you choose the option “Run whether user is logged on or not” otherwise the task will not run unless a user is actively logged in. If this is a production server, you definitely want this option checked.
  6. Go to the Triggers tab and setup the schedule you want.
  7. Go to the Actions tab, click “New…” in the dropdown labeled “Action” choose “Start a program”. Then in the text box labeled “Program/script” click browse and choose the batch file we created in step 1. Next, add the 4 parameter values for the Python script separated by a space (Note: If your parameter values contain spaces make sure you surround them with quotes). Finally, enter the path where the batch file is located in the optional “Start in” text box. For example, if the batch file is located in the folder C:\Programs\BatchFiles then this is what you would enter in this field.
  8. Click OK and OK again in main window and your done!

Don’t forget to please leave comments below. Enjoy!