Personal data using Spotfire® Data Connectors, Scheduled Updates and Parameterized Custom Queries

Introduction

Sometimes, for example in a lab environment or test system, you may not have been able to implement row level security in a Spotfire^® data source. Your IT department might also have a policy that access control should be implemented in each application layer accessing data sources. When this is not the case and access control is managed in the data source, this can be done using, for example, SAP HANA Analytic Privileges, Apache KNOX, Cloudera Sentry or a third party platform in combination with personal logins to the data source and Kerberos for Single Sign-On.

NOTE: Consumers using this article's example should not be given access rights to Spotfire Analyst, Spotfire Business Author or permission to download a local copy of analysis files. Spotfire authors can view complete data sets and change configurations.

This article will show you how to load a large data table into memory that contains the complete data table with data for all users. When users log in they are only served their personal data rows, which they are allowed to view. The analysis file with personalized data will load extremely fast, since we are using Spotfire scheduled updates to preload the complete data table.

This solution uses a combination of Spotfire features; data connectors, scheduled spdates, parameterized custom queries and add columns (JOINs).

If you are new to scheduled updates, they are basically used on Node Managers (Web Player instances) to preload analysis files and their data into memory, before 1000's of consumers log in in the mornings to consume analysis files.

The key to scheduled updates is that you need to make sure that the service account used by the scheduled updates process has permission to access the (complete) data sets. This example explains how to solve this with (1) credentials stored in the Web Players's web.config file and (2) credentials stored in the data source library object.

Prerequisites

The following products and assets are used in this article:

Spotfire Analyst and Spotfire Consumer.
A Spotfire Server and Node Manager.
A relational database.
A complete data table with data for all users.
A lookup data table used to personalize data.

Preparing the two data tables used in this article

The complete data table

The complete data table containing data to be analyzed is the ORDER table from TPC-H.

It's available at http://www.tpc.org/tpch/ and https://github.com/electrum/tpch-dbgen.

This is an example row from the complete data table:

OrderKey    CustKey    OrderStatus    TotalPrice    OrderDate    OrderPriority    Clerk    ShipPriority    Comment
5894885    68231    F    179855,76    1993-10-17 00:00:00    2-HIGH    Clerk#000000001    0    ckly ironic deposits. ironic

The lookup table

The lookup table named Permissions can be generated like this:

CREATE TABLE [dbo].[Permissions](
    [SpotfireUser] [varchar](50) NOT NULL,
    [Clerk] [varchar](50) NOT NULL,
    primary key (SpotfireUser, Clerk)
) ON [PRIMARY]

Data can be inserted into the lookup table like this:

INSERT INTO Permissions(SpotfireUser, Clerk)
       VALUES ('user1', 'Clerk#000000001')

The resulting lookup table looks like this:

SpotfireUser,Clerk
user1,Clerk#000000001
user2,Clerk#000000002
user3,Clerk#000000003

Configuring your Spotfire environment

Make sure the connector is available to your users

Spotfire data connectors use a data source, which is similar to an information link data source, and a data connection. The data source object basically stores the connection string. The data connection stores a metadata view of tables, queries, and columns used to define a data table. The data connections and data sources can be stored in analysis files, but it's preferable to store them in the Spotfire Library. They can then be secured just like other Spotfire Library items.

For more information regarding Spotfire connectors, please see the online help: What is a Data Connection?.

Data connector access control is determined in the Administration Manager, under Groups and Licenses:

This determines what connectors show up in the Spotfire >Add Data Tables dialog:

Make sure that you are a custom query author

Before saving the analysis file, make sure that you are a custom query author. If not, the lookup table, which is based on a custom query, won't be loaded since the custom query has not been trusted by a custom query author. Like connector access, this is also configured in the Administration Manager under Groups and Licenses, see the image above. If you make yourself a Custom Query Author in the current Spotfire Analyst session, you will need to close Spotfire Analyst and re-open it in order for Spotfire Analyst to get the new group membership information.

Make sure to add the test users

In the example "Permissions" table, rows were added for user1, user2, and user3. Make sure to add these users as users in the Spotfire Administration Manager either in Spotfire Analyst or using the Spotfire Administrative Web Interface. The users should have access to the Spotfire Consumer in order to test opening the analysis by multiple users.

Credentials profiles

The scheduled updates user can't fill in usernames and passwords in prompts. Therefore credentials need to be stored somewhere. This section describes how to store the credentials as a credentials profile on the Web Player server.

On the computer running Spotfire Server, open a command prompt, change directory to \tomcat\bin\.

For help on available config commands type:

