Date Posted:
Product: TIBCO Spotfire®
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:
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] endThese 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: NoThe 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 Doc: Creating Information link Doc: Add Data Tables Doc: Insert Columns Doc: Filtering in Related data tables KB: How to setup caching for reports with personalized information links with row level security Doc: Personalized Information links Video: Setting up Personalized Information links