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:

Then create a new Content Manager device:

Select the new Content Manager transmitter type

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

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:

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

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:

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:

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...

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.  

Pointing Microstrategy at the Ontario Energy Board

If you read yesterday's post then you know it's possible to point Microstrategy at Content Manager via the ServiceAPI.  It's an easy win for an initial effort.  I was using data from the ridiculously unusable demonstration database.  What's the point in shipping a demonstration database that lacks any real data?

I turned my sites northward, to the frozen land we colloquially call our "attic" (see: Canada).  There I found the Ontario Energy Board (OEB), which makes available lots of data via the ServiceAPI.  So I pointed Microstrategy there to see what can be accomplished.

Here's a sequences sunburst, which quickly breaks-out applicants by volume of submissions...

Then I created a data grid with a selector for the applicant, which provides a quick view of submitted documents.  Changing the applicant changes the contents of the grid...

Then I moved onto trying to create a visualization based on the record dates.  Three dates are exposed: Date Issued, Date Received, and Date Modified.  I'd like to show a timeline for each applicant and the types of documents they are submitting.  I decide to leverage date received & date issued in the google timeline visualization shown below.

Yay!  Some fun visuals!