User Parameters Use Case - Query Tracking / Audit

[TriAnthony]

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
     C."ID"
     , C."NAME"
     , C."DATE_OF_BIRTH"
     , S."SEXASSIGNEDTYPE" AS SEX
     , A."ADDRESS"
     , A."CITY"
     , A."STATECODE"
     , A."STATENAME"
     , A."COUNTRYCODE"
     , A."COUNTRY"
     , A."ZIPCODE"
FROM "INSURANCE_CLAIMS"."CLIENTS" C
JOIN "INSURANCE_CLAIMS"."SEXES" S
ON C."GENDERTYPE" = S."SEXASSIGNEDCODE"
JOIN "INSURANCE_CLAIMS"."ADDRESSES" A
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:

/* Query executed by {{USER_QUERY_TRACKER}} */

Here is the full, updated query of the DimClients table:

/* Query executed by {{USER_QUERY_TRACKER}} */
SELECT
     C."ID"
     , C."NAME"
     , C."DATE_OF_BIRTH"
     , S."SEXASSIGNEDTYPE" AS SEX
     , A."ADDRESS"
     , A."CITY"
     , A."STATECODE"
     , A."STATENAME"
     , A."COUNTRYCODE"
     , A."COUNTRY"
     , A."ZIPCODE"
FROM "INSURANCE_CLAIMS"."CLIENTS" C
JOIN "INSURANCE_CLAIMS"."SEXES" S
ON C."GENDERTYPE" = S."SEXASSIGNEDCODE"
JOIN "INSURANCE_CLAIMS"."ADDRESSES" A
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 [email protected]:

Here is how the query looks like on the Snowflake query monitoring tool: