User Parameters - What They Are and What They're For (Example Use Cases)
User Parameters - What They Are and What They're For (Example Use Cases) User Parameters, or simply Parameters are user and user group-specific values that can be assigned to live database connections and queries. These values are resolved during query runtime and change dynamically depending on the current logged-in user. This means that we can manage a single Live Data Model with varying connection details and queries. By eliminating the need for multiple identical copies of data models and dashboards, parameters provide increased design flexibility and reduce maintenance complexity without compromising your data security restrictions. Parameters are managed by Admins and Data Admins. There are two types of parameters: Live Connection Parameters are used to personalize connection details, such as database connection string, location, username, and password. Custom Live Query Parameters are used to customize parts of live SQL queries, such as column names, calculations, and filter conditions. For more information on how to create Parameters, refer to this documentation: Personalization Parameters for Live Data Sources. Here are a few example use cases for User Parameters: Federating multiple isolated, structurally identical databases into a single model (parameterized database location or name) Enforcing row-level security rules already defined in the source database to Sisense queries (parameterized username and password) Automatic localization such as time zone conversion and currency conversion (parameterized formula or join condition) Personalized filter requirements (parameterized WHERE clause condition, e.g. history depth) Column-level security (parameterized column selection) Query tracking/audit (parameterized comment text) Let us know in the comments if you have other use cases of Parameters you'd like to share!User Parameters Use Case - Automatic Currency Conversion
User Parameters Use Case - Automatic Currency Conversion This article explains one of the example use cases of User Parameters laid out in User Parameters - What They Are and What They're For (Example Use Cases). In addition to connection settings, User Parameters can also be used in table queries in live models to provide tailored data to end users. One of the most common uses of this type of parameter is for customizing a formula to achieve an automatic localization, e.g. currency conversion. The idea is to use a parameter to associate each user group to their respective currency, then use the parameter to customize the table query and convert the default currency to users' local currencies. Depending on your requirements, you can use parameters to store a static exchange rate, or the currency code to allow for dynamic currency conversion. Let's walk through an example of each type of currency conversion. a. Static Conversion For simple static conversion, the values of the parameter should represent the exchange rates of the default currency to users' local currencies. When creating the parameter, be sure to select Custom Live Queries in the Applicable Areas field, and Number in the Field Type field. Here is how the exchange rate parameter should be defined: Once you've created the user parameter, the next step is to assign a value for each group (or individual user, if needed). Here is an example of user groups and value assignment for the exchange rate parameter, assuming the default currency is US Dollar (USD): Now that the parameter values have been assigned to groups, the final step is to create/modify your table query(ies). Let's take a table called FactPayments in an insurance company's data model as an example. This is the original query of the table: SELECT P.CLAIM_KEY , P.POLICY_KEY , P.PAYMENT_DT_TIME , CONVERT_TIMEZONE('UTC', '{{TIMEZONE}}', PAYMENT_DT_TIME) AS PAYMENT_DT_TIME_LOCAL , DATE_TRUNC('DAY', PAYMENT_DT_TIME_LOCAL) AS PAYMENT_DT_LOCAL , P.PAIDAMOUNT , P.STATUS , PO.CUSTOMERID FROM "INSURANCE_CLAIMS"."PAYMENTS" P JOIN "INSURANCE_CLAIMS"."POLICIES" PO ON P."POLICY_KEY" = PO."POLICY_KEY" In this example, the amount of funds paid to customers for their insurance claim is stored in USD in a column called PAIDAMOUNT. To convert the payment amount to users' local currencies, multiply the PAIDAMOUNT column by the value of the user parameter. To add a parameter to a query, type two curly brackets, then select the parameter from the dropdown menu. Here is what the conversion formula should look like: PAIDAMOUNT * {{EXCHANGE_RATE}} Here is the full, updated query of the FactPayments table: SELECT P.CLAIM_KEY , P.POLICY_KEY , P.PAYMENT_DT_TIME , CONVERT_TIMEZONE('UTC', '{{TIMEZONE}}', PAYMENT_DT_TIME) AS PAYMENT_DT_TIME_LOCAL , DATE_TRUNC('DAY', PAYMENT_DT_TIME_LOCAL) AS PAYMENT_DT_LOCAL , P.PAIDAMOUNT , {{EXCHANGE_RATE}} AS EXCHANGE_RATE , P.PAIDAMOUNT * {{EXCHANGE_RATE}} AS PAIDAMOUNT_LOCAL , P.STATUS , PO.CUSTOMERID FROM "INSURANCE_CLAIMS"."PAYMENTS" P JOIN "INSURANCE_CLAIMS"."POLICIES" PO ON P.POLICY_KEY = PO.POLICY_KEY Save the query and repeat the same step for all other tables that require currency conversion. Once completed, republish the model and revisit your dashboard. For this example, here is how the data originally looks with payment amount in USD: Here is how it looks for users in the Japan user group, for which the payment amount is converted with a static exchange rate of 151.7: b. Dynamic Conversion For a more complex conversion where historical exchange rate fluctuations need to be taken into account, a history table of currency exchange rates is required. This data is typically readily available within your organization or can be collected from public sources, such as the IMF. Here's an example of a historical exchange rate table (one date per currency): The values of the parameter should reflect the IDs of users' local currencies as referenced in the exchange rates history table. In this example, they should correspond to the values in the CURRENCY_CODE field in the history table above. Note that whitespace is not currently supported in parameter values. Therefore, using the full currency names as values, such as "Japanese Yen," will not be accepted. Here is how the currency code parameter should be defined: Here is an example of user groups and value assignment for the currency code parameter: In the context of the FactPayments table example, to convert the payment amount to users' local currencies using the exchange rate effective on the payment date, add a join in the query with the historical exchange rate table. The join conditions should be set on the currency code and date fields, where the currency code from the history table matches the value of the user parameter. Here is how the join should look like: JOIN "INSURANCE_CLAIMS"."EXCHANGE_RATE" X ON X.CURRENCY_CODE = '{{CURRENCY_CODE}}' AND PAYMENT_DT_LOCAL = X.DATE Here is the full, updated query of the FactPayments table: SELECT P.CLAIM_KEY , P.POLICY_KEY , P.PAYMENT_DT_TIME , CONVERT_TIMEZONE('UTC', '{{TIMEZONE}}', PAYMENT_DT_TIME) AS PAYMENT_DT_TIME_LOCAL , DATE_TRUNC('DAY', PAYMENT_DT_TIME_LOCAL) AS PAYMENT_DT_LOCAL , P.PAIDAMOUNT , X.EXCHANGE_RATE AS EXCHANGE_RATE , P.PAIDAMOUNT * X.EXCHANGE_RATE AS PAIDAMOUNT_LOCAL , '{{CURRENCY_CODE}}' AS CURRENCY_CODE , P.STATUS , PO.CUSTOMERID FROM "INSURANCE_CLAIMS"."PAYMENTS" P JOIN "INSURANCE_CLAIMS"."POLICIES" PO ON P.POLICY_KEY = PO.POLICY_KEY JOIN "INSURANCE_CLAIMS"."EXCHANGE_RATE" X ON X.CURRENCY_CODE = '{{CURRENCY_CODE}}' AND PAYMENT_DT_LOCAL = X.DATE Save the query and repeat the same step for all other tables that require currency conversion. Here is how the data originally looks with payment amount in USD: Here is how it looks for users in the Japan user group, for which the payment amount is converted using the USD-JPY exchange rate applicable on the payment date:User Parameters Use Case - Enforcing Data Isolation / Row-Level Security Rules from Source Database
User Parameters Use Case - Enforcing Data Isolation / Row-Level Security Rules from Source Database This article explains two of the example use cases of User Parameters laid out in User Parameters - What They Are and What They're For (Example Use Cases). The most common use case of User Parameters is to create a single dynamic data model that supports multiple database connections. A dynamic model has varying connection information that is resolved during runtime based on the currently logged-in user. These are two common scenarios that can be supported by a dynamic live model: Data are stored in multiple isolated, structurally identical databases and need to be federated in a single model (parameterized database location or name) Row-level security rules are already defined in the source database and need to be enforced to Sisense queries (parameterized username and password) Further details on each scenario and its implementation are provided below. 1. Data are stored in multiple isolated, structurally identical databases and need to be federated in a single model Certain organizations partition their data into separate isolated databases (e.g. one database per customer or region), typically due to laws, industry regulations, or company policies. For example, an insurance company serving the US and Australian markets may store the American customers' data in a Snowflake server located in Ohio and the Australian customers' data in a Snowflake server located in Sydney. We can serve both users in the US and Australia with a single data model without comprising security by using parameters to assign a connection string, database location, or database name to users and groups. For more information on how to create parameters, refer to this documentation: Personalization Parameters for Live Data Sources. Once the parameters are created and assigned to the appropriate groups and users, you can update your live model's connection settings and replace the hard-coded values with the parameters. Depending on the database vendor (Snowflake, Redshift, etc) and what is being parameterized, you can use the Location, Database, or Connection String fields. To select a parameter, click the dropdown menu to the right of the input field, then select the parameter name. The parameter name will then be populated in the input field in two pairs of curly brackets. Note that you can parameterize the whole or parts of a string. For example, if you opt to use the connection string field, and the databases are in the same server (they just have different warehouse/database names), you can specify the parameter as part of the string, as shown in the example below. jdbc://yoursnowflakeserver/?warehouse={{WAREHOUSE_NAME}}&db={{DATABASE_NAME}} If the two databases are stored in different servers, with different warehouse and database names, you can parameterize the whole string, as shown in the example below. The value of {{CONNECTION_STRING}} parameter is the entire connection string assigned to the groups/users. For example, this could be the connection string assigned to the American users: jdbc://yourUSAsnowflakeserver/?warehouse=USA_WH&db=USA_DB and this could be the connection string assigned to the Australian users: jdbc://yourAUSsnowflakeserver/?warehouse=AUS_WH&db=AUS_DB After updating the connection settings, republish the model and revisit your dashboard. The screenshots below show the same dashboard connected to the same data model, utilizing user parameters. When users open the dashboard, Sisense determines which database to be queried based on the value of the user parameter assigned to the logged-in user or the group they are a member of. Therefore, in this example, the entire dashboard displays only data from the region the user belongs to. This is most apparent in the State and City filters, as well as the Scatter Map widget. For USA users: For Australian users: Note that if further row-level restrictions are required within each database/region, data security rules still need to be established in the data model. 2. Row-level security rules are already defined in the source database and need to be enforced to Sisense queries In this scenario, each Sisense user or group has an associated account in the database server where row-level access rules have already been defined. By parameterizing the user and password used in the live model's connection settings, you don't have to recreate the security rules in the Sisense live model. In other words, when users open the dashboard, Sisense determines which database username and password will be used to establish the connection, based on the value of the user parameter assigned to the logged-in user or the group they are a member of. All associated row access policies from the source database are then enforced automatically to Sisense queries. The first step to implementing this use case is creating the username and password parameters, and assigning the appropriate values to the users and groups. After you create and assign these parameters, update your live model's connection settings and populate the User Name and Password input fields with the associated parameters, as shown in the example below. Save the changes and republish the model. Note that when you create the parameter for passwords, it's important to mark it as a hidden value for security. Selecting Yes for this option prevents assigned values from being displayed in plain text. Once the values are saved, they cannot be viewed anymore. It's also not possible to change the Hide Values option to No once the parameter has been created.
