Retrieving data from a single visualization from dossier

Manager, Sales Engineering • MicroStrategy

There are cases when you want to programatically save data behind a single visualization within a dossier. This script combines functions of mstrio Python module with some MicroStrategy REST API endpoints to achieve that.

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.
Please note - I've added color formatting in the code below (http://hilite.me/ ). Code copied from here might not work due to indentation issues. In that case, use attached file with sample code. Also this CRM is adding ';' after URLs.


# ### Tools
# 1. Jupyter Notebook
# 2. Dossier in MSTR Web
# 3. Strategy Library Swagger site for REST enpoints definitions

# ### Plan
# 1. Import Librarires
# 2. Connect to MSTR using `Connection` object
# 3. Create functions
# 4. List dossiers to find ID        -> get dossier ID
# 5. Create dossier instance         -> get instance ID
# 6. Get Dossier Instance definition -> get visualization ID
# 7. Import visualization data as a CSV file
# 8. Store data in pandas data frame

import pandas as pd
import csv
from io import StringIO
from mstrio.project_objects.dossier import list_dossiers, list_dossiers_across_projects
from mstrio.connection import Connection
import getpass

mstr_username = "mstr"
mstr_password = getpass.getpass(prompt='Password ')
mstr_base_url = "https://env-240147.customer.cloud.Strategy.com"
mstr_url_api  = mstr_base_url+"/MicroStrategyLibrary/api"
mstr_project  = "Strategy Tutorial"

conn=Connection(mstr_url_api, mstr_username, mstr_password, login_mode=1, project_name=mstr_project)

# Functions
def post_dossier_instance(connection, dossier_id):
    url_add=f"/api/dossiers/{dossier_id}/instances"
    res = connection.post(url=connection.base_url+url_add)
    return res

def get_dossier_instance_def(connection, dossierId, instanceId):
    url_add=f"/api/v2/dossiers/{dossierId}/instances/{instanceId}/definition"
    res = connection.get(url=connection.base_url+url_add)
    return res

def csv_export_viz(connection, dossierId, instanceId, nodeKey):
    url_add=f"/api/documents/{dossierId}/instances/{instanceId}/visualizations/{nodeKey}/csv"
    res = connection.post(url=connection.base_url+url_add)
    return res

list_dossiers(conn)
dossier_id="6F1040F519446C7618CD4AB4D555F4F9"

resp_instance=post_dossier_instance(conn, dossier_id)
instance_id=resp_instance.json()['mid']

viz_id=get_dossier_instance_def(conn, dossier_id, instance_id).json()['chapters'][0]['pages'][0]['visualizations'][2]['key']

resp_viz = csv_export_viz(conn, dossier_id, instance_id, viz_id)
result = str(resp_viz.content, 'utf-16')
df = pd.read_csv(StringIO(result))

Comment

0 comments

Details

Example

Published:

July 11, 2022

Last Updated:

March 21, 2024

# ### Tools # 1. Jupyter Notebook # 2. Dossier in MSTR Web # 3. Strategy Library Swagger site for REST enpoints definitions # ### Plan # 1. Import Librarires # 2. Connect to MSTR using `Connection` object # 3. Create functions # 4. List dossiers to find ID -> get dossier ID # 5. Create dossier instance -> get instance ID # 6. Get Dossier Instance definition -> get visualization ID # 7. Import visualization data as a CSV file # 8. Store data in pandas data frame import pandas as pd import csv from io import StringIO from mstrio.project_objects.dossier import list_dossiers, list_dossiers_across_projects from mstrio.connection import Connection import getpass mstr_username = "mstr" mstr_password = getpass.getpass(prompt='Password ') mstr_base_url = "https://env-240147.customer.cloud.Strategy.com" mstr_url_api = mstr_base_url+"/MicroStrategyLibrary/api" mstr_project = "Strategy Tutorial" conn=Connection(mstr_url_api, mstr_username, mstr_password, login_mode=1, project_name=mstr_project) # Functions def post_dossier_instance(connection, dossier_id): url_add=f"/api/dossiers/{dossier_id}/instances" res = connection.post(url=connection.base_url+url_add) return res def get_dossier_instance_def(connection, dossierId, instanceId): url_add=f"/api/v2/dossiers/{dossierId}/instances/{instanceId}/definition" res = connection.get(url=connection.base_url+url_add) return res def csv_export_viz(connection, dossierId, instanceId, nodeKey): url_add=f"/api/documents/{dossierId}/instances/{instanceId}/visualizations/{nodeKey}/csv" res = connection.post(url=connection.base_url+url_add) return res list_dossiers(conn) dossier_id="6F1040F519446C7618CD4AB4D555F4F9" resp_instance=post_dossier_instance(conn, dossier_id) instance_id=resp_instance.json()['mid'] viz_id=get_dossier_instance_def(conn, dossier_id, instance_id).json()['chapters'][0]['pages'][0]['visualizations'][2]['key'] resp_viz = csv_export_viz(conn, dossier_id, instance_id, viz_id) result = str(resp_viz.content, 'utf-16') df = pd.read_csv(StringIO(result))