Forum Discussion

benjamindass's avatar
benjamindass
Data Storage
01-26-2024
Solved

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.

 

  • DRay's avatar
    DRay
    01-29-2024

    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.

7 Replies

  • 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

    • DRay's avatar
      DRay
      Admin

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

  • 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's avatar
      benjamindass
      Data Storage

      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's avatar
        DRay
        Admin

        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.