REST API documentation can be found here.
MSTR 10.9 was updated and now uses REST Server. For that reason the required syntax has changed as compared to previous versions.
Attached is a Python script that will help you to understand how to push data to cubes in 10.9. The program allows you to :
1 - Generate and Display Auth Token
2 - Check session (if it's valid)
3 - Create a Cube
4 - Reset the Cube (push sample data)
It is made for Strategy Secure Cloud on AWS, but you can modify it easily for any other MSTR server.
Note - After selecting option (3) the program will display two important parameters: Cube Id and Table ID. You must save it back to the script in Parameters section before you use option (4)
Note - The Dataset API will create and publish the cube in 'My Reports' folder of the user who invoked the API call. You are free to move the cube anywhere else.
Note - 'request' is a Python library that you need to install in your Python environment (pip install requests)
# JSON DATA PUSH API
# Tested with MSTR 10.9 / 2017-10-16
import requests
import base64
### Parameters ###
environmentId = '12345'
api_login = 'mstr'
api_password = 'XXX'
api_iserver = '10.254.113.99'
project_id = 'B7CA92F04B9FAE8D941C3E9B7E0CD754'
api_projectname = 'Strategy Tutorial'
base_url = "https://env-" + environmentId + ".customer.cloud.Strategy.com/StrategyLibrary/api/"
cube_structure_json = '{"name":"SpeedCarCube1","tables":[{"data":"e30=","name":"Speed_Car_Table","columnHeaders":[{"name":"Timestamp","dataType":"DATETIME"},{"name":"Id_Run","dataType":"STRING"},{"name":"Id_Measure_W","dataType":"DOUBLE"},{"name":"Id_Measure","dataType":"DOUBLE"},{"name":"Driver_Id","dataType":"DOUBLE"},{"name":"Accel_X","dataType":"DOUBLE"},{"name":"Accel_Y","dataType":"DOUBLE"},{"name":"Accel_Z","dataType":"DOUBLE"},{"name":"Gyro_X","dataType":"DOUBLE"},{"name":"Gyro_Y","dataType":"DOUBLE"},{"name":"Gyro_Z","dataType":"DOUBLE"},{"name":"Humidity","dataType":"DOUBLE"},{"name":"Pitch","dataType":"DOUBLE"},{"name":"Pressure","dataType":"DOUBLE"},{"name":"Roll","dataType":"DOUBLE"},{"name":"Temp_H","dataType":"DOUBLE"},{"name":"Yaw","dataType":"DOUBLE"}]}],"metrics":[{"name":"Accel_X","dataType":"DOUBLE","expressions":[{"formula":"Speed_Car_Table.Accel_X"}]},{"name":"Accel_Y","dataType":"DOUBLE","expressions":[{"formula":"Speed_Car_Table.Accel_Y"}]},{"name":"Accel_Z","dataType":"DOUBLE","expressions":[{"formula":"Speed_Car_Table.Accel_Z"}]},{"name":"Gyro_X","dataType":"DOUBLE","expressions":[{"formula":"Speed_Car_Table.Gyro_X"}]},{"name":"Gyro_Y","dataType":"DOUBLE","expressions":[{"formula":"Speed_Car_Table.Gyro_Y"}]},{"name":"Gyro_Z","dataType":"DOUBLE","expressions":[{"formula":"Speed_Car_Table.Gyro_Z"}]},{"name":"Humidity","dataType":"DOUBLE","expressions":[{"formula":"Speed_Car_Table.Humidity"}]},{"name":"Pitch","dataType":"DOUBLE","expressions":[{"formula":"Speed_Car_Table.Pitch"}]},{"name":"Pressure","dataType":"DOUBLE","expressions":[{"formula":"Speed_Car_Table.Pressure"}]},{"name":"Roll","dataType":"DOUBLE","expressions":[{"formula":"Speed_Car_Table.Roll"}]},{"name":"Temp_H","dataType":"DOUBLE","expressions":[{"formula":"Speed_Car_Table.Temp_H"}]},{"name":"Yaw","dataType":"DOUBLE","expressions":[{"formula":"Speed_Car_Table.Yaw"}]}],"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"}]},{"name":"Id_Measure_W","attributeForms":[{"category":"ID","expressions":[{"formula":"Speed_Car_Table.Id_Measure_W"}],"dataType":"DOUBLE"}]},{"name":"Id_Measure","attributeForms":[{"category":"ID","expressions":[{"formula":"Speed_Car_Table.Id_Measure"}],"dataType":"DOUBLE"}]},{"name":"Driver_Id","attributeForms":[{"category":"ID","expressions":[{"formula":"Speed_Car_Table.Driver_Id"}],"dataType":"DATETIME"}]}]}'
datasetId = "XYZXYZXYZXYZXYZXYZXYZXYZXYZXYZXYZXYZ" # Cube ID; remember to copy& paste this parameter from step 3
tableId = "XYZXYZXYZXYZXYZXYZXYZXYZXYZXYZXYZXYZ" # remember to copy& paste this parameter from step 3 !!! IMPORTANT! For Strategy version 10.11 you should use Table name, not table ID! The name has been defined two lines above (Speed_Car_Table).
#### 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 get_sessions(base_url, auth_token, cookies):
print("Checking session...")
header_gs = {'X-MSTR-AuthToken': auth_token,
'Accept': 'application/json'}
r = requests.get(base_url + "sessions", headers=header_gs, cookies=cookies)
if r.ok:
print("Authenticated...")
print(r)
print("HTTP %i - %s, Message %s" % (r.status_code, r.reason, r.text))
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("\nCreating 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("\nCube ID: " + r.json()['datasetId'])
print("Cube Name: " + r.json()['name'])
print("Table ID: " + r.json()['tables'][0]['id'])
print("Table Name: " + r.json()['tables'][0]['name'])
print("\nRemember to copy and note down Cube ID (dataset ID) and Table ID. Enter those values in the Python script 'Parameters' section")
else:
print("HTTP %i - %s, Message %s" % (r.status_code, r.reason, r.text))
def push_data(updatePolicy, pushed_data_json, auth_token, cookies, project_id, datasetId, tableId):
pushed_data = base64.b64encode(bytes(pushed_data_json, 'utf-8')).decode('ascii')
push_url = base_url + "datasets/" + datasetId + "/tables/" + tableId
headers_push = {'X-MSTR-AuthToken': auth_token,
'Content-Type': 'application/json',#IMPORTANT!
'X-MSTR-ProjectID': project_id,
'updatePolicy': updatePolicy
}
insert_data = ('{"name":"Speed_Car_Table",'
'"columnHeaders":['
'{"name": "Timestamp","dataType": "DATETIME"},'
'{"name": "Id_Run","dataType": "STRING"},'
'{"name": "Id_Measure_W","dataType": "DOUBLE"},'
'{"name": "Id_Measure","dataType": "DOUBLE"},'
'{"name": "Driver_Id","dataType": "DOUBLE"},'
'{"name": "Accel_X","dataType": "DOUBLE"},'
'{"name": "Accel_Y","dataType": "DOUBLE"},'
'{"name": "Accel_Z","dataType": "DOUBLE"},'
'{"name": "Gyro_X","dataType": "DOUBLE"},'
'{"name": "Gyro_Y","dataType": "DOUBLE"},'
'{"name": "Gyro_Z","dataType": "DOUBLE"},'
'{"name": "Humidity","dataType": "DOUBLE"},'
'{"name": "Pitch","dataType": "DOUBLE"},'
'{"name": "Pressure","dataType": "DOUBLE"},'
'{"name": "Roll","dataType": "DOUBLE"},'
'{"name": "Temp_H","dataType": "DOUBLE"},'
'{"name": "Yaw","dataType": "DOUBLE"}'
'],"data":"' + pushed_data + '"}'
)
print("\nPushing data...")
r = requests.patch(push_url, headers=headers_push, data=insert_data, cookies=cookies)
if r.ok:
print("Pushed successfully...")
print("HTTP %i - %s, Message %s" % (r.status_code, r.reason, r.text))
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
2 - Check session
3 - Create a Cube
4 - Reset the Cube
""")
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 == "2":
get_sessions(base_url, authToken, cookies)
elif choice == "3":
create_cube(base_url, authToken, cookies, project_id, cube_structure_json)
elif choice == "4":
print("Reseting Cube")
sample_data = '[{"Timestamp": "10/5/2017 7:28:10 PM","Id_Run": "RESET","Id_Measure_W": 1,"Id_Measure": 1,"Driver_Id": 1234,"Accel_X": 0.02546,"Accel_Y": 0.09226,"Accel_Z": 0.97747,"Gyro_X": -0.0123,"Gyro_Y": 0.01709,"Gyro_Z": 0.00736,"Humidity": 38.81998,"Pitch": 358.58579,"Pressure": 980.43262,"Roll": 5.39072,"Temp_H": 34.27213,"Yaw": 67.82173},{"Timestamp": "10/5/2017 7:28:20 PM","Id_Run": "RESET","Id_Measure_W": 2,"Id_Measure": 2,"Driver_Id": 1234,"Accel_X": 0.09999,"Accel_Y": 0.09226,"Accel_Z": 0.97747,"Gyro_X": -0.0123,"Gyro_Y": 0.01709,"Gyro_Z": 0.00736,"Humidity": 38.81998,"Pitch": 358.58579,"Pressure": 980.43262,"Roll": 5.39072,"Temp_H": 34.27213,"Yaw": 67.82173}]'
push_data('Replace', sample_data, authToken, cookies, project_id, datasetId, tableId)
else:
print(" ### Wrong option ### ")
### Main program
main()
##########
### Sample JSON Syntax that creates the cube via OOTB Web Form
'''
https://env-61410.customer.cloud.Strategy.com:10443/json-data-api/#/
{"name":"cubeName3","tables":[{"data":"e30=","name":"RACE_DATA","columnHeaders":[{"name":"ID_Run","dataType":"STRING"},{"name":"gyro_z","dataType":"DOUBLE"}]}],"metrics":[{"name":"gyro_z","dataType":"number","expressions":[{"formula":"RACE_DATA.gyro_z"}]}],"attributes":[{"name":"ID_Run","attributeForms":[{"category":"ID","expressions":[{"formula":"RACE_DATA.ID_Run"}],"dataType":"string"}]}]}
'''
### 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.
'''