Sisense Community

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

Categories

localization

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
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