416.467.9100 | Dundas Data Visualization | Login
Welcome Guest
This is the support page for the legacy Dundas Dashboard application. For assistance with the current Dundas BI application please click here.
Dashboard v5.0

This site makes extensive use of JavaScript.

Please enable JavaScript in your web browser and reload the page before proceeding.

Using Dynamic Connection Strings

Hide navigation
RSS
Modified on Fri, 23 May 2014 03:17 PM Categorized as Data Connectors, Level-Advanced, Level-Expert
Click to return to: Documentation | Data Connectors


Overview

Dynamic connection strings make it easier to provide SaaS (software-as-a-service) functionality by allowing the same set of dashboards to be reused across multiple tenants or users. The underlying connection strings of data connectors are switched dynamically at viewing-time depending on the tenant/user that is logged in to Dundas Dashboard.

Background

Prior to Dundas Dashboard 4.0, a typical way to implement SaaS functionality for your tenants was to create a master Dundas Dashboard project containing a set of dashboards for viewing. For each specific tenant, a copy of the master project was made and the data connectors in the copied projects were modified to point to the appropriate data sources for each tenant. While this approach works, it is difficult to maintain as any change to the master project (such as modifying some dashboards) needs to be reflected in each of the copied projects as well.

In Dundas Dashboard 4.0 or later, you can use dynamic connection strings which enable a single Dundas Dashboard instance to host the same set of dashboards for multiple users or tenants. In this scenario, each tenant requires access to data sources that have the same structure (&ie; schema) but which may be located physically on different servers. When a user logs in to view a dashboard, the underlying data source is switched automatically so that the user sees a dashboard that is powered by the applicable data.

Example

The following is a basic example that shows how to use dynamic connection strings.

Set up data sources for each tenant

Create a SQL Server database with a single table as follows:

  • Database name: CompanyA
  • Table name: Products
    • Column: Product, nvarchar(50)
    • Column: Price, Decimal(18,2)

Products table for Company A.

Products table for Company A.


Create a second SQL Server database for Company B with the exact same schema (but different data) as above.

Products table for Company B.

Products table for Company B.


Create a new project and dashboard

Design a dashboard that will be accessible to all tenants:

  1. Create a new dashboard project called Project1.
  2. Create a data connector for the CompanyA database using the SQL Server provider. Name this data connector as TenantDC.
  3. Create a standard virtual table ProductsVT from the dbo.Products table.
  4. Create a KPI based on the ProductsVT virtual table:
    • Name: ProductPrice
    • Measure: Price
    • Instant dimension: Product
  5. Create a new dashboard, Dashboard1. Drag the ProductPrice KPI to the canvas to show a bar chart.
  6. Save and then view the resulting dashboard.

The dashboard displays data for Company A only for now.

View the completed dashboard.

View the completed dashboard.


Configure Dundas Dashboard

The dynamic connection string feature is based on the use of custom attributes, which are key-value pairs of information that Dundas Dashboard administrators can associate with individual user accounts or security groups.

Specifically, you need to use custom attributes to associate a tenant identifier and a tenant connection string value with each user account. But before you can do this, the names of these two custom attributes must be configured by modifying the Dundas Dashboard Configuration File.

Uncomment and set two keys in the configuration file as follows (example):





You must restart the Dundas Dashboard website afterwards for these changes to take effect.

Set up user accounts with custom attributes

Create a new user account that will be able to view data for Company A:

  1. Enter the user information in the General tab, and then click Save.
    Add a user for Company A.
  2. Click the Custom Attributes tab.
  3. Click New Attribute.
  4. In the Name box, type TENANTID. This is the attribute name that was specified in the configuration file.
  5. Click the Generate GUID value button next to the Value box to generate a tenant ID. Note that this ID must be a valid GUID; it cannot be some arbitrary string.
    Generate GUID value.
  6. Click OK to add the custom attribute.

Add a second custom attribute to specify the connection string for this tenant (Company A):

  1. Click New Attribute.
  2. In the Name box, type TENANTCONNECTIONSTRING. This is the attribute name that was specified in the configuration file.
  3. Click the Load existing data connectors button next to the Value box. The dialog expands to show a list of Dundas Dashboard projects and corresponding data connectors.
  4. Locate the TenantDC data connector and enter its Connection string override. This is the connection string needed to access the Company A database. An example string override is:
    Integrated Security=SSPI;Data Source=SERVER1\SQLEXPRESS2008R2;Initial Catalog=CompanyA;

    Enter connection string.
  5. Click OK. The user is now configured with the two custom attributes. Note that all other user accounts from Company A must be given the exact same custom attribute values (&ie; for these users there is no need to generate a new tenant identifier). The value of the tenant connection string attribute has a specific format which is described here.
    Tenant identifier and connection string attributes.
  6. Click Save.

Next, create a second user account for viewing data from Company B. Follow the exact same steps as above, including generating a new tenant identifier (GUID). But use the appropriate connection string to access the database for Company B. Here is an example string override:

Integrated Security=SSPI;Data Source=SERVER1\SQLEXPRESS2008R2;Initial Catalog=CompanyB;

When specifying the connection string override, it is not necessary to include the full connection string if you only want to customize a portion of it. An example of a partial override:

Initial Catalog=CompanyB

Tip: You can add the tenant-related custom attributes on a security group instead of a user account. Users belonging to that security group will automatically inherit the custom attributes from the group. This saves you from having to edit every single user account.

View dashboards

Log in as the first user and view Dashboard1. Observe that the chart displays data from the Company A database.

Dashboard1 showing data from CompanyA database.

Dashboard1 showing data from CompanyA database.


Log in as the second user and view Dashboard1. Observe that the chart displays data from the Company B database.

Dashboard1 showing data from CompanyB database.

Dashboard1 showing data from CompanyB database.


Besides viewing dashboards, the tenant connection string is also used when you test a connection or perform a discovery operation. The data connector settings in the General tab (⪚ server name, credentials) are not used in this case.

Notes

Limitations

Sync database caching cannot be used in conjunction with dynamic connection strings.

Tenant connection string format

As shown in the example, the value of the tenant connection string attribute is constructed for you automatically based on your data connector selection and the connection string override for that data connector.

However, if you are working with the Account Services API and need to add the custom attributes programmatically, you need to know the format of the attribute value.

In the case of a single data connector, the attribute value has this format:

<DD_DC:GUID>override

Where:

  • GUID is the Object ID of the data connector (which you can get from the Properties dialog)
  • override is your connection string override

Tenant connection string attribute value has a specific format.

Tenant connection string attribute value has a specific format.


In the case of multiple data connectors you simply append a similarly formatted value for each data connector. For example, here is the format for two data connectors:

<DD_DC:GUID1>override1<DD_DC:GUID2>override2

Below is example code for adding custom attributes via the API:

ddAccount.CustomAttributes.Add("TENANTCONNECTIONSTRING", "Initial Catalog=CompanyD;"); ddAccount.CustomAttributes.Add("TENANTID", "3c3791c5-8af2-4e78-9edb-925b06ccd95");

Related topics


Click to return to: Documentation | Data Connectors

About Dundas | Contact Us Follow us on Twitter! | Privacy Statement | Report Site Issues

Copyright © 2009-2014 Dundas Data Visualization, Inc.