SUMMARY
The REST API enables you to create Strategy intelligent cubes from 3rd party applications. Documentation on the REST API is at developer.Strategy.com.
This Python 3 / Strategy 10.9 and 10.10 example shows how you can: Generate Auth Token and create an Intelligent Cube containing data. The data used for this example is from a SQL Server 2014 version of the Strategy Tutorial database. It loads up the DAY_CTR_SLS table into a list of dictionaries, which is then pushed into an Intelligent Cube.
NOTE: This has only been tested in 10.9 and 10.10.
RELATED EXAMPLES
https://community.strategy.com/article/REST-API-10-9-code-example-in-Python
https://community.strategy.com/article/JSON-Data-API-Demo-Streaming-data-from-600-IoT-devices-to-a-map-by-Robert-Prochowicz
SAMPLE - PYTHON
# JSON DATA PUSH API by Anil Gupta and Stephen Lippens
# Tested with MSTR 10.9 / 2017-12-05
import json
import datetime
import pyodbc
import requests
import base64
#Strategy Project ID
projectID = 'B7CA92F04B9FAE8D941C3E9B7E0CD754'
#connect to SQL Server database and create cursor
conn = pyodbc.connect('DSN=mileage;UID=mstr;PWD=demo')
cursor = conn.cursor()
#execute simple select statement
cursor.execute('''SELECT * from DAY_CTR_SLS''')
#read the field names
fields = [item[0] for item in cursor.description]
#create list and dictionary variables
dataDictList = []
dataDict = {}
#populate dataDictList with dictionaries, 1 per database row
while True:
row = cursor.fetchone()
if row == None:
break
dataDict[fields[0]]=row.DAY_DATE
dataDict[fields[1]]=row.CALL_CTR_ID
dataDict[fields[2]]=row.TOT_DOLLAR_SALES
dataDict[fields[3]]=row.TOT_UNIT_SALES
dataDict[fields[4]]=row.TOT_COST
dataDict[fields[5]]=row.GROSS_DOLLAR_SALES
dataDictList.append(dataDict)
dataDict = {}
#convert Python to JSON
def myconverter(o):
if isinstance(o, datetime.datetime):
return o.__str__()
j = json.dumps(dataDictList, indent=4, default = myconverter)
#base64 encode the json
encodedData = base64.b64encode(j.encode('utf-8')).decode('ascii')
#form a request, get the session
s = requests.session()
#use the session to login the user
url = 'http://localhost:8080/MicroStrategyLibrary/api/auth/login'
header = {"Content-Type": "application/json", "Accept": "application/json"}
payload = {"username": "administrator", "password": "", "loginMode": 1}
r = s.post(url, data=json.dumps(payload), headers=header)
#retrieve authtoken and cookie
authToken = r.headers.get('X-MSTR-AuthToken')
cookies = dict(r.cookies)
#build payload for creating cube. Include base64 encoded data
dataPayload = '''
{
\"name\": \"SalesDataCube\",
\"tables\":
[
{
\"name\": \"SalesTable\",
\"data\": \"''' + str(encodedData) + '''\",
\"columnHeaders\": [
{
\"name\": \"DAY_DATE\",
\"dataType\": \"DATETIME\"
},
{
\"name\": \"CALL_CTR_ID\",
\"dataType\": \"INTEGER\"
},
{
\"name\": \"TOT_DOLLAR_SALES\",
\"dataType\": \"DOUBLE\"
},
{
\"name\": \"TOT_UNIT_SALES\",
\"dataType\": \"DOUBLE\"
},
{
\"name\": \"TOT_COST\",
\"dataType\": \"DOUBLE\"
},
{
\"name\": \"GROSS_DOLLAR_SALES\",
\"dataType\": \"DOUBLE\"
}
]
}
],
\"metrics\": [
{
\"name\": \"Total_Dollar_Sales\",
\"dataType\": \"DOUBLE\",
\"expressions\": [
{
\"formula\": \"SalesTable.TOT_DOLLAR_SALES\"
}
]
},
{
\"name\": \"Unit_Sales\",
\"dataType\": \"DOUBLE\",
\"expressions\": [
{
\"formula\": \"SalesTable.TOT_UNIT_SALES\"
}
]
},
{
\"name\": \"Total_Cost\",
\"dataType\": \"DOUBLE\",
\"expressions\": [
{
\"formula\": \"SalesTable.TOT_COST\"
}
]
},
{
\"name\": \"Gross_Dollar_Sales\",
\"dataType\": \"DOUBLE\",
\"expressions\": [
{
\"formula\": \"SalesTable.GROSS_DOLLAR_SALES\"
}
]
}
],
\"attributes\": [
{
\"name\": \"DayDate\",
\"attributeForms\": [
{
\"category\": \"ID\",
\"expressions\": [
{
\"formula\": \"SalesTable.DAY_DATE\"
}
],
\"dataType\": \"STRING\"
}
]
},
{
\"name\": \"Call_Center_ID\",
\"attributeForms\": [
{
\"category\": \"ID\",
\"expressions\": [
{
\"formula\": \"SalesTable.CALL_CTR_ID\"
}
],
\"dataType\": \"INTEGER\"
}
]
}
]
}'''
#invoke service to create cube
url = 'http://localhost:8080/MicroStrategyLibrary/api/datasets'
header = {"Content-Type": "application/json", "Accept": "application/json", "X-MSTR-AuthToken": authToken, "X-MSTR-ProjectID": projectID, "updatePolicy": "add"}
r = s.post(url, headers=header, data=dataPayload, cookies = cookies)
#print http response. If it's 200, the cube creation succeeded.
print(r)
