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.

DRay

·2 years ago

Hello benjamindass,

Thank you for your question.

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.

benjamindass

OP2 years ago

Hi DRay,i've looked at that solution before. It sends the data to an external db. I'm trying to put it into an elasticube on Sisense itself.

Usecase, I'm trying to gather feedback from users using the dashboard.

DRay

·2 years ago

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.

benjamindass

OP2 years ago

I see, sounds good. Thank you DRay!

DRay

·2 years ago

Glad to help!