Dedicated warehouse databases for tenants

1. Overview

Tenants can have their own dedicated warehouse databases. This allows for better security control and also helps isolate the load on the warehouse databases between tenants, when compared to a shared warehouse database.

This article provides the necessary steps to:

  • Create a dedicated tenant warehouse database and migrate tenant data from the global warehouse database.
  • Remove a dedicated tenant warehouse database and migrate tenant data back to the global warehouse database.

Note
The tasks detailed in this article are advanced and it is recommended that they be performed by users familiar with using .NET or REST APIs and the command line interface.

2. Create a dedicated tenant warehouse database

Creating a dedicated warehouse database and migrating tenant data from the global warehouse database to the tenant warehouse database involves the following steps.

2.1. Disable the tenant

Access Administration from the main menu, expand the SaaS / Multi-Tenancy section, and click Tenants. (If you don't see this option, it means you aren't licensed for this feature.) 

In the Tenants screen, select a tenant from the list and click Edit on the toolbar.

Edit a tenant
Edit a tenant

In the Tenant Details dialog uncheck the Enabled check box.

Disable a tenant
Disable a tenant

2.2. Set the configuration value from the command line

Set the AllowTenantWarehouses configuration setting value to True using the command line.

Using the command prompt, enter the following:

dt setConfigValue /settingId:cde27d97-d493-475d-aba2-783b4a200237 /value:True

You can find more information about this command in the article on setting a configuration value from the command line.

2.3. Add the tenant warehouse override using the API

You must add a tenant warehouse override using either the .NET API or the REST API.

Note
More than one TenantWarehouseOverride can be created for a tenant, each one corresponding to a different scope (similar to configuration settings). This flexibility would be required if you have a server farm in which different servers need to use different connection strings for the same physical database. The overrides here represent a different means of accessing the same database, rather than accessing different databases. In most cases this flexibility is not required, and the warehouse override should be applied only at the Global scope.

The following example demonstrates how to add a tenant warehouse database override using the .NET API:

// get tenant
var mtSvc = Engine.Current.GetService<IMultiTenancyService>();
Tenant tenant = mtSvc.GetTenant(/*pass tenant ID here*/);

// create tenant warehouse override
TenantWarehouseOverride whOverride = new TenantWarehouseOverride()
{
       Scope = AppSettingScope.Global, // desired scope 
       ScopeTarget = null, // desired scope target
       ConnectionString = tenantWHConnStr, // connection string to the future tenant warehouse DB
 };
tenant.SetWarehouseOverride(whOverride);
mtSvc.SaveTenant(tenant);

The following example demonstrates how to add a tenant warehouse database override using the REST API from JavaScript, where a sessionId has already been obtained via logging on:

var warehouseOverrideData = {
    connectionString: "[connectionString]",
    password: "[password]",
    scope: "[scope]",
    scopeTarget: "[scopeTarget]"
};

var jqXHR = $.ajax({
    url: "/api/tenant/SetWarehouseOverride?id=[tenantId]",
    type: "POST",
    contentType: "application/json",
    headers: { "Authorization": "Bearer " + sessionId" }
    dataType: "json",
    data: JSON.stringify(warehouseOverrideData)
});

Note
Passing the session ID in an authorization header requires Dundas BI version 10 or higher. In earlier versions, you can pass it as another query string parameter in the URL: "...&sessionId=" + sessionId

2.4. Migrate tenant data using the command line

The dt command line tool is used as shown below to migrate the tenant data. You can choose either to create the tenant warehouse database manually using your database administration tools and then run the migrateTenantData command to migrate tenant data to the warehouse database, or you can just run the migrateTenantData command on the command line to both create the warehouse database and migrate tenant data to it.

Ensure that the connection string used here has the same server and database as the connection string used for the tenant warehouse override and its credentials need to have the appropriate privileges to create and/or instantiate the database and migrate data from your global warehouse to it.

dt migrateTenantData /tenantId:[tenant ID] /direction:toTenantWh /tenantWhCs:[connection string to the tenant warehouse DB]

2.5. Enable the tenant

Access Administration from the main menu, expand the SaaS / Multi-Tenancy section, and click Tenants.

In the Tenants screen, select a tenant from the list and click Edit on the toolbar.

In the Tenant Details dialog select the Enabled check box.

3. Remove a dedicated tenant warehouse database

Removing a tenant database and migrating the tenant data back to the global warehouse database involves the following steps.

3.1. Disable the tenant

Before removing a dedicated tenant warehouse database you must disable the tenant.

3.2. Remove the tenant warehouse override using the API

You can remove a tenant warehouse override using either the .NET API or the REST API. The examples below follow from the ones earlier that added overrides.

In .NET:

// get tenant
var mtSvc = Engine.Current.GetService<IMultiTenancyService>();
Tenant tenant = mtSvc.GetTenant(/*pass tenant ID here*/);

// remove tenant warehouse override
tenant.RemoveWarehouseOverride(AppSettingScope.Global, null);
mtSvc.SaveTenant(tenant);

Using REST:

var warehouseOverrideRemoveData = {
    scope: "[scope]",
    scopeTarget: "[scopeTarget]"
};
var jqXHR = $.ajax({
    url: "/api/tenant/RemoveWarehouseOverride?id=[tenantId]",
    type: "POST",
    contentType: "application/json",
    headers: { "Authorization": "Bearer " + sessionId" }
    dataType: "json",
    data: JSON.stringify(warehouseOverrideRemoveData)
});

3.3. Migrate tenant data back to the global warehouse DB using the command line

Use the dt command below to migrate tenant data back to the global warehouse database.

Ensure that the connection string used here has the same server and database as the connection string that was used when setting up the tenant warehouse override. The credentials must also have the appropriate privileges to migrate data from your tenant warehouse to the global warehouse, and to remove the the tenant warehouse database if you use the dropTenantWh argument.

dt migrateTenantData /tenantId:[tenant ID] /direction:toGlobalWh /tenantWhCs:[connection string to the tenant warehouse DB]

3.4. Enable the tenant

Re-enable the tenant as described above.

4. Important notes

  • Tenant warehouse overrides are not transferred by export/import. If they are required, they should be created independently in each instance.
    • If both source and target instances have tenant warehouse databases, tenant data will be transferred from the source tenant warehouse database to the target tenant warehouse database.
    • If only the source instance has tenant warehouse database, the tenant data will be transferred from the source tenant warehouse database to the target global warehouse database.
    • If only the target instance has tenant warehouse database, tenant data will be transferred from the global warehouse database to the target tenant warehouse database.
  • It is recommended to include the tenant name or ID into the name of your tenant warehouse database for maintenance purposes.
  • The tenant ID is stored in the GlobalProperty table of the tenant warehouse database, under the TenantId property name. This ID is also used to validate the migration processes.
  • Processes such as upgrade or health checks that are applicable to the global warehouse database, will be applied to all existing tenant warehouse databases. Note that even though the process mimics the transactional behavior, integrity across multiple warehouse databases is not guaranteed. For example, the upgrade can succeed for the global warehouse database but could fail for tenant warehouse databases.
  • The TenantWarehouseOverride is active only if a matching scope and scope target is stored in the configuration settings. When tenant data is processed, the app tries to find TenantWarehouseOverride matching the effective application scope and scope target. If not found, the application tries to use the TenantWarehouseOverride created for AppSettingScope.Global, and if that is not available, the global warehouse database will be used.

5. See also

Dundas Data Visualization, Inc.
400-15 Gervais Drive
Toronto, ON, Canada
M3C 1Y8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours:
Phone: 9am-6pm, ET, Mon-Fri
Email: 7am-6pm, ET, Mon-Fri