User Parameters Use Case - Personalized Data Filter Requirements

[TriAnthony]

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
     P.CLAIM_KEY
     , P.POLICY_KEY
     , P.PAYMENT_DT_TIME
     , P.PAIDAMOUNT
     , P.STATUS
     , PO.CUSTOMERID
FROM "INSURANCE_CLAIMS"."PAYMENTS" P
JOIN "INSURANCE_CLAIMS"."POLICIES" PO
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
     P.CLAIM_KEY
     , P.POLICY_KEY
     , P.PAYMENT_DT_TIME
     , P.PAIDAMOUNT
     , P.STATUS
     , PO.CUSTOMERID
FROM "INSURANCE_CLAIMS"."PAYMENTS" P
JOIN "INSURANCE_CLAIMS"."POLICIES" PO
ON P."POLICY_KEY" = PO."POLICY_KEY"
WHERE P.PAYMENT_DT_TIME >= DATEADD(YEAR, -1 * {{HISTORY_DEPTH}}, CURRENT_DATE)

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: