MicroStrategy REST APIs change slightly with new versions of Strategy Platform. This example contains update to previous articles about Python examples for version 10.8 and 10.9.
Changes in this example as compared to v. 10.9:
It's worth mentioning that the process of pushing data to MSTR cubes can be greatly simplified by using mstrio library (GitHub). mstrio provides a high-level Python interface that's designed to give data scientists and developers access to Strategy data using the MicroStrategy REST API. mstrio has a simple workflow to create and manage connections, fetch data from cubes and reports, create new cubes, and modify existing cubes. The mstrio packages are supported in Strategy 10.10 and later. In most cases it's advised to use mstrio library. This example is aimed to help people that for some reason cannot use mstrio, want to learn the basics of Python APIs or want to learn working examples of JSON syntax used for pushing data.
Attached is a Python script that will help you to understand how to push data to cubes in 11.1. The program allows you to :
1 - Generate and Display Auth Token
3 - Create a Cube
4 - Reset the Cube (push sample data)
Note - This example covers APIs needed to push data to single table Intelligent Cubes. A separate example will cover pushing data to cubes with multiple tables (a new feature in Strategy 11).
Note - After selecting option (3) the program will display an Cube Id parameter. You must save it back to the script in Parameters section before you use option (4)
Note - The Dataset API, by default, will create and publish the cube in 'My Reports' folder of the user who invoked the API call. Optionally you can provide destination folder ID.
Note - 'request' is a Python library that you need to install in your Python environment (pip install requests)
# Strategy REST API - Single Table Dataset Creation and Update
# POST/datasets (Create a single-table dataset)
# PATCH /datasets/{datasetId}/tables/{tableId} (Update data in a single-table dataset)
# Tested with MSTR 11.1
# Robert Prochowicz / 2019-01-04
import requests
import base64
### Parameters ###
api_login = 'XXXX'
api_password = 'XXXX'
project_id = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXX'
base_url = 'http://YOURSERVER_ADDRESS/StrategyLibrary/api/'
table_name = 'Speed_Car_Table' #since version 10.11 you use table name, not table ID
cube_structure_json = ('{"name":"SpeedCarCube",'
'"tables":[{"data":"Ww0KCSAgeyJTQUxFUyI6MTIzNDUuMjIyfQ0KCSAgXQ==","name":"Speed_Car_Table","columnHeaders":[{"name":"Timestamp","dataType":"DATETIME"},{"name":"Id_Run","dataType":"STRING"},{"name":"Speed","dataType":"DOUBLE"}]}],'
'"metrics":[{"name":"Speed","dataType":"DOUBLE","expressions":[{"formula":"Speed_Car_Table.Speed"}]}],'
'"attributes":[{"name":"Timestamp","attributeForms":[{"category":"ID","expressions":[{"formula":"Speed_Car_Table.Timestamp"}],"dataType":"STRING"}]},{"name":"Id_Run","attributeForms":[{"category":"ID","expressions":[{"formula":"Speed_Car_Table.Id_Run"}],"dataType":"STRING"}]}]'
',"folderId": "B0EE6B824CBA6F66C9306282E70CD0D6"' #if you ommit this, the cube will be saved to default 'My Reports' folder
'}')
sample_data_json = '[{"Timestamp": "10/5/2017 7:28:10 PM","Id_Run": "RESET","Speed": 121},{"Timestamp": "10/5/2017 8:28:20 PM","Id_Run": "RESET","Speed": 201}]'
sample_encoded = base64.b64encode(bytes(sample_data_json, 'utf-8')).decode('ascii')
insert_data_json = ('{"name":"Speed_Car_Table","columnHeaders":['
'{"name": "Timestamp","dataType": "DATETIME"},'
'{"name": "Id_Run","dataType": "STRING"},'
'{"name": "Speed","dataType": "DOUBLE"}'
'],"data":"'+sample_encoded+'"}'
)
datasetId = "XXXXXXXXXXXXXXXXXXXXXXXXXXXX" # Cube ID; remember to copy& paste this parameter from results of step 3
#### Get token ###
def login(base_url,api_login,api_password):
print("Getting token...")
data_get = {'username': api_login,
'password': api_password,
'loginMode': 1}
r = requests.post(base_url + 'auth/login', data=data_get)
if r.ok:
authToken = r.headers['X-MSTR-AuthToken']
cookies = dict(r.cookies)
print("Token: " + authToken)
return authToken, cookies
else:
print("HTTP %i - %s, Message %s" % (r.status_code, r.reason, r.text))
def create_cube(base_url, auth_token, cookies, project_id, cube_structure_json):
headers_cc = {'X-MSTR-AuthToken': auth_token,
'Content-Type': 'application/json',#IMPORTANT!
'Accept': 'application/json',
'X-MSTR-ProjectID': project_id}
print("Creating new cube...")
r = requests.post(base_url + "datasets", headers=headers_cc, data=cube_structure_json, cookies=cookies)
if r.ok:
print("Error: " + str(r.raise_for_status()) + " || HTTP Status Code: " + str(r.status_code))
print("\nCube CREATED successfully")
print("Cube ID: " + r.json()['datasetId'])
print("\nRemember to copy and note down Cube ID. Enter this value in the Python script 'Parameters' section")
else:
print("HTTP %i - %s, Message %s" % (r.status_code, r.reason, r.text))
def push_data(updatePolicy, insert_data_json, auth_token, cookies, project_id, datasetId, table_name):
#pushed_data = base64.b64encode(bytes(pushed_data_json, 'utf-8')).decode('ascii')
push_url = base_url + "datasets/" + datasetId + "/tables/" + table_name
headers_push = {'X-MSTR-AuthToken': auth_token,
'Content-Type': 'application/json',#IMPORTANT!
'X-MSTR-ProjectID': project_id,
'updatePolicy': updatePolicy
}
print("\nPushing data...")
r = requests.patch(push_url, headers=headers_push, data=insert_data_json, cookies=cookies)
if r.ok:
print("Pushed successfully...")
print("Error: " + str(r.raise_for_status()) + " || HTTP Status Code: " + str(r.status_code))
else:
print("HTTP %i - %s, Message %s" % (r.status_code, r.reason, r.text))
def main():
authToken, cookies = login(base_url,api_login,api_password)
choice = None
while choice != "0":
print \
("""
---MENU---
0 - Exit
1 - Generate and Print Token
3 - Create a Cube
4 - Reset the Cube (push sample data)
""")
choice = input("Your choice: ") # What To Do ???
print()
if choice == "0":
print("Good bye!")
elif choice == "1":
authToken, cookies = login(base_url,api_login,api_password)
elif choice == "3":
create_cube(base_url, authToken, cookies, project_id, cube_structure_json)
elif choice == "4":
print("Reseting Cube")
push_data('Replace', insert_data_json, authToken, cookies, project_id, datasetId, table_name)
else:
print(" ### Wrong option ### ")
### Main program
main()
##########
### JSON samples for Swagger site
# POST/datasets (Create a single-table dataset)
'''
{"name":"SpeedCarCube","tables":[{"data":"Ww0KCSAgeyJTQUxFUyI6MTIzNDUuMjIyfQ0KCSAgXQ==","name":"Speed_Car_Table","columnHeaders":[{"name":"Timestamp","dataType":"DATETIME"},{"name":"Id_Run","dataType":"STRING"},{"name":"Speed","dataType":"DOUBLE"}]}],"metrics":[{"name":"Speed","dataType":"DOUBLE","expressions":[{"formula":"Speed_Car_Table.Speed"}]}],"attributes":[{"name":"Timestamp","attributeForms":[{"category":"ID","expressions":[{"formula":"Speed_Car_Table.Timestamp"}],"dataType":"STRING"}]},{"name":"Id_Run","attributeForms":[{"category":"ID","expressions":[{"formula":"Speed_Car_Table.Id_Run"}],"dataType":"STRING"}]}]}
'''
# PATCH /datasets/{datasetId}/tables/{tableId} (Update data in a single-table dataset)
'''
{"name":"Speed_Car_Table","columnHeaders":[{"name":"Timestamp","dataType":"DATETIME"},{"name":"Id_Run","dataType":"STRING"},{"name":"Speed","dataType":"DOUBLE"}],"data":"W3siVGltZXN0YW1wIjogIjEwLzUvMjAxNyA3OjI4OjEwIFBNIiwiSWRfUnVuIjogIlJFU0VUIiwiU3BlZWQiOiAxMjN9LHsiVGltZXN0YW1wIjogIjEwLzUvMjAxNyA4OjI4OjIwIFBNIiwiSWRfUnVuIjogIlJFU0VUIiwiU3BlZWQiOiAyMDF9XQ=="}
'''
### Update Policy
'''
Add - Adds new data if it does not exist in the current dataset table. Does not replace data if it already exists in the current dataset table.
Update - Replaces data if it does exist in the current dataset table. Does not add new data if it does not exist in the current dataset table.
Upsert - Adds new data if it does not exist in the current dataset table. Replaces data if it does exist in the current dataset table.
Replace - Replaces all the existing data in the dataset table with new imported external data.
'''