Multi Tenant support for Dashboard Save as into Database Schema

[jbat]

Dashboard Save as into Database Schema

Motivation

ReadyTech offers SaaS applications for education and workforce management built on the Ruby on Rails framework. With hundreds of clients, each having their own database instance, we've encountered challenges using Superset due to its limited support for multi-tenant configurations at the database level

Feature suggestion is to extend the existing Duplicate Dashboard functionality adding a new option with the ability to "Save as (into new database/schema)"

Direction is to extend support for multi tenant (database level) related infrastructure setups.

Goal: Given a Multi Tenant application setup with x number of logical databases (or db schemas) where
each database is an exact replica of the db structure, provide the ability to duplicate a superset dashboard so that all datasets are sourced from the new database and all new charts and filters then source data from the new datasets.

There are many discussion threads on slack relating to this.

For this initial discussion, the assumption would be that for the source dashboard, all datasets are based of 1 and only 1 database, for all filters and charts.

Current functionality for "Save Dashboard" includes these options:

• Overwrite Dashboard
• Save as
• Save as (also copy/duplicate slices)"

New option might be and additional 'Save As' to replicate all objects and point to a new database connector.

• "Save as (into new database/schema)"

Proposed Change

"Dashboard -> Save as (into new database/schema)"

Will result in all objects on a dashboard being duplicated including charts, datasets and filters with all data being sourced from new datasets pointing to the targeted database schema.

For the purpose of this discussion, applying the following terminology:
Source - the original source dashboard we are copying, also will be referred to as the template or parent.
Target - the resulting target new dashboard that all objects will relate to, aka as the child dashboard or children.

Possible Directions

Implementing the proposed changes could potentially take 2 directions

• A. Utilise a series of API calls to duplicate all dashboard objects and point to the target database
• B. Use the Export Dashboard process, then for the given target database, adjust the export yaml files and Import Dashboard to new Database

Direction A: Utilise API Work Flow

Using a combination of existing API calls, duplicate a dashboard and all objects to point to the target database.

1. duplicate the source dashboard and slices, resulting in the target dashboard and a new set of charts.
2. foreach dataset in the source, duplicate said dataset and update each to point to the new database
3. foreach new chart in the target dashboard, update the new chart to link to the new dataset created in point 2.
4. foreach new filter in the target, update the filter to link to the new dataset created in point 2
5. update target dashboard json metadata with new chart / dataset / filter details.

A Proof of Concept(POC) for the above API workflow has been successfully implemented in this ruby gem.

Ruby Superset API Client

# example to duplicate dashboard 90 into database 2, schema: client_2
Superset::Services::DuplicateDashboard.new(
    source_dashboard_id: 90,
    target_schema: 'client_2',
    target_database_id: 2
  ).perform

=> {:new_dashboard_id=>91, :new_dashboard_url=>"https://your-superset-host/superset/dashboard/91/"}

Direction B: Export Dashboard then 'Import to Database'

Not tested, just a idea atm based on slack discussions and Preset doc on managing content across env.

Background: understanding object UUIDs

Preset reference doc provides excellent description on object UUIDs usage in Superset.
https://docs.preset.io/docs/managing-content-across-workspaces

A dashboard export results in a series of YAML file for each of the primary objects, charts, datasets, databases, dashboard.
Each object has a identifier (UUID) that is unique to the Superset Environment it was created in.

A dashboard Import to a different Superset environment will result in creating a new dashboard and new objects with the same UUIDs.

A dashboard Import to the same Superset environment as the original export will result in updating any objects on the existing dashboard with the same UUIDs.

Dashboard Import to database

Would require:

1. Dashboard Export Zip file
2. Target Database and schema

Upon importing a Dashboard, the process would be to add the Target Database yaml file ( from point 2 ), and then create new UUIDs for all objects with the correct object UUIDs linking, and link all datasets to the target database UUID from point 2.

Importing a raw Dashboard Export, with a target database option, would result in:

• new Dashboard
• new set of Charts (and filters) pointing to
• new set of datasets pointing to
• the target database ( identified in point 2 )

Questions / Future Proofing

Logging / Debugging

Either option would require detailed logging and tracking ability.

Given there may be hundreds of boards duplicated off of a source template board a granular approach to logging would be desirable. With the idea that if the process fails half way through, the failure could be identified easily, and the process restarted from the point of failure.

How do we handle change?

(out of scope of this SIP .. but raising here for discussion / feedback)

Dashboards are always growing and changing to meet future demands / requests.
Replicating changes across a set of duplicated dashboards would be a complex process.

The Problem: Given we have a source parent dashboard template has already been used to duplicate across to N other child dashboards, the requirement is to make a change to the parent template dashboard and then replicate to all child boards.

Option 1: one solution would be to make changes in the parent template parent dashboard, and provide an option to 'Apply Changes to Children' which would then replicate the changes into each related child dashboard. This direction might imply something like the following:

• parent child relationship setup for template source dashboard to have many replicated children dashboards
• some type of change event tracking for a dashboard and objects that would then enable a change event to be repeatable across the set of replicated dashboards and objects. (putting this in writing already makes me cringe at the complexity )

Option 2: (cheap way out)

• make the changes in the template parent dashboard
• delete (cascade) all existing child dashboards and related objects
• then recreate all child dashboards from the template with new changes

Delete Cascade would need to entirely remove all objects related to each child dashboard. This assumes that each chart/dataset is 'only' used on the child dashboard targeted for deletion.

For any Embedded child dashboards that are deleted, it will mean the application setup to embed will need to re-link to the new set of dashboards. This implies any in-app authorisation to specific superset dashboards would also need to be re-link the new set of dashboards.

[AngeloHulshout]

I need something similar, are there any plans to implement this in a way that makes it easy to do from the UI?