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.  

Ensuring Records Managers can access Microstragegy

I've got a MicroStragtegy server environment with a Records Management group.  I'd like to ensure that certain new CM users always have access to MSTR, so that they have appropriate access to dashboards and reports.  For simplicity of the post I'll focus just on CM administrators.

Within MicroStrategy I'd like to create a new user and include them in a "Records Management" group, like so:

2018-05-05_8-48-44.png

This implementation of MSTR does not have an instance of the REST API available, so I'm limited to using the command manager.  My CM instance is in the cloud and I won't be allowed to install the CM client on the MSTR server.  To bridge that divide I'll use a powershell script that leverages the ServiceAPI and Invoke-Expression cmdlet.  

First I need a function that gets me a list of CM users:

function Get-CMAdministrators {
    Param($baseServiceApiUri)
    $queryUri = ($baseServiceApiUri + "/Location?q=userType:administrator&pageSize=100000&properties=LocationLogsInAs,LocationLoginExpires,LocationEmailAddress,LocationGivenNames,LocationSurname")
    $AllProtocols = [System.Net.SecurityProtocolType]'Ssl3,Tls,Tls11,Tls12'
    [System.Net.ServicePointManager]::SecurityProtocol = $AllProtocols
    $headers = @{ 
        Accept = "application/json"
    }
    $response = Invoke-RestMethod -Uri $queryUri -Method Get -Headers $headers -ContentType "application/json"
    Write-Debug $response
    if ( $response.TotalResults -gt 0 ) {
        return $response.Results
    }
    return $null
}

Second I need a function that creates a user within MSTR:

function New-MstrUser {
    Param($psn, $psnUser, $psnPwd, $userName,$fullName,$password,$group)
    $command = "CREATE USER `"$userName`" FULLNAME `"$fullName`" PASSWORD `"$password`" ALLOWCHANGEPWD TRUE CHANGEPWD TRUE IN GROUP `"$group`";"
    $outFile = ""
    $logFile = ""
    try {
        $outFile = New-TemporaryFile
        Add-Content -Path $outFile $command
        $logFile = New-TemporaryFile
        $cmdmgrCommand = "cmdmgr -n `"$psn`" -u `"$psnUser`" -f `"$outFile`" -o `"$logFile`""
        iex $cmdmgrCommand
    } catch {
        
    }
    if ( (Test-Path $outFile) ) { Remove-Item $outFile -Force }
    if ( (Test-Path $logFile) ) { Remove-Item $logFile -Force }
}

Last step is to tie them together:

$psn = "MicroStrategy Analytics Modules"
$psnUser = "Administrator"
$psnPwd = ""
$recordsManagemenetGroupName = "Records Management"
$baseUri = "http://10.0.0.1/HPECMServiceAPI"
$administrators = Get-CMAdministrators -baseServiceApiUri $baseUri
if ( $administrators -ne $null ) {
    foreach ( $admin in $administrators ) {
        New-MstrUser -psn $psn -psnUser $psnUser -psnPwd $psnPwd -userName $admin.LocationLogsInAs.Value -fullName ($admin.LocationSurName.Value + ', ' + $admin.LocationGivenNames.Value) -password $admin.LocationLogsInAs.Value -group $recordsManagemenetGroupName
    } 
}

After running it, my users are created!

2018-05-05_10-03-31.png

Fiscal Calendar Fun

A collection of SQL statements to be used as logical tables in support of fiscal calendar transformations with dates stored as integers.  Will need these to be able to report metrics out of CM based on an internal fiscal calendar.  


Current Fiscal Day, Month, and Year with Start/End & Yesterday

SELECT 
	fiscal_month_year_nr, 
	fiscal_year_nr, 
	fiscal_month_nr, 
	fiscal_month_year_bgn, 
	fiscal_month_year_end, 
	fiscal_year_bgn, 
	fiscal_year_end, 
	cast(convert(varchar(8),dateadd(d,-1,getdate()),112) as int) yesterday 
FROM Dim_Fiscal_Month_Year 
WHERE 
	fiscal_month_year_bgn <= cast(convert(varchar(8),dateadd(d,-1,getdate()),112) as int) 
	AND 
	fiscal_month_year_end >= cast(convert(varchar(8),dateadd(d,-1,getdate()),112) as int)

Fiscal Week to Date

select d.[date_id], wtd.[Date_id] wtd_fiscal_date_id
from [Dim_Date] dd
join [Dim_Fiscal_week_Year] fwy
	on (dd.[Fiscal_week_year_nr] = fwy.[fiscal_week_year_nr])
join [Dim_Date] wtd
	on (wtd.Date_id between fwy.[fiscal_week_year_bgn] and dd.[date_id])

Fiscal Month to Date

select dd.[date_id], mtd.[Date_id] mtd_date_id
from [Dim_Date] dd
join [Dim_Fiscal_Month_Year] fmy
	on (dd.[Fiscal_month_year_nr] = fmy.[fiscal_month_year_nr])
join [Dim_Date] mtd
	on (mtd.Date_id between fmy.[fiscal_month_year_bgn] and dd.[date_id])

Fiscal Quarter to Date

select dd.[date_id], qtd.[Date_id] qtd_date_id
from [Dim_Date] dd
join [Dim_Fiscal_Quarter_Year] fqy
    on (dd.[Fiscal_quarter_year_nr] = fqy.[fiscal_quarter_year_nr])
join [Dim_Date] qtd
	on (qtd.Date_id between fqy.[fiscal_quarter_year_bgn] and dd.[date_id])

Fiscal Year to Date

select dd.[date_id], ytd.[Date_id] ytd_date_id
from [Dim_Date] dd
join [Dim_Fiscal_Year] fy
	on (dd.[Fiscal_year_nr] = fy.[fiscal_year_nr])
join [Dim_Date] ytd
	on (ytd.Date_id between fy.[fiscal_year_bgn] and dd.[date_id])

Prior Fiscal Year Day

The trick here is to calculate the number of days since the start of the year (for the given date) and then add that number to the start date of the prior fiscal year.

select d.[Date_id], pyd.Date_id pydate_id 
from [Dim_Date] d 
join [Dim_Fiscal_Year] fy 
	on d.Fiscal_year_nr = fy.fiscal_year_nr 
join [Dim_Fiscal_Year] py 
	on (py.fiscal_year_ix = (fy.fiscal_year_ix-1)) 
join [Dim_Date] pyd 
	on (pyd.Calendar_dt = DATEADD(D,Datediff(D,convert(date,CONVERT(varchar(10),fy.fiscal_year_bgn,101)), d.Calendar_dt),convert(date,CONVERT(varchar(10),py.fiscal_year_bgn,101))))
where fy.fiscal_year_bgn not in (-1,0)