Sisense Community

Community Feedback
Chapters
Events
Forums
Blogs
KB Docs
Support
Learning
Use Case Gallery

Categories

dynamic live connection

Sisense Administration
User Parameters Use Case - Query Tracking / Audit

User Parameters Use Case - Query Tracking / Audit 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) . User Parameters can be used in live models to generate tailored queries. One of the benefits of this feature is it allows data designers to add personalized tags or comments in table queries for query tracking and audit purposes. By default, only the user specified in the connection settings will appear in your database query monitoring tool. This means that unless every Sisense user has an associated account in the database, there is no way to track which Sisense user triggers a query to the database. User parameters can be used to address this problem. The idea is to use a parameter to associate each user (or group) to their respective name, email address, or other identifiable information, then use the parameter in a comment line in the table query. Let's walk through an example of a Snowflake live model. Let's suppose the Snowflake data admin wants to track every Sisense query sent to the database based on who triggers it, without having to create individual users in the database for each Sisense user. The first step to implement this requirement is to create the user parameter. When creating parameters for a query, be sure to select Custom Live Queries in the Applicable Areas field. Once you've created the user parameter, the next step is to assign a value for each individual user (or group if that is sufficient). For this use case, the value should represent the username or email of the user. Note that whitespace is not currently supported in parameter values. Therefore, using the regular name format, such as "John Doe," will not be accepted. Below is an example of a user and value assignment for this parameter. Now that the parameter values have been assigned to users, the final step is to create/modify your table query(ies). Ideally, the comment should be added to the query of the security table to ensure it appears in all widget queries. If you don't have row-level security defined for the model or have scope limitations defined for the security rules, you may need to add the comment to all table queries. Let's take a table called DimClients as an example. This is the original query of the table: SELECT<br/> C."ID"<br/> , C."NAME"<br/> , C."DATE_OF_BIRTH"<br/> , S."SEXASSIGNEDTYPE" AS SEX<br/> , A."ADDRESS"<br/> , A."CITY"<br/> , A."STATECODE"<br/> , A."STATENAME"<br/> , A."COUNTRYCODE"<br/> , A."COUNTRY"<br/> , A."ZIPCODE"<br/>FROM "INSURANCE_CLAIMS"."CLIENTS" C<br/>JOIN "INSURANCE_CLAIMS"."SEXES" S<br/>ON C."GENDERTYPE" = S."SEXASSIGNEDCODE"<br/>JOIN "INSURANCE_CLAIMS"."ADDRESSES" A<br/>ON C."ID" = A."CLIENTID" To provide tracking/audit information to the query, add a comment line and include the parameter. To add a parameter to a query, type two curly brackets, then select the parameter from the dropdown menu. For this example, here is how the comment line should look like: <font color="#000000">/* Query executed by {{USER_QUERY_TRACKER}} */</font> Here is the full, updated query of the DimClients table: <strong><font color="#993300">/* Query executed by {{USER_QUERY_TRACKER}} */</font></strong><br/>SELECT<br/> C."ID"<br/> , C."NAME"<br/> , C."DATE_OF_BIRTH"<br/> , S."SEXASSIGNEDTYPE" AS SEX<br/> , A."ADDRESS"<br/> , A."CITY"<br/> , A."STATECODE"<br/> , A."STATENAME"<br/> , A."COUNTRYCODE"<br/> , A."COUNTRY"<br/> , A."ZIPCODE"<br/>FROM "INSURANCE_CLAIMS"."CLIENTS" C<br/>JOIN "INSURANCE_CLAIMS"."SEXES" S<br/>ON C."GENDERTYPE" = S."SEXASSIGNEDCODE"<br/>JOIN "INSURANCE_CLAIMS"."ADDRESSES" A<br/>ON C."ID" = A."CLIENTID" Save the query, and if needed, repeat the same step for all other tables. Once completed, republish the model. In this example, here is how a widget query looks like from the Analyze SQL query option (which can be found in the widget menu), when it's accessed by the user tri.anthony@sisense.com : Here is how the query looks like on the Snowflake query monitoring tool:
Tri AnthonyPosted 2 years ago
0
Data Models
User Parameters Use Case - Personalized Data Filter Requirements

