Subscribing a MicroStrategy Report to be delivered to Content Manager

As users work with MicroStrategy they will create records, right?  In this post I'll detail one approach for routinely getting those records into Content Manager with no user effort....

First create a new file transmitter named Content Manager:

 
2018-05-25_21-05-36.png
 
 
 

Then create a new Content Manager device:

 
Select the new Content Manager transmitter type

Select the new Content Manager transmitter type

 
 
Note the File Location is dynamic, based on the User's ID

Note the File Location is dynamic, based on the User's ID

 

Then update the user so that they have an address on the new device:

 
Note that user addresses can be automatically attached via a command manager script

Note that user addresses can be automatically attached via a command manager script

 

Also need to ensure that the user can subscribe to a file (Content Manager is of type File):

 
 

Now the user can subscribe a report to a file:

 
2018-05-25_21-31-07.png
 

This one will be scheduled to run every day at 5am:

 
2018-05-26_0-20-25.png
 

When the schedule runs the PDF will be placed within a sub-folder unique for this user:

The Recipient ID in the folder name doesn't really help me.  The script will need to know the login of the user so that it can be registered within CM on their behalf.  I can lookup the login by using the command manager, as shown below.

If I execute the command manager from within powershell I won't get a nice table.  Instead I'll get some really ugly output:

2018-05-26_8-44-30.png

I don't want the results file to include everyone.  I just need those who have addresses pointing to my Content Manager staging devices.  But I also want to know the name of the report and have it formatted to be more easily worked-with inside powershell.  I'll have to use a command manager procedure....

DisplayPropertyEnum iProperty = DisplayPropertyEnum.EXPRESSION;
ResultSet oUserProperties = executeCapture("LIST ALL PROPERTIES FOR USERS IN GROUP 'EVERYONE';");
ResultSet oPropertySet = null;
oUserProperties.moveFirst();
while (!oUserProperties.isEof()) {
    String sUserLogin = oUserProperties.getFieldValueString(DisplayPropertyEnum.LOGIN);
    String sID = oUserProperties.getFieldValueString(DisplayPropertyEnum.ID);
    String sUserName = oUserProperties.getFieldValueString(DisplayPropertyEnum.FULL_NAME);
    ResultSet oUserAddresses = (ResultSet)oUserProperties.getFieldValue(DisplayPropertyEnum.DS_ADDRESSES_RESULTSET);
    oUserAddresses.moveFirst();
    while (!oUserAddresses.isEof()) {
        String sAddressName = oUserAddresses.getFieldValueString(DisplayPropertyEnum.DS_ADDRESS_NAME);
        if (sAddressName.contains("Content Manager")) {
            ResultSet oProjects = executeCapture("LIST ALL PROJECTS;");
            oProjects.moveFirst();
            while (!oProjects.isEof()) {
                String sProjectName = oProjects.getFieldValueString(DisplayPropertyEnum.NAME);
                ResultSet oSubscriptions = executeCapture("LIST ALL SUBSCRIPTIONS FOR RECIPIENTS USER '" + sUserName + "' FOR PROJECT '" + sProjectName + "';");
                oSubscriptions.moveFirst();
                while (!oSubscriptions.isEof()) {
                    String sContent = oSubscriptions.getFieldValueString(DisplayPropertyEnum.CONTENT);
                    String sSubscriptionType = oSubscriptions.getFieldValueString(DisplayPropertyEnum.SUBSCRIPTION_TYPE);
                    if (sSubscriptionType.contains("File")) {
                        ResultSet oRecipientList = (ResultSet)oSubscriptions.getFieldValue(RECIPIENT_RESULTSET);
                        oRecipientList.moveFirst();
                        while (!oRecipientList.isEof()) {
                            String sRecipientAddress = oRecipientList.getFieldValueString(RECIPIENT_ADDRESS);
                            if (sRecipientAddress.contains(sAddressName)) {
                                printOut("||" + sID + ",\"" + sUserLogin + "\",\"" + sContent+"\"");
                            }
                            oRecipientList.moveNext();
                        }
                    }
                    oSubscriptions.moveNext();
                }
                oProjects.moveNext();
            }
        }
        oUserAddresses.moveNext();
    }
    oUserProperties.moveNext();
}

Executing this yields the following content within the log file:

2018-05-26_8-05-45.png

Note that I included two pipe characters in my log file, so that I can later find & parse my results.  In powershell I'll invoke the procedure via the command manager, redirect the output to a file, load the file, find the lines with the double pipes, extract the data, and convert it from CSV. 

