Add data from Widget directly into Elasticube

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

[email protected]

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

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.