Microstrategy Workstation scripts to get the quick search results - mstrio Python scripts

 It is Microstrategy Workstation scripts (Python) to get the results from the search object. In the past, this is the usual thing we do in Command Manager. 

Lets see a use case to understand this better: List all reports and dashboards in a particular folder and get its details (Report name, Owner, Location, Create date, Mod date, etc). 

Usually, we create a search object in Microstrategy and save the search object in a folder. Then we run a command manager script to get the results from the search object. 

Here, we will see how to achieve that in Microstrategy Workstation Scripts (Python)

Script Logic: 

1) Import all packages

2) Establish a connection to Microstrategy project

3) Get the search results in a List object

4) #3 is a complex nested list and nested dictionaries

5) Loop through the nested list and dictionary and construct the path and get owner details

6) Move the results to a data frame

7) Remove unwanted columns from the data frame

8) Add the path and owner details that we constructed

9) Consumption 1 - Get the final result to a CSV file

10 ) Consumption 2- Publish the final result as a cube and then create a report and make it available to the end users

Note : We can save this script in our metadata and execute this in Microstrategy workstation 

Sample Script:

#Import packages

from mstrio.object_management import (

    Folder,

    full_search,

    get_my_personal_objects_contents,

    get_predefined_folder_contents,

    get_search_results,

    get_search_suggestions,

    list_folders,

    list_objects,

    Object,

    PredefinedFolders,

    quick_search,

    quick_search_from_object,

    SearchObject,

    SearchPattern,

    SearchResultsFormat,

    start_full_search

)

from mstrio.project_objects import Dossier

from mstrio.types import ObjectSubTypes, ObjectTypes

from mstrio.utils.entity import Rights

from mstrio.connection import Connection

import pandas as pd

from mstrio.project_objects.datasets.super_cube import SuperCube, list_super_cubes

#Establish a Connection to MSTR Project

base_url = "http://servername:8080/MicroStrategyLibrary/"

username = "xxx"

password = "xxx"

PROJECT_NAME = 'project name'

PROJECT_ID = 'project id'

conn = Connection(base_url, username, password,PROJECT_NAME,login_mode=1)

#Get the search object id

search_object='search object id'

#Get the serach results in a list. Add Limit=-1 otherwise, it will only return 50 rows

search_result_object = quick_search_from_object(conn, PROJECT_ID, search_object, include_ancestors=True, include_acl=False,to_dictionary=True,limit=-1)

# It returns a list of dictionaries

# Some dictionaries has a list in it

# Again those list has dictionaries in it

# Actually it is a nested dictionary and nested list

# Construct Object path and Owner details from the nested list and nested dictionary

j=0

owner_name=[]

object_path=[]

for i in search_result_object:

 k=0

 temp=''

 temp1='/'

 owner_name.append(search_result_object[j]['owner']['name'])

 

 for m in range(0,len(search_result_object[j]['ancestors'])):

   

   temp+=search_result_object[j]['ancestors'][k]['name']

   temp=temp + temp1

   k=k+1

 j=j+1

 object_path.append(temp)

 

 k=0

  

# search_result_object - Full search result in a nested list

# owner_name - Owner name in a list

# object_path - full object path in a list

# Move the search results to a Dataframe

df=pd.DataFrame(search_result_object)

#drop the unwanted columns. Depends on the requirements

df = df.drop(df.columns[[1, 3, 6,7,8,9,10,11,12,13,14]], axis=1)

#Insert the path and owner list to this dataframe

df.insert(2,'Owner',l)

df.insert(2,'path',object_path)

# to get the result in csv

 df.to_csv('E:/Shared/searchresults.csv')  

# to publish it as a cube  

cube = SuperCube(conn, name="Test")

cube.add_table(name="test", data_frame=df, update_policy="replace", to_attribute=["name", "path","Owner","description","dateCreated","dateModified"])

cube.create()