As written by Patrick Drew, Developer @ Tessitura Network

----------------------

Tessitura Network, a non-profit company founded by leading arts organizations, advances the success of 650+ premier arts and cultural organizations worldwide with our unified enterprise CRM solution and by continually delivering innovative technology.

A key part of our delivery strategy is Business Intelligence, it is baked into our value proposition. Tessitura Network soft-launched our modern analytics solution with Sisense, Tessitura Analytics, in the Winter of 2017 and officially launched it in the Summer of 2018.

We provide analytics to our members embedded within our CRM system. We deploy Sisense with prebuilt dashboards and models but also enable members to build their own dashboards.

Several of our partners are on-premises, but many are managed by our Hosted Services team in the cloud. Each member organization has their own single-tenant version of our software.  

Automated and Integrated Installer

Installation of Sisense components is integrated with our Tessitura Installation Manager (TIM). TIM is a WPF application written in C# / XAML. It handles configuration input from system administrators and executes a series of NAnt scripts to install components. We utilize the Sisense Silent Installer within TIM to deploy the Web Application, Cube Server, data models and dashboards, then complete additional configuration using the Sisense REST APIs and PSM (Sisense Shell). This provides a seamless experience for the system administrators using TIM, they can install both Sisense and Tessitura components using the same application.

Our on-premises clients use TIM to complete installation. For our hosted clients, we introduced a layer of automation using GoCD and TIM. This has greatly improved the velocity at which we can deploy and upgrade Sisense in our hosted environment.

The Installation Manager Configuration Entry

Through TIM, the administrator can specify the data warehouse server and connection details, Sisense application licensing details including offline installer key, web application server name, port numbers, website name, default dashboard owner, cube server name, cube data directory path as well as SMTP server settings if they want to use a custom email server.

With the configuration details completed, the tasks below can be completed:

•        Install Analytics Web Application
•        Install Analytics Cube Server
•        Install Cubes
•        Install Dashboards

We give admins the option of installing web and cube components on different servers by manipulating the PrismFeature.xml file. TIM is bundled with a copy of the file with all features deactivated. During installation, the application checks if PrismFeature.xml exists on the target server, then copies the ‘blank’ version over and updates the file to activate the relevant Sisense component (Web or Cube Server). If the feature file exists on the server, we assume a Sisense component is already installed, so we just update the existing file to activate whatever the administrator is installing.

Installing Analytics Web Application

TIM uses the Sisense Silent Installer to deploy the Web Application. The installation is conditional, we compare the installer version against the Sisense component (if it exists) and will only run it if the installer is more recent. We do this to reduce the amount of time it takes to complete upgrades and apply service packs.

 

<exec program="SiSenseInstaller.exe" verbose="false">

<arg line="-q -nosamples"/>

<arg line="password=&quot;${Decrypt(analytics.app.encryptedPassword)}&quot;"/>

         <arg line="-webname=&quot;${analytics.app.sitename}&quot;" />

         <arg line="-webport=${analytics.app.port}"/>

</exec>

 

Once the web application is installed, TIM then performs additional configuration tasks:

•       enables SSL
•       Customize Sisense roles (Viewer, Admin & Designer) for unique business requirements like disabling ‘Copy to Server’ functionality for Admin and Designer Roles

Sisense REST API 0.9 /roles

• Setting up a custom email server, if required
• White-Labeling of Sisense including rebranding emails, and setting up a custom Homepage
• Installing custom plugins

Installing the Cube Server

Much like the Web Application, we use the Silent Installer to deploy the Cube Server, and installation is conditional. We give administrators the option to choose the default cube data dictionary and use PSM to update that after installation.

Deploying Cubes

Sisense data models aka ElastiCubes can be deployed automatically using a combination of Sisense Shell Commands and any scripting language.

Using Sisense Shell / PSM, the .ecube files are first converted to XML and poked to update their connection strings to match the SQL Server details entered into TIM. They are then converted back into the .ecube format and attached to the Cube Server. Finally, using the API we create a group with the same name as the cube and share the cube with it. We use this group to grant users access to the cube and any dashboards that use the cube as a data source.

Deploying Dashboards

The next step is deploying and publishing dashboards.