User Parameters Use Case - Personalized Data Filter Requirements 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) . User Parameters can be used in table queries in live models to provide tailored data to end users. A common use case of this type of parameter is for personalizing filters of certain tables in a live model. For example, users in different countries may have different history depth requirements or data retention policies. The idea is to use a parameter to associate each user group to their respective filter value, then use the parameter to customize the WHERE clause of the table query. Let's walk through an example of a Snowflake live model. Let's suppose we have an insurance company that has clients in the US and Australia. While all claim data is stored in the database, let's say for analytical purposes, claim managers in the US are allowed access to only one year of data, while those in Australia have access to two years. The first step to implement this requirement is to create the user parameter. When creating parameters for a query, be sure to select Custom Live Queries in the Applicable Areas field. Once you've created the user parameter, the next step is to assign a value for each group (or individual user, if needed). For this use case, the value should represent the required history depth, measured in years, i.e. 1 for US users and 2 for Australia users. 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 as an example. This is the original query of the table: SELECT<br/> P.CLAIM_KEY<br/> , P.POLICY_KEY<br/> , P.PAYMENT_DT_TIME<br/> , P.PAIDAMOUNT<br/> , P.STATUS<br/> , PO.CUSTOMERID<br/>FROM "INSURANCE_CLAIMS"."PAYMENTS" P<br/>JOIN "INSURANCE_CLAIMS"."POLICIES" PO<br/>ON P."POLICY_KEY" = PO."POLICY_KEY" To limit the data to the required history depth, add a WHERE clause with a condition that ensures the date field values are within the last number of years assigned in the user parameter. To add a parameter to a query, type two curly brackets, then select the parameter from the dropdown menu. For this example, here is how the WHERE clause condition should look like for a Snowflake database: WHERE P.PAYMENT_DT_TIME >= DATEADD(YEAR, -1 * {{HISTORY_DEPTH}}, CURRENT_DATE) Here is the full, updated query of the FactPayments table: SELECT<br/> P.CLAIM_KEY<br/> , P.POLICY_KEY<br/> , P.PAYMENT_DT_TIME<br/> , P.PAIDAMOUNT<br/> , P.STATUS<br/> , PO.CUSTOMERID<br/>FROM "INSURANCE_CLAIMS"."PAYMENTS" P<br/>JOIN "INSURANCE_CLAIMS"."POLICIES" PO<br/>ON P."POLICY_KEY" = PO."POLICY_KEY"<br/><strong><font color="#993300">WHERE P.PAYMENT_DT_TIME >= DATEADD(YEAR, -1 * {{HISTORY_DEPTH}}, CURRENT_DATE)</font></strong> Save the query and repeat the same step for all other tables that require a restriction on history depth. Once completed, republish the model and revisit your dashboard. In this example, here is how the data looks for the US users: Here is how the data looks for Australian users:
Tri AnthonyPosted 2 years ago
0
Security
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!
Tri AnthonyPosted 2 years ago • Last reply 2 years ago
1
How-Tos & FAQs
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<br/> P.CLAIM_KEY<br/> , P.POLICY_KEY<br/> , P.PAYMENT_DT_TIME<br/><span> , CONVERT_TIMEZONE('UTC', '{{TIMEZONE}}', PAYMENT_DT_TIME) AS PAYMENT_DT_TIME_LOCAL<br/> , DATE_TRUNC('DAY', PAYMENT_DT_TIME_LOCAL) AS PAYMENT_DT_LOCAL</span><br/> , P.PAIDAMOUNT<br/> , P.STATUS<br/> , PO.CUSTOMERID<br/>FROM "INSURANCE_CLAIMS"."PAYMENTS" P<br/>JOIN "INSURANCE_CLAIMS"."POLICIES" PO<br/>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: <font color="#000000">PAIDAMOUNT * {{EXCHANGE_RATE}}</font> Here is the full, updated query of the FactPayments table: <span>SELECT<br/> P.CLAIM_KEY<br/> , P.POLICY_KEY<br/> , P.PAYMENT_DT_TIME<br/> , CONVERT_TIMEZONE('UTC', '{{TIMEZONE}}', PAYMENT_DT_TIME) AS PAYMENT_DT_TIME_LOCAL<br/> , DATE_TRUNC('DAY', PAYMENT_DT_TIME_LOCAL) AS PAYMENT_DT_LOCAL<br/> , P.PAIDAMOUNT<br/><font color="#993300"><strong> , {{EXCHANGE_RATE}} AS EXCHANGE_RATE</strong></font><br/><font color="#993300"><strong> , P.PAIDAMOUNT * {{</strong></font><font color="#993300"><strong>EXCHANGE_RATE</strong></font><font color="#993300"><strong>}} AS PAIDAMOUNT_LOCAL</strong></font><br/> , P.STATUS<br/> , PO.CUSTOMERID<br/>FROM "INSURANCE_CLAIMS"."PAYMENTS" P<br/>JOIN "INSURANCE_CLAIMS"."POLICIES" PO<br/>ON P.POLICY_KEY = PO.POLICY_KEY</span> 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<br/>ON X.CURRENCY_CODE = '{{CURRENCY_CODE}}'<br/>AND PAYMENT_DT_LOCAL = X.DATE Here is the full, updated query of the FactPayments table: SELECT<br/><span> </span>P.CLAIM_KEY<br/><span> </span>, P.POLICY_KEY<br/><span> </span>, P.PAYMENT_DT_TIME<br/><span> </span>, CONVERT_TIMEZONE('UTC', '{{TIMEZONE}}', PAYMENT_DT_TIME) AS PAYMENT_DT_TIME_LOCAL<br/><span> </span>, DATE_TRUNC('DAY', PAYMENT_DT_TIME_LOCAL) AS PAYMENT_DT_LOCAL<br/><span> </span>, P.PAIDAMOUNT<br/><font color="#993300"><strong> , X.EXCHANGE_RATE AS EXCHANGE_RATE</strong></font><br/><font color="#993300"><strong> , P.PAIDAMOUNT * X.EXCHANGE_RATE AS PAIDAMOUNT_LOCAL</strong></font><br/><font color="#993300"><strong> , '{{CURRENCY_CODE}}' AS CURRENCY_CODE</strong></font><br/><span> </span>, P.STATUS<br/><span> </span>, PO.CUSTOMERID<br/>FROM "INSURANCE_CLAIMS"."PAYMENTS" P<br/>JOIN "INSURANCE_CLAIMS"."POLICIES" PO<br/>ON P.POLICY_KEY = PO.POLICY_KEY<br/><font color="#993300"><strong>JOIN "INSURANCE_CLAIMS"."EXCHANGE_RATE" X</strong></font><br/><font color="#993300"><strong>ON X.CURRENCY_CODE = '{{CURRENCY_CODE}}'</strong></font><br/><font color="#993300"><strong>AND PAYMENT_DT_LOCAL = X.DATE</strong></font> 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:
Tri AnthonyPosted 2 years ago
0
How-Tos & FAQs
User Parameters Use Case - Automatic Time Zone Conversion

