Hi everyone, I'm trying to build a custom widget where I can get user input from a text field and directly put it into the elasticube. I've tried using the Sisense API but i can't seem to find my way around adding data into an elasticube. Any help would be greatly appreciated!
Without the ability to write directly to the EC, I recommend using the method posted above to capture the Dashboard ID and feedback and write it to your datasource, then import that into your EC.
I have a very unconventional solution for this, involving custom tables in an EC with a SQL expression that is just a growing string containing lots of unions.
The idea is that if we have a custom table in an EC, we can modify the expression to tack on an extra value to the end of the SQL by doing original expression + ' UNION ALL select ${newValueToAdd}'
Here is some Javascript that works in the browser console:
let datamodelOid = '97c7234d-179d-406e-9082-c165098f4c48'
let customDataTable = 'customData'
let newValueToAdd = 'this is some more text'
let sisenseUrl = 'https://SISENSESERVER.com' // no slash at the end
let getECSettings = {
"async": true,
"crossDomain": true,
"url": `${sisenseUrl}/api/v2/datamodels/${datamodelOid}/schema`,
"method": "GET",
"headers": {
"access-control-allow-origin": "*",
"content-type": "application/json",
// "authorization": "Bearer <my-token>"
}
}
let updateTableSettings = {
"async": true,
"crossDomain": true,
"method": "PATCH",
"headers": {
"access-control-allow-origin": "*",
"content-type": "application/json",
// "authorization": "Bearer <my-token>"
}
}
let setBuildSettings = {
"async": true,
"crossDomain": true,
"url": `${sisenseUrl}/api/v2/builds`,
"method": "POST",
"headers": {
"access-control-allow-origin": "*",
"content-type": "application/json",
// "authorization": "Bearer <my-token>"
},
"data": JSON.stringify({
"datamodelId": datamodelOid,
"buildType": "schema_changes",
"rowLimit": 0,
"schemaOrigin": "latest"
})
}
function getTableRef(elasticube, tablename) {
let datasetCount = ECDefinition.datasets.length
for (let i = 0; i < datasetCount; i++) {
let dataset = ECDefinition.datasets[i]
let tablesLength = dataset.schema.tables.length
for (let j = 0; j < tablesLength; j++) {
let table = dataset.schema.tables[j]
if (table.name === customDataTable) {
return {
tableRef: table.oid,
datasetRef: dataset.oid,
currentTable: table
}
}
}
}
}
let ECDefinition = await $.ajax(getECSettings)
let data = getTableRef(ECDefinition, customDataTable)
updateTableSettings.url = `${sisenseUrl}/api/v2/datamodels/${datamodelOid}/schema/datasets/${data.datasetRef}/tables/${data.tableRef}`,
data.currentTable.expression.expression = `${data.currentTable.expression.expression} \n UNION ALL SELECT '${newValueToAdd}'`
updateTableSettings.data = JSON.stringify(data.currentTable)
await $.ajax(updateTableSettings)
await $.ajax(setBuildSettings)
What we're doing above is getting the tableid and datasetid of the custom table from the elasticube, and then using this to update the table with our new table expression. We then tell the EC to build so that when we refresh, we can see the new data stored in the custom code table.
It's a massive hack. I wouldn't consider this a production level solution, but I saw your issue and wanted to solve for it, even if was impractical.
That endpoint is used for querying the Elasticube. You can't insert data directly into the elasticube, but you may be able to use an approach like this to write back to a database.
What is the use case here? We may be able to provide additional solution options.
Without the ability to write directly to the EC, I recommend using the method posted above to capture the Dashboard ID and feedback and write it to your datasource, then import that into your EC.