We deploy the dashboards in a folder hierarchy. We bundle our .dash files within a series of subdirectories off the root of TIM, this structure is created or updated in Sisense using the API. We then read into memory the .dash files, and update the cube names and ids of any “Jump To” dashboards in the dashboard JSON. The dashboard is then imported into Sisense and placed in the relevant folder, and we share the dashboard with the groups associated with their underlying Cubes.

The Sisense REST APIs provide end points to load dashboards (.dash files), change the underlying ElastiCube if needed, publish it and share it with corresponding users/groups.

• Load Dashboards (REST API v1.0)

• Share Dashboards (REST API v0.9)

The Dynamic Security Layer (Security Setup upon User Authentication)

Tessitura users are arranged into user groups with granular data security rights. In our security application, administrators can associate user groups to security objects such as cubes or dashboard roles and specify access rights - can they add, delete, edit or only view the object.

When users access Sisense, we use SSO to authenticate them. Once authenticated, middleware uses the Sisense REST APIs to dynamically update users’ data security and group membership before they are redirected to their dashboards.

Read more about setting up Data Security and how to automate row-level security using Sisense APIs

The Data Dictionary Plugin

For end users who know the original database and are familiar with the source, we wanted to provide a way for them to see where the dimensions and measures in a particular widget are coming from (all the way back to the source). In order to aid this, we built the data dictionary plugin.

This client-side plugin allows the user to view the dimensions used by the panels and filters of individual widgets, and maps the dimension back to the underlying column and table in our SQL Server data warehouse. Upon loading a dashboard, each widget gets a data dictionary icon added to its toolbar. When the user clicks on the icon, the widget JAQL is analysed and a new query constructed against a data dictionary table included in each cube. The results of the query are displayed in a table contained within a modal window.

The contents of the data dictionary table get generated before each release of Tessitura Analytics and are imported into the SQL Server data warehouse at installation. They are then included in each cube when processed. To generate the contents we use a custom C# console application to parse each cubes’ XML and map it to SQL Server metadata.

Steps to achieve this:

1. We built an application outside of Sisense that uses Sisense Shell commands to convert an ElastiCube into an XML file. The tool parses the XML to find the dimensions and measures of the ElastiCube and converts into a table format.

1. The tool then queries the SQL Server data warehouse to look up table metadata and maps it to the Sisense metadata contained within the cube XML. This mapping data is exported to CSV.
2. The CSV data is then imported into a data warehouse table, and then loaded back into each ElastiCube.
3. This process is executed when releasing a new version of Tessitura. When the user installs Tessitura Analytics, that data is then imported into the data warehouse and ElastiCube.
4. On the front end, when the data dictionary plugin is invoked, the plugin analyses the Sisense JAQL for that widget and extracts the dimensions and measures used.
5. The plugin sends a separate request to the ElastiCube to get the values from the data dictionary table mapped to the dimensions and measures used by the widget. The resulting data is populated in a modal window.

Learn about Sisense JAQL (the query that is sent to the ElastiCube Server) from the front-end and how to manipulate the JAQL easily in our Developer portal here and in the community here.

 

The Save To List Write-Back Plugin

In order to close the BI loop, we wanted to write-back data from one of our dashboards into the Tessitura Application. Tessitura has a List Manager feature that allows users of our software to generate lists of customers based on adhoc criteria (e.g. “Who purchased tickets last season”, “Who made a contribution to the annual fund last year”).  The “Save To List” plugin extends this functionality by letting users import a list of customers numbers from Sisense into the parent application for segmentation purposes.

We make extensive use of the existing “Jump To Dashboard” plugin. One of our “Jump To Dashboards” is a simple pivot table with customer details, users can jump to the dashboard from other locations and view the customer associated with the data displayed in the previous dashboard. The plugin adds an icon button to the widget toolbar of this specific dashboard only. When clicked, the button pops open a modal window, the user then enters a name for the list and submits the data. The list of customer ids displayed in the widget is then proxied to the Tessitura REST API. To get a full list of customer numbers without pagination, we rebuild the widget JAQL without the offset included in the pivot table. Authentication for Tessitura is handled by including an auth cookie with the HTTP request and configuring the server to allow credentialized requests from the Sisense domain.