Importing Data with Custom Queries

Sisense allows you to import data from a variety of data sources and manipulate the data that is imported by running custom SQL queries against the database, for example:

  • Import selected data only, to reduce import and build times when you do not require all the data.
  • Perform various procedures in the database, such as table joins.

After running your custom query, you can preview the updated tables before importing the data into the ElastiCube.

Note:

  • This feature is available for the following relational databases: AWS Athena, Databricks, Microsoft SQL Server, MySQL, PostgreSQL, Oracle, AWS Redshift, Salesforce, Snowflake, BigQuery and Generic JDBC.
  • This feature runs a query in the source database, which may require a lot of processing power from your database, especially for complex queries.

To add tables with custom queries:

  1. After selecting your database, click + at the bottom of the list of tables and views in the database. An empty custom table is added and the Settings area is displayed.

    Or
    When previewing an existing table, click Edit Query to modify the custom query.

  2. In the Settings area, enter your custom SQL query.

  3. Click + to add additional custom tables. You can delete any of the tables by selecting the import query's menu and clicking Delete.

  4. Click Done to import your custom data into Sisense.

Using the Advanced Limit Query Editor

If your SQL query parses successfully but the preview pane shows "Query Error", use the Advanced Limit Query Editor to resolve the problem.

By default, the Advanced Limit Query Editor is activated and available. It can be activated or deactivated as follows:

  1. Navigate to Admin > Server & Hardware > System Management > Configuration > 5 clicks on the Sisense logo > Base Configuration > Ecm.

  2. Activate or deactivate the Advanced Limit Query Editor.

  3. Click Save Base. A notice is displayed asking you to confirm the change.

  4. Click OK.

To resolve the preview pane query error and have the Preview Data pane display the preview correctly:

  1. When viewing your SQL query, click Advanced Query Editor to open it.

  2. Add the following to the end of your query: AND ROWNUM <= @numberofrecords.

    Example:

  3. Click Done. You are returned to the basic editor where your original SQL query appears as before, but now the Preview Data pane displays the preview correctly.

Dynamic Import Query

The pre-configured user parameters with static values (see Personalization Parameters for Live Data Sources) can be used as part of import queries. This enables personalized and dynamic import queries.

To activate/deactivate the dynamic import query feature:

The pre-configured user parameters with static values (see Personalization Parameters for Live Data Sources) can be used as part of import queries for Live models. This enables personalized and dynamic import queries.

To use the parameters in import queries:

To use a user-parameter in an import query, enclose the user-parameter between "{{" and "}}". Typing {{ in the query displays the menu of the existing parameters.

The value of the parameter is determined by the user or user group of the user, or the default value if specific values are not set for the user or user group.

Dynamic parameter values can also be used when creating new connections. Where parameters exist for fields in the connection page, clicking displays the menu of the existing parameters.

Note:

  • The user-parameter value must be set to the correct type so the query will give a valid result.

  • The data types of the columns of the custom table created by the dynamic custom query are set according to the data modeler-resolved query. Queries where the parameter resolves to a different data type will fail.

  • Every column parameter must have an alias, and that alias (not the actual parameter name) must be used in the query.

    Example of a bad query, without an alias for the parameter:

    Example of a good query, with an alias for the parameter:

Use Case Examples

  • Using a parameter as a literal in a user-defined function (UDF) or calculation:

    Example:

    Exchange rate: SELECT (Cost * {{exchange_rate}}) AS LOCAL_COST FROM TABLE1, where the exchange_rate parameter is replaced by its applicable value for the specific user. In this way, the user sees the cost amount in their own currency.

  • Using a parameter as a reference to an SQL element:

    Example:

    Time zones: In the source data, there are multiple date dimensions to show different time zones of the same transaction (fact).

    The dynamic query SELECT {{local_time_zone_column_name}} AS DATE_TIME FROM TABLE1 will return the local time for the user from TABLE1 that holds the converted time of the event. The local_time_zone_column_name represents the dimension column where the local times are stored.

    SELECT {{local_time_zone_column_name}} AS DATE_TIME FROM TABLE1 will return 23 March 2023 19:03 as the DATE_TIME for group 1, and 23 March 2023 22:03 as the DATE_TIME for group 2.

    The dashboard designer works with Date_Time, unaware of the fact that the table with DATE_TIME is populated in the background with the relevant value, determined by the local_time_zone_column_name dynamic reference parameter.