How to Apply Row Level Security on multiple columns in TIBCO Spotfire – Revvity Signals

Date Posted: 2020-02-13
Product: TIBCO Spotfire®

Problem:

How to Apply Row Level Security on multiple columns in TIBCO Spotfire

Solution:

Row level security is applied to a data set to restrict data access per user. In TIBCO Spotfire, row level security can be setup using Personalized Information links. Row level security can be easily applied when security is based on only one column or more columns with "AND" condition between them.

For example:
1. When security needs to be applied only on one column:
User-added image You cannot directly apply "OR" condition when setting up data table relations or inserting columns or matching columns. Instead the following approaches can be used:

Approach 1:
If your requirement is as mentioned above in first point, then you can use the following approach:

1. You can follow steps 1-6 as mentioned in KB article How to setup caching for reports with personalized information links with row level security

2. Once the actual data set,for example "AllData", has been imported then create another data table based on AllData, for example "PivotedData", which would just have unique combination of Region and Business columns:

Select Data > Add...
    Source: Data table from current analysis
    Data table: AllData
    Update behavior: Automatic
    Added transformations
        Transformation name: Pivot
        Row identifiers:
            Region
            Business
        Value columns and aggregation methods: (None)
        Column titles: (None)
        Column naming pattern: %M(%V) for %C
        Transfer columns and aggregation methods: (None)
        Transfer column naming pattern: %A(%T)

3. Then add another data table which will contain security data and name it as "SecurityData".
4. Add calculated columns while transforming data:

Select Data > Transform data...
    Added transformations
        Transformation name: Calculate new column
        Column name: BusinessMatch
        Expression: case  when [Region] is null then [Business] end

        Transformation name: Calculate new column
        Column name: RegionMatch
        Expression: case  when [Business] is null then [Region] end

These calculated columns will contain only data where either Business or Region is null.

5. Now add column from PivotedData to SecurityData table and match only on Region columns:

Select Data > Add...
    Source: Data table from current analysis
    Data table: PivotedData
    Update behavior: Automatic
    Data was added as new columns in data table 'SecurityData'
    Matching behavior: Tries to match the specified columns when data is loaded
    Matched columns: RegionMatch – Region
    Added columns: Business
    Ignored columns: (None)
    Join method: Left outer join
    Treat empty values as equal: No

6. Now add column from PivotedData to SecurityData table and match only on Business columns:

Select Data > Add...
    Source: Data table from current analysis
    Data table: PivotedData
    Update behavior: Automatic
    Data was added as new columns in data table 'SecurityData'
    Matching behavior: Tries to match the specified columns when data is loaded
    Matched columns: BusinessMatch – Business
    Added columns: Region
    Ignored columns: (None)
    Join method: Left outer join
    Treat empty values as equal: No

The above inserted columns will fetch corresponding Region and Business based on matched Business and Region respectively i.e. if Region columns are matched, then all Business values would be fetched where region is Americas. Similarly, when Business columns are matched, then all Regions values would be fetched where Business is Finance.

7. Now, add calculated columns which would be used to set up data table relation between AllData and SecurityData tables. If Region/Business columns inserted above are null then use original Region/Business column (when both values are present eg: Region = Europe and Business = Transportation) otherwise use inserted column values:

Data > Add calculated column...
    Column name: Region Filter
    Expression: SN([Region (2)],[Region])
Data > Add calculated column...
    Column name: Business Filter
    Expression: SN([Business (2)],[Business])

8. Setup the data table relation between AllData and SecurityData tables:

Data > Data table properties > Relations
    Added relation:
        [SecurityData].[Region Filter] – [AllData].[Region]
        [SecurityData].[Business Filter] – [AllData].[Business]

9. Open the Filter Panel, go to AllData table > Click on related tables icon > Filtering in Data table > Select Include Filtered Rows Only

10. If you are caching this analysis using schedule updates, Edit data table properties to exclude SecurityData table from schedule updates so that it is loaded for each user.
Data >> Data Table Properties >> Schedule Updates >> Select the checkbox for SecurityData, PivotedData, PivotedData (Reload the following data for each user).

Approach 2:
If your requirement is similar to that mentioned in second point of Details section, then you can use the following approach:

1. All steps will remain the same as Approach 1 except steps 4, 5, 6 and 7. Step 4 is not required now as there is no combination of security data involved.

2. Step 5 and Step 6 will have column matches on original Region and Business columns from SecurityData and PivotedData tables.

3. Step 7 calculated columns will have different expression. Now as Regions/Business present in SecurityData table are only required, the rest of them can be nullified:

Data > Add calculated column...
    Column name: Region Filter
    Expression: case  when Find([Region (2)],UniqueConcatenate([Region]))<>0 then [Region (2)] end

Data > Add calculated column...
    Column name: Business Filter
    Expression: case  when Find([Business (2)],UniqueConcatenate([Business]))<>0 then [Business (2)] end

Example Spotfire .dxp files for both Approach 1 and Approach 2 are attached for reference. Note: In the .dxp files, instead of using security Information link, data is imported from from clipboard so it opens up without any error. Doc: Create Data Table Relations