User Parameters Use Case - Automatic Time Zone 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. time zone conversion. The idea is to use a parameter to associate each user group to their respective time zone name or UTC offset, then use the parameter to customize the table query and convert the default time zone to users' local time zones. Let's walk through an example of an automatic time zone conversion on a Snowflake live model. The first step is to create the user parameter. When creating parameters for a query, be sure to select Custom Live Queries in the Applicable Areas field. Once you've created the user parameter, the next step is to assign a value for each group (or individual user, if needed). For this use case, the value should represent the time zones of the users, either the standardized name or the UTC offset, depending on the database that you use. Snowflake, in particular, has a pre-built time zone conversion function called CONVERT_TIMEZONE that accepts IANA standardized time zone names as inputs. This function greatly simplifies the conversion formula because it automatically takes into account daylight saving time/summer time. Below is an example of user groups and value assignment for the time zone parameter. 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<br/> P.CLAIM_KEY<br/> , P.POLICY_KEY<br/> , P.PAYMENT_DT_TIME<br/> , P.PAIDAMOUNT<br/> , P.STATUS<br/> , PO.CUSTOMERID<br/>FROM "INSURANCE_CLAIMS"."PAYMENTS" P<br/>JOIN "INSURANCE_CLAIMS"."POLICIES" PO<br/>ON P."POLICY_KEY" = PO."POLICY_KEY" In this example, payment dates and timestamps are stored in UTC in the PAYMENT_DT_TIME column. To convert the timestamps to users' local timezones, add a new calculated column utilizing Snowflake's CONVERT_TIMEZONE function and the user parameter. To add a parameter to a query, type two curly brackets, then select the parameter from the dropdown menu. Here is how the conversion formula should look like: CONVERT_TIMEZONE(UTC , {{TIMEZONE}}, PAYMENT_DT_TIME) Here is the full, updated query of the FactPayments table: SELECT<br/> P.CLAIM_KEY<br/> , P.POLICY_KEY<br/> , P.PAYMENT_DT_TIME<br/><font color="#993300"> <strong>, CONVERT_TIMEZONE('UTC', '{{TIMEZONE}}', P.PAYMENT_DT_TIME) AS PAYMENT_DT_TIME_LOCAL //Date with timestamp<br/> , DATE_TRUNC('DAY', PAYMENT_DT_TIME_LOCAL) AS PAYMENT_DT_LOCAL //Date without timestamp</strong></font><br/> , P.PAIDAMOUNT<br/> , P.STATUS<br/> , PO.CUSTOMERID<br/>FROM "INSURANCE_CLAIMS"."PAYMENTS" P<br/>JOIN "INSURANCE_CLAIMS"."POLICIES" PO<br/>ON P.POLICY_KEY = PO.POLICY_KEY Save the query and repeat the same step for all other tables that require time zone conversion. Once completed, republish the model and revisit your dashboard. For this example, here is how the data originally looks with dates in UTC time zone: Here is how it looks for users in the U.S. Eastern time zone: Note : if your database does not have a pre-built time zone conversion function, you can use UTC offsets as the values of the parameter. For example, for Eastern Standard Time (UTC-5), the value for the parameter should be -5. You can then add the offset to the timestamp in the query using the date manipulation function that your database supports (DATEADD for SQL Server, Snowflake, and Amazon Redshift, DATE_ADD for MySQL, + INTERVAL for PostgreSQL and Oracle ). The limitation of this approach is that it doesn't automatically account for daylight saving time. Therefore, you will need to build logic in the query to handle this aspect (e.g., get the daylight saving period for each time zone, then use a CASE statement to check if each date falls within that period).
Tri AnthonyPosted 2 years ago
0
Data Models
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.
Tri AnthonyPosted 2 years ago
0