Transforming Data with Custom Code

Custom Code enables you to run Python code from your Jupyter Notebooks to transform and cleanse data inside your ElastiCube models as part of the build process. With Custom Code, you can:

From Sisense, your Data Administrators can access the Juptyer UI to create notebooks hosted on your Sisense server. After they have created some custom notebooks, they can attach them to the relevant ElastiCube model and define what parameters should be processed by your notebook.

If you are not familiar with Jupyter Notebooks, you can learn more here.

From the Data page, you can add custom code through the Notebook Library. The Notebook Library is the centralized location for your notebooks. It contains sample notebooks provided by Sisense as well as custom notebooks you can create to Sisense.

After you transform or cleanse your data with your notebooks, the updated tables or columns are added to your ElastiCube model.

On this page you can find the following topics:

Enabling Custom Code

By default, Custom Code is disabled. Contact your CSM to enable Custom Code for your account. In addition, your System Administrator needs to enable Custom Code in Sisense.

To enable Custom Code in Sisense:

  1. In the Admin page, select System Configuration > Feature Management.
  2. Toggle Custom Code to Enabled.
  3. Click Save. This will launch the Diagnostic pod. You should allow several minutes to finish.

Adding Notebooks

The library tiles displayed in your Notebook Library are configured according to Notebook Manifests. Each manifest holds the definitions for a single notebook including the text to display in the library, parameters to pass to the notebook, and the column definitions for the output to be added to the data model.

This section describes how to create manifest files for your notebooks and upload them.

Creating Manifest Files

Each notebook is linked to the Notebook Library by a notebook manifest. The manifest is a JSON file that contains key-value pairs that display text in the Notebook Library and defines the schema definitions for additional parameters. These additional parameters are passed to the notebook to determine the output columns of the notebook that will be transformed into a Custom Code table.

When creating a new notebook, Sisense provides a pre-built template manifest file that you can use. The template contains example values that you should replace with your own.

To create a new manifest file:

  1. From your ElastiCube model, select Custom > Custom Code. The Notebook Library is displayed.
  2. Click the Create your own notebook tile.
  3. Click Open Manifest. The manifest template is displayed. For information on how to define your key-value pairs, see the following sections. You can select File > Save to save the manifest.

Notebook Manifest Schema

Field Name

Default Value

Example Value

Description

Type

id

required

num_char

Unique notebook name (no spaces)

string

displayName

required

Number of characters

Notebook name to display in the Notebook Library in Sisense

string

description

""

Set the number of characters to truncate the string

Notebook description to display in the Notebook Library in Sisense

string

icon

“”

 

Base64 representation of an icon (leaving this blank will display a Jupyter Notebook icon)

string

codePath

required

"/work/storage_notebooks/custom_code_notebooks/ examples/notebook_template/notebook_template.ipynb"

Path to complementary notebooks. (Relative to notebook pod)

string

cellsDisable

[0]

[0]

Define the Test cell. Skip this cell when this notebook is triggered by the build process in the Data page For more information about the Test Cell, see Creating Notebooks

list of integers

additionalParameters

[]

[{ "name": "char_limit", "type": "number", "dependsOn": true, "send": true, "title": "Character Limit", "description": "Enter character limit to truncate each string" }]

Additional parameters passed to a notebook. For more information and examples, see Additional Parameters.

JSON array

columns

required

[{  "type": 18, "id": "original_column", "name": "original column"}]