\tomcat\bin\config
\tomcat\bin\config <command> --help

1. Export the default Spotfire.Dxp.Worker.Host.exe.config file. You will add the credentials profile section to this file and upload it to the server with a new name once done.

 config export-service-config --tool-password=<yourToolPassword> --capability=WEB_PLAYER --deployment-area=Production

By default, a collection of config files end up in \tomcat\bin\config\root.

2. Verify that the config files that are distributed to WebPlayer nodes will be encrypted by checking that the <cryptography encryptConfigurationSections="true" ..../> flag in the Spotfire.Dxp.Worker.Core.config file is set.

3. In the Spotfire.Dxp.Worker.Host.exe.config file search for Spotfire.Dxp.Web.Properties.Settings and the DataAdapterCredentials section.

Insert the following block into the setting/value/credentials section:

<entry profile="myprofile">
    <username>user</username>
    <password>password</password>
</entry>

4. In the Spotfire.Dxp.Worker.Host.exe.config file search for Spotfire.Dxp.Data.Access.Adapters.Settings and the WebAuthenticationMode section.

In the WebAuthenticationMode section for the adapter in use, set the "mode" attribute to "WebConfig" in order to use the profile name set earlier. This example sets it for the SqlServerAdapter:

<setting name="WebAuthenticationMode" serializeAs="Xml">
    <value>
        <adapters>
            <adapter name="Spotfire.SqlServerAdapter" mode="WebConfig"/>
            [... Other Adapters Here ...]
        </adapters>
    </value>
</setting>

Save the file in \tomcat\bin\config\root.

NOTE: If multiple authentication methods are needed for the same connector you need to deploy one Web Player instance per authentication method and direct the analysis files to the web player with the WebConfig setting you wish to use.

5. Now it's time to upload the new file to the Spotfire Server. Note the flag that removes the \tomcat\bin\config\root folder after upload as a security measure. Please note down the new name of the new configuration (file), in this case "TheNewCredentialsProfileConfig".

 config import-service-config --tool-password=spotfire --config-name=TheNewCredentialsProfileConfig --delete-directory

All command prompt tasks are now completed.

6. It's now time to activate the new configuration.

Open a web browser and log in to the Spotfire Server.
Browse to Nodes & Services.
Select the WEB_PLAYER service.
Select Edit Service.
Select the TheNewCredentialsProfileConfig configuration in the drop-down.

Repeat this step for all WEB_PLAYER services you wish to deploy the TheNewCredentialsProfileConfig on.

The TheNewCredentialsProfileConfig will also be available when new WEB_PLAYER services are created.

The instance will restart automatically.

Creating the analysis file

In Spotfire Analyst, connect to and import your complete data table, in this example the orders table from TPC-H:

Make sure when setting the authentication method for the data source to use a username and password-based authentication so that the profile is set in the Spotfire.Dxp.Worker.Host.exe.config file will work. For SQL Server, this would be the "SQL Sever authentication" authentication method.

The result could look something like this in Spotfire's source view:

The next step is to define the JOIN between the complete data table and the lookup table. Select Insert > Columns....

Connect to your data source and create a PermissonsLookupTable custom query. In the image below the lookup table is named Permissions:

 select * from Permissions WHERE SpotfireUser = ?current_user

Remember to select the check box to enable Use parameters for personalized queries.

Match on the Clerk columns:

Use an Inner JOIN:

The result looks like this:

Note that the final data table now only shows rows for the current user.

IMPORTANT: All data is still available in the analysis and can be viewed in the source view, on the first data source we created. A reminder that data is only private for Spotfire Consumer users, not for the user that can download and/or open the file in Spotfire Analyst or Spotfire Business Author.

Now, go to Edit > Data Connection Properties to configure the profile name for the data source authentication to work in the Web Player. In the Data Connection Properties dialog, click the "Settings..." button. In the Data Connection Settings dialog, select the Data Source tab and click the "Settings..." button. In the Data Source Settings dialog, select the Credentials tab and select "No, but save credentials profile..." and set the profile name to match the name set in the Spotfire.Dxp.Worker.Host.exe.config file above. In this example, "myprofile" is used:

Click Ok on the Data Source Settings, Data Connection Settings and Data Connection Properties dialogs to get back to the analysis. The data will be reloaded between the Data Connection Settings and Data Connection Properties dialogs.

Now, go to Edit > Data Table Properties to configure the PermissonsLookupTable table to always reload with each user. This will exclude the table from the scheduled updates job.

What's left is to share the analysis file in the Spotfire Library and configure Scheduled Updates for it. Then you're done!

Sign In