How to setup caching for reports with personalized information links with row level security.

How to setup caching for reports with personalized information links with row level security.

book

Article ID: KB0071221

calendar_today

Updated On:

Products Versions
Spotfire Analyst All Versions
Not Applicable -

Description

This article describes how Scheduled Updates can be used together with personalized information links to achieve row level security using the "Reload the following data for each user" functionality. This has the performance benefits of caching the primary data table in Scheduled Updates as well as the security benefits of personalized information links so that each user sees only the data they are allowed to see.

Issue/Introduction

How to setup caching for reports with personalized information links with row level security.

Environment

All

Resolution

  1. You need to have two data tables in your database, one which contains all of your data (AllData) and one which contains the security mapping which will be used to limit the data returned (SecurityMapping). For example:

    AllData
    MatchColumn ActualData 
    Finance Finance Record 1 
    Finance Finance Record 2 
    SalesSales Record 1 
    SalesSales Record 2 

    SecurityMapping
    MatchColumnUserName 
    Finance User 1 
    FinanceUser 2 
    SalesUser 1 
  2. Create column elements for AllData and SecurityMapping.
  3. Create a Filter element in Information Designer for "UserName" column in "SecurityMapping" table - Name it UserName_Filter.
    • Change the expression to "%1=%CURRENT_USER%" .
    • Make sure that %CURRENT_USER% and UserName are in the same format, e.g., user@email.com versus user. You can verify this by running an information link and checking the SQL in sql.log.
  4. Create an Information link - AllData_IL .
    • Add both columns (MatchColumn, ActualData) from AllData table.
  5. Create an Information link - SecurityMapping_IL .
    • Add both columns (MatchColumn, UserName) from SecurityMapping table.
    • Add filter created earlier - UserName_Filter .
  6. Add data table to your analysis.
    • File >> Add Data Table >> Information Link >> AllData_IL .
  7. Insert columns from SecurityMapping information link.
    • Insert >> Column >> Add Columns From >> Information Link >> SecurityMapping_IL >> Match on "MatchColumn" >> Inner Join (Rows matching in both original and new data only).
  8. Edit data table properties to exclude "SecurityMapping" from schedule updates so that it is loaded for each user.
    • Edit >> Data Table Properties >> Schedule Updates >> Select the checkbox for SecurityMapping_IL (Reload the following data for each user).
       
When user 2 logs in, it will load AllData_IL from the WebPlayer cache since it is preloaded during schedule updates. It will do a fresh load of SecurityMapping_IL and will only load the 2nd row in above example (Finance, User 2). When doing an inner join between AllData_IL and SecurityMapping_IL, all rows with Finance in MatchColumn will be loaded.

Additional Information

Q). What is "Reload the following data for each user"?

A). Lists all source data tables contributing to the data table selected in the Data tables field at the top of the dialog. If you have inserted additional columns or rows to a data table there may be more than one source table contributing to the data table.

Select the check box for data tables that should be reloaded for each user. All data sources with a check mark will be excluded from the scheduled update specified for the analysis and always reloaded when the analysis is opened by a new user.

Note: In order to preload any data for the analysis you must set up a scheduled update on the Web Player server. Click on the link at the bottom of the dialog to do this.
Powered by Wolken Software