Define custom code table columns (and the notebook's df_result respectively)

JSON array

language

"Python"

"Python"

Notebook language. Currently, only Python is supported

string

mode

"Full"

"Full"

Currently only Full(Replace All) is available. In the future pre and post-build modes will be supported

string

serverUrl

null

"diagnostics.sisense:8888"

 

string

timeout

0

900

Timeout in seconds

int

group

""

"My Notebooks"

Group notebooks together under Group Name, in Notebook Library

string

isActive

true

true

If True, display notebook in Notebook Library

boolean

isSystem

false

false

If True, the notebook will not be available for editing

boolean

Additional Parameters

Key

Default Value

Key is relevant for parameter type

Type

Description

type

 

all

string

Parameter type ( number / text / column / table / list / boolean)

name

 

all

string

Parameter name in the Jupyter Notebook

title

 

all

string

Parameter display name in the Notebook Library

description

""

all

string

Parameter description displayed by hover in the Notebook Library

dependsOn

true

all

boolean

When true, the Custom Code table depends on a different table according to the value assigned to this column when adding the custom code table to the data model

send

true

all

boolean

Set to true to send the parameter to the notebook as an additional parameter

isPrimary

false

column / table

boolean

When working with columns or tables, only one parameter can have this flag as true and this sets the column or table to be triggered from the Column or Table menu on the left side of the Data page.

columnType

 

column

string

Restrict the possible column types to integer, floatingPoint, text, datetime

min

 

number

int

The minimum number that can be passed as a parameter

max

 

number

int

The maximum number that can be passed as a parameter

Examples of Additional Parameters

Number

{
  "name": "char_limit",
  "type": "number",
  "send": true,
  "title": "Character Limit",
  "description": "Enter character limit to truncate each string",
  "min": 1,
  "max": 10
}

Text

{
  "name": "prefix_str",
  "type": "text",
  "send": true,
  "title": "Prefix",
  "description": "Enter a prefix string"
}

Column

{
  "name": "col_name",
  "type": "column",
  "dependsOn": true,
  "send": true,
  "title": "Choose a column",
  "description": "Choose a column to pass to the notebook",
  "columnType": "text" // optional. only if you want to restrict the data type     
}

Table

{
  "name": "table_name",
  "type": "table",
  "dependsOn": true,
  "send": true,
  "title": "Choose a table",
  "description": "Choose a table to pass to the notebook"
}

List

{
  "name": "type",
  "type": "list",
  "send": true,        
  "title": "Input type",
  "description": "Choose an input type from the list below",
  "options": [
    {
      "value": "type_1",
      "title": "Type 1",
      "isDefault": true
    },
    {
      "value": "type_2",
      "title": "Type 2"
    }
  ] 
}

Column Schema

Key

Type

Description

type

int

The column SQL DB type of the Custom Code Table in the Data page (see Column Type Codes in the section below)

id

string

The output column name in the output dataframe, df_result, in the Jupyter notebook

name

string

The column name of the Custom Code Table in the Data page

Examples of Columns

Text

{
  "type": 18,
  "id": "column_with_suffix",
  "name": "column with suffix"
}

Int

{
  "type": 8,
  "id": "original_column_length",
  "name": "original column length"
}

Column Type Codes

Full List of Column Types (according to the SQL DB Types) can be found here.

export default Object.freeze({
    BigInt: 0,
    Binary: 1,
    Bit: 2,
    Char: 3,
    DateTime: 4,
    Decimal: 5,
    Float: 6,
    Image: 7,
    Int: 8,
    Money: 9,
    NChar: 10,
    NText: 11,
    NVarChar: 12,
    Real: 13,
    UniqueIdentifier: 14,
    SmallDateTime: 15,
    SmallInt: 16,
    SmallMoney: 17,
    Text: 18,
    Timestamp: 19,
    TinyInt: 20,
    VarBinary: 21,
    VarChar: 22,
    Variant: 23,
    Xml: 25,
    Udt: 29,
    Structured: 30,
    Date: 31,
    Time: 32,
    DateTime2: 33,
    DateTimeOffset: 34
});

Uploading Manifest Files

The easiest way to create a manifest file is to open and modify the template provided by Sisense. However, you can manually upload your own manifest file by creating a JSON file with the same key-value pairs described above and uploading the manifest file to any subfolder within the following location:

storage_notebooks/custom_code_notebooks/

Example:

http://10.50.86.176:30845/app/diag/tree/work/storage_notebooks/custom_code_notebooks

Sisense automatically loads Notebook Manifests from this location. If you add or remove the manifests the changes will be reflected the next time the Notebook Library is displayed.

Creating Notebooks

Notebook files define how your data is to be transformed or cleansed. When you create a notebook in Sisense, you are redirected to a Jupyter template and a tutorial within the template written in Markdown to help you create your notebook.

Within this template is a test cell to help you test and debug your notebook. The first parameter in the test cell is additional_parameters. The value in your test cell is the same as the value of "additionalParameters": [] in the manifest file associated with your notebook. You can pull these parameters after you create your manifest file by clicking Copy Generated Data.

Paste the value of additionalParameters into the value of additional_parameters in your Test cell as shown in the example below.

Pasting the values here in your test cell is useful so you can verify that you are referring to the correct parameters later on in your code and it allows you to run tests of your notebook locally without building the ElastiCube.

Within the template you can find additional information about how to import libraries not already included, how to import data from your ElastiCube, and how to perform your transformations.

To create a notebook:

  1. From your ElastiCube model, select Custom > Custom Code. The Notebook Library is displayed.
  2. Click the Create your own notebook tile.
  3. Click Open Notebook. The notebook template is displayed.
  4. Click File > Save As to save your notebook.

Applying Custom Code to your ElastiCube Models

You can apply Custom Code on three different levels, on the column level, table level, or the ElastiCube model level. Custom Code will be enabled or disabled from the different levels according to the availability of notebooks relevant for that level. For example, Custom Code will be available on the table level when there is a notebook that has a single table in its additional parameters, as defined in the notebook manifest.

To apply Custom Code:

Column Level

  1. Hover over a column on the menu on the left side (nested under the table).
  2. Click the … menu.
  3. Click Add Custom Code. The Notebook Library is displayed.

Table Level

  1. Hover over a table on the menu on the left side (nested under the table).
  2. Click the … menu.
  3. Click Add Custom Code. The Notebook Library is displayed.

Data Model

For all Levels

  1. In the Notebook Library, select a Jupyter Notebook from the existing library.
  2. Define the additional parameters that will serve as input arguments to the notebook. The notebook will run as part of the build process