function Get-MstrCMUserSubscriptions {
    $command = 'EXECUTE PROCEDURE ListContentManagerUserSubscriptions();'
    $outFile = New-TemporaryFile
    $logFile = New-TemporaryFile
    $userSubscriptions = @("ID,Name,Report")
    try {
        Add-Content -Path $outFile $command
        $cmdmgrCommand = "cmdmgr -n `"$psn`" -u `"$psnUser`" -f `"$outFile`" -o `"$logFile`""
        iex $cmdmgrCommand$results += $_ }}
        $results = Get-Content -Path $logFile | Where-Object { $_ -like "*||*" }
        foreach ( $result in $results ) {
            $userSubscriptions += ($result.split('|')[2])
        }
    } catch {
    }
    if ( (Test-Path $outFile) ) { Remove-Item -Path $outFile -Force }
    if ( (Test-Path $logFile) ) { Remove-Item -Path $logFile -Force }
    return $userSubscriptions | ConvertFrom-CSV
}

That effort yields an array I can work with...

2018-05-26_8-28-54.png

Last step, iterate the array and look for reports in the staging area.  As I find one, I submit it to Content Manager via the Service API and remove it from disk.  

$userSubscriptions = Get-MstrCMUserSubscriptions
foreach ( $userSubscription in $userSubscriptions ) {
    $stagingPath = ("$stagingRoot\\$($userSubscription.ID)")
    $reports = Get-ChildItem $stagingPath -Filter "*$($userSubscription.Report)*"
    foreach ( $report in $reports ) {
        New-CMRecord -UserLogin $userSubscription.Name -Report $userSubscription.Report -File $report.Name
        Remove-Item $report.Name -Force
    }
}

My New-CMRecord function includes logic that locates an appropriate folder for the report.  Yours could attach a schedule, classification, or other meta-data fetched from Microstrategy.  

Enriching Record Metadata via the Google Vision API

Many times the title of the uploaded file doesn't convey any real information.  We often ask users to supply additional terms, but we can also use machine learning models to automatically tag records.  This enhances the user's experience and provides more opportunities for search.  

faulkner.jpg
Automatically generated keywords, provided by the Vision API

Automatically generated keywords, provided by the Vision API

In the rest of the post I'll show how to build this plugin and integrate it with the Google Vision Api...


First things first, I created a solution within Visual Studio that contains one class library.  The library contains one class named Addin, which is derived from the TrimEventProcessorAddIn base class.  This is the minimum needed to be considered an "Event Processor Addin".

using HP.HPTRIM.SDK;
 
namespace CMRamble.EventProcessor.VisionApi
{
    public class Addin : TrimEventProcessorAddIn
    {
        public override void ProcessEvent(Database db, TrimEvent evt)
        {
        }
    }
}

Next I'll add a class library project with a skeleton method named AttachVisionLabelsAsTerms.  This method will be invoked by the Event Processor and will result in keywords being attached for a given record.  To do so it will call upon the Google Vision Api.  The event processor itself doesn't know anything about the Google Vision Api.

using HP.HPTRIM.SDK;
 
namespace CMRamble.VisionApi
{
    public static class RecordController
    {
        public static void AttachVisionLabelsAsTerms(Record rec)
        {
 
        }
    }
}

Before I can work with the Google Vision Api, I have to import the namespace via the NuGet package manager.

The online documentation provides this sample code that invokes the Api:

var image = Image.FromFile(filePath);
var client = ImageAnnotatorClient.Create();
var response = client.DetectLabels(image);
foreach (var annotation in response)
{
    if (annotation.Description != null)
        Console.WriteLine(annotation.Description);
}

I'll drop this into a new static method in my VisionApi class library.  To re-use the sample code I'll need to pass the file path into the method call and then return a list of labels.  I'll mark the method private so that it can't be directly called from the Event Processor Addin.

private static List<string> InvokeDetectLabels(string filePath)
{
    List<string> labels = new List<string>();
    var image = Image.FromFile(filePath);
    var client = ImageAnnotatorClient.Create();
    var response = client.DetectLabels(image);
    foreach (var annotation in response)
    {
        if (annotation.Description != null)
            labels.Add(annotation.Description);
    }
    return labels;
}

Now I can go back to my record controller and build-out the logic.  I'll need to extract the record to disk, invoke the new InvokeDetectLabels method, and work with the results.  Ultimately I should include error handling and logging, but for now this is sufficient.

public static void AttachVisionLabelsAsTerms(Record rec)
{
    // formulate local path names
    string fileName = $"{rec.Uri}.{rec.Extension}";
    string fileDirectory = $"{System.IO.Path.GetTempPath()}\\visionApi";
    string filePath = $"{fileDirectory}\\{fileName}";
    // create storage location on disk
    if (!System.IO.Directory.Exists(fileDirectory)) System.IO.Directory.CreateDirectory(fileDirectory);
    // extract the file
    if (!System.IO.File.Exists(filePath) ) rec.GetDocument(filePath, false"GoogleVisionApi", filePath);
    // get the labels
    List<string> labels = InvokeDetectLabels(filePath);
    // process the labels
    foreachvar label in labels )
    {
        AttachTerm(rec, label);
    }
    // clean-up my mess
    if (System.IO.File.Exists(filePath)) try { System.IO.File.Delete(filePath); } catch ( Exception ex ) { }
}

I'll also need to create a new method named "AttachTerm".  This method will take the label provided by google and attach a keyword (thesaurus term) for each.  If the term does not yet exist then it will create it.

private static void AttachTerm(Record rec, string label)
{
    // if record does not already contain keyword
    if ( !rec.Keywords.Contains(label) )
    {
        // fetch the keyword
        Keyword keyword = null;
        try { keyword = new HP.HPTRIM.SDK.Keyword(rec.Database, label); } catch ( Exception ex ) { }
        if (keyword == null)
        {
            // when it doesn't exist, create it
            keyword = new Keyword(rec.Database);
            keyword.Name = label;
            keyword.Save();
        }
        // attach it
        rec.AttachKeyword(keyword);
        rec.Save();
    }
}

Almost there!  Last step is to go back to the event processor add in and update it to use the record controller.  I'll also need to ensure I'm only calling the Vision API for supported image types and in certain circumstances.  After making those changes I'm left with the code shown below.

using System;
using HP.HPTRIM.SDK;
using CMRamble.VisionApi;
 
namespace CMRamble.EventProcessor.VisionApi
{
    public class Addin : TrimEventProcessorAddIn
    {
        public const string supportedExtensions = "png,jpg,jpeg,bmp";
        public override void ProcessEvent(Database db, TrimEvent evt)
        {
            switch (evt.EventType)
            {
                case Events.DocAttached:
                case Events.DocReplaced:
                    if ( evt.RelatedObjectType == BaseObjectTypes.Record )
                    {
                        InvokeVisionApi(new Record(db, evt.RelatedObjectUri));
                    }
                    break;
                default:
                    break;
            }
        }
 
        private void InvokeVisionApi(Record record)
        {
            if ( supportedExtensions.Contains(record.Extension.ToLower()) )
            {
                RecordController.AttachVisionLabelsAsTerms(record);
            }
        }
    }
}

Next I copied the compiled solution onto the workgroup server and registered the add-in via the Enterprise Studio. 

2018-05-19_7-57-09.png

 

Before I can test it though, I'll need to create a service account within google.  Once created I'll download the API key as a json file and place it onto the server.

The API requires that the path to the json file be referenced within an environment variable.  The file can be placed anywhere on the server that is accessible by the CM service account.  This is done within the system properties contained in the control panel.

2018-05-19_7-51-45.png

Woot woot!  I'm ready to test.  I should now be able to drop an image into the system and see some results!  I'll use the same image as provided within the documentation, so that I can ensure similar results.  

2018-05-19_8-16-19.png

Sweet!  Now I don't need to make users pick terms.... let the cloud do it for me!

Stress Testing GCP CM via ServiceAPI and jmeter

Let's see how my GCP hosted CM infrastructure holds up under ridiculous load.  

jmeter can be downloaded here.  Since this is a pure Java application you'll need to have java installed (I'm using JRE 9 but most should use JDK 8 here).  Note that JDK v10 is not yet officially supported.

After extracting jmeter you launch it by executing jmeter.bat...

2018-05-12_6-39-04.png

Every test plan should have a name, description, and a few core variables...

There are many, many possible ways to build out a test plan.  Each will have at least one thread group though, so I'll start by creating just one.  

2018-05-12_6-49-13.png

As shown below, I created a thread group devoted to creating folders.  I also set error action to stop test.  Later I'll come back and increase the thread properties, but for now I only want one thread so that I can finish the configuration.

Next I'll add an HTTP Request Sampler, as shown below...

2018-05-12_7-37-48.png

The sampler is configured to submit a Json record definition, with values for Title and Record Type.  This is posted to the ServiceAPI end-point for records.  It's as basic as you can get!

I'll also need an HTTP Header Manager though, so that the ServiceAPI understands I'm intending to work with json data.

2018-05-12_7-34-29.png

Last, I'll add a View Results Tree Listener, like so...

2018-05-12_7-40-52.png

Now I can run the test plan and review the results...

2018-05-12_7-46-35.png

After fixing the issue with my json (I used "RecordRecordTitle" instead of "RecordTypedTitle"), I can run it again to see success.

2018-05-12_7-48-46.png

Now I'll disable the view results tree, add a few report listeners, and ramp up the number of threads to 5.  Each thread will submit 5 new folders.  With that I can see the average throughput.

2018-05-12_7-51-36.png

Next I'll increase the number of folders, tweak the ramp-up period, and delay thread creation until needed.  Then I run it and can review a the larger sample set.

2018-05-12_7-54-24.png

This is anecdotal though.  I need to also monitor all of the resources within the stack.  For instance, as shown below, I can see the impact of that minor load on the CPU of the workgroup server.  I'd want to also monitor the workgroup server(s) RAM, disk IO, network IO, and event logs.  Same goes for the database server and any components of the stack.

2018-05-12_7-58-44.png

From here the stress test plan could include logic to also create documents within the folders, run complex searches, or perform actions.  There are many other considerations before running the test plan, such as: running the plan via command line instead of GUI, scheduling the plan across multiple workstations, calculating appropriate thread count/ramp-up period based on infrastructure, and chaining multiple HTTP requests to more aptly reflect end-user actions.

For fun though I'm going to create 25,000 folders and see what happens!

2018-05-12_8-32-43.png