Managing Live Dynamic Connections

Sisense supports dynamic parameters, enabling Admins and Data Admins to manage a single Live data source where the parameter values for the connection depend on the user. Dynamic parameters are resolved when the query runs.

For example, you can create one Live data model with a single dashboard, but different users - with their own parameter definitions - see only the results they have permission to view. This is useful when you have row-level or column-level security on a Live data source, and for those who want to create one Live data model and share it with customers so that each customer has access to the data they need.

Note:

The use of User Parameters is only supported for Live data models.

Example Use Cases

  • You manage your customers' data on different Snowflake or Redshift databases or schemas.
  • You control your data source workload management, such as Snowflake warehouse, to define different workloads and compute resources for your users and groups.

To create user parameters and assign values, see Personalization Parameters for Live Data Sources.

To apply User Parameters in a Live connection:

  1. Create a connection to a Live data source.

    • Select the Live data source with the user parameters showing. The user parameters are automatically entered into the connection field.
    • Select the Live data source without the user parameters showing. You can then select the user parameters manually for each connection field.
  2. On each field, click to view available parameters and their values. Select the appropriate one.

    You can also manually add parameters using special syntax:

    • Delimiters: { { } }
    • Identifier: Parameter name
    • You can combine multiple parameters in one string, and you can combine a parameter with plain text. You can also add a parameter as part of a string. For example:
      jdbc:snowflake://<connection string>/?warehouse={{warehouse}}


  3. In the Default Database field, if you have a dynamic database, add it here. For more information about dynamic databases, see below.

  4. Click Next and connect to the data source.

The data that you see in the dashboard depends on your permissions and connection settings.

All management for functionality for parameters is available in the API api/v1/user-parameters. For more information about this API, see sisense.dev.

Optional Dynamic Settings

The following are the optional dynamic settings per Live provider:

  • Athena:
    • Basic: Region, S3 Output Location, Access Key, Secret Key,
    • Advanced: JWC Connection String
    • EC2Instance: Region, S3 Output Location
  • BigQuery: Service Account and dynamic schema
  • Custom Connector: Connection string, user name, password
  • Databricks: Connection string, user name, password
  • MemSQL: Location, user name, and password
  • MS SQL/Synapse: Location, user name, password, and default database
  • MySQL: Location, user name, and password
  • Oracle: Location, user name, password, and by type (including: service ID, port, service name, TNS_ADMIN path, network alias, and URL connection string)
  • PostgreSQL: Location, user name, password, and default database
  • Redshift: Location (using full string, endpoint, port, and database), user name, password, default database and dynamic schema
  • Snowflake: Connection string (using full JDBC string, Snowflake domain, warehouse and database), user name, password and dynamic schema

Dynamic Database

Dynamic databases enable the user to be taken directly to the database defined. This means that the user doesn't have to select a database when creating a connection.

You can assign different values to users so that one Live model serves many customers who have databases with the same logical schema.

You can apply a dynamic database via the 'Default Database' field for:

  • Azure Synapse
  • MS SQL
  • MySQL
  • PostgreSQL
  • Redshift
  • Snowflake

Once the database is defined, the user is limited to a single database and cannot change databases.

Dynamic Schema Support

Dynamic Schema expects that all schemas have the same schema structure as the one used to define the data model. That is, they have the same:

  • Table / column names

  • Number of tables / columns

  • Column data types

  • Relationship definitions between tables

The Dynamic Schema functionality requires the creation of a parameter in the User Parameters area of the Sisense Admin console that specifies which schema each user or group should use.

Note:

Dynamic schema support is currently provided out of the box for the following cloud databases:

  • Athena
  • Google Big Query
  • Redshift
  • Snowflake

Using a Dynamic Schema User Parameter

To use the dynamic schema functionality, first define a dynamic schema user parameter in the User Parameters area of the Sisense admin console.

This example shows two different schemas values being defined for specific Admin and Viewer users:

Once a dynamic schema User Parameter has been defined, the user can select it in the Use Dynamic Schema option in the connection dialog.

After selecting the Use Dynamic Schema checkbox in the connection dialog, the user clicks on the {...} drop down, and then selects the user parameter for the dynamic schema.

Note:

The default schema must be specified for the connection. It will be replaced in runtime by the dynamic schema parameter value for each user.

The correct schema will be automatically ("dynamically") chosen for the connection based on the identity of the user creating the connection, according to the schema name defined for them in the dynamic schema User Parameter.

The default schema must also be provided in the connection configuration.

Example: