Add data from Widget directly into Elasticube

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!

I am using the Rest API v0.9 reference. The following is the direct link to the POST method I'm using: https://sisense.dev/guides/restApi/v0/?platform=linux&spec=L2023.6#/query-rest-controller/executeSqlUsingPOST

I use $.ajax(settings) with settings set up like the following:

var settings = {
"async": true,
"crossDomain": true,
"url": "https://<server>/api/datasources/<server>/elasticubes/write-back-live-model/sql",
"method": "POST",
"headers": {
"access-control-allow-origin": "*",
"content-type": "application/json",
"authorization" : "Bearer <my-token>"
},
"data": "insert into write_back values (\"Benjamin Dass\", \"Test 1\")"
}

The error I'm getting.

benjamindass

Posted 2 years ago·Last reply 2 years ago

7 comments

RAPID BI Support

·2 years ago

Hi benjamindass , DRay ,

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.

I'd be keen to hear your thoughts.

Thanks,

Daniel

RAPID BI

daniel.ranisavljevic@rapidbi.com.au

RAPID BI - Sisense Professional Services | Implementations | Custom Add-ons

DRay

·2 years ago

That's an interesting solution! I love it when people come up with thinking-outside-the-box answers.