Client Source Substitution is a mechanism for using a separate database for each Client Organization when viewing shared content from the Primary Organization. This allows a single copy of reports and dashboards to be consumed by each client with their own data, with minimal configuration.
A common scenario is that a new Tenant is onboarded into a Yellowfin instance, and reporting against their data needs to be enabled. The process involves several steps:
- A datasource needs to be created at the Client Organization.
- The new Client Datasource needs to be linked to the Primary Organization datasource on which all the shared content has been created.
There are two approaches for creating a new datasource at a Client Organization. A datasource can be created for the client by importing a copy of an existing datasource. There is also an end-point for creating a datasource directly.
Once the datasource is created, there is a single end-point to associate the Client Datasource with the Primary Organization’s datasource.
Create datasource via Import Clone
One approach to creating a new datasource for a new client organization is to clone an existing datasource, and change the connection details for a new connection. This can be done by exporting an existing datasource, altering the export file itself, and then reimporting it into Yellowfin.
When using Source Substitution, there should already be a placeholder datasource at the Primary Organization, to which all reporting content is attached. This datasource can be exported via the Export UI within Yellowfin. This will result in a YFX file being exported from Yellowfin.
YFX files are actually archive (zip) files that can be decompressed. Within the YFX there will be a ExportListHierarchy.json file and a YFExport.xml file. The YFExport.xml file contains a readable version of the exported datasource.
Within the YFExport.xml file the database connection settings can be changed. If the process of creating tenant data sources for source substitution is to be automated, then the contents of the YFExport.xml file could be used as a template, with tokens added where connection specific data needs to be injected.
The following extract from a YFExport.xml file shows in red where tokens could be replaced by an automated process for generating a datasource for use with client source substitution.
<source> <id>132641</id> <sourceName>PostgreSQL Connection for [TENANT_NAME]</sourceName> <sourceDescription>PostgreSQL Connection for [TENANT_NAME]</sourceDescription> <databasePath>public</databasePath> <userName>[USERNAME]</userName> <password>[ENCRYPTED_PASSWORD]</password> <connectionTypeCode>GENERICUSER</connectionTypeCode> <connectionMethodCode>JDBC</connectionMethodCode> <connectionDriver>org.postgresql.Driver</connectionDriver> <databaseURL>jdbc:postgresql://[SERVER_ADDRESS]:5432/[DATABASE_NAME]></databaseURL> <databaseTypeCode>POSTGRESQL</databaseTypeCode> <minConnections>1</minConnections> <maxConnections>5</maxConnections> <connectionRefreshTime>180</connectionRefreshTime> <connectionTimeOut>180</connectionTimeOut> <accessCode>UNSECURE</accessCode> <maxRowCount>10000</maxRowCount> <maxAnalysisRowCount>0</maxAnalysisRowCount> <broadcastPermitted>true</broadcastPermitted> <subscribePermitted>true</subscribePermitted> <dataProfileEnabled>true</dataProfileEnabled> <localTimezoneCode>AUSTRALIA/LORD_HOWE</localTimezoneCode> <secondaryMinConnections>0</secondaryMinConnections> <secondaryMaxConnections>0</secondaryMaxConnections> <secondaryConnectionRefreshTime>0</secondaryConnectionRefreshTime> <secondaryConnectionTimeOut>0</secondaryConnectionTimeOut> <lastModifiedGMTDateTime>20240607025556.000000</lastModifiedGMTDateTime> <sourceUUID>7d7543c0-e9c7-4a80-ab14-73181e4d0694</sourceUUID> <filterList/> <sourceParameterList> <sourceParameter> <id>0</id> <parameterTypeCode>TEXT</parameterTypeCode> <parameterKey>DATABASE</parameterKey> <parameterIndex>0</parameterIndex> <parameterValue>[DATABASE_NAME]</parameterValue> <documentId>0</documentId> <userVisible>true</userVisible> </sourceParameter> <sourceParameter> <id>0</id> <parameterTypeCode>TEXT</parameterTypeCode> <parameterKey>HOSTNAME</parameterKey> <parameterIndex>0</parameterIndex> <parameterValue>[SERVER_ADDRESS]</parameterValue> <documentId>0</documentId> <userVisible>true</userVisible> </sourceParameter>
Once an export file has been automatically injected with tokens, it can be uploaded via the REST API using the POST /api/rpc/import-export/import-content end-point (https://developers.yellowfinbi.com/dev/api-docs/current/#operation/importContent ).
This particular endpoint uses a form based submission paradigm. Two form bodies are required - one named “contentToProcess” with the YFX/XML file contents and another named “importOptions”, which contains the rules on how the import is processed. For this example, assuming the XML file contains a single datasource, this importOptions payload can be used:
[
{ "itemIndex": 0, "optionKey": "SKIP", "optionValue": false },
{ "itemIndex": 0, "optionKey": "OPTION\", "optionValue": "ADD" }
]
The following code examples take a pre-exported database XML template and inject replacement values for the database host, database name, database user and password into the file. It is then imported into a Tenant (specified by the Client Reference Id).
Create a new datasource via the datasource service
A datasource can be created directly via the REST API using the POST /api/data-sources end-point, Create Data Source.
This takes a datasource model of this format:
{
"sourceName": "Client Database",
"sourceDescription": "",
"sourceType": "POSTGRESQL",
"connectionType": "JDBC",
"connectionTypeCode": "GENERICUSER",
"connectionDriver": "org.postgresql.Driver",
"connectionString": "jdbc:postgresql://192.168.1.100:5432/testdata",
"connectionTimeout": 180,
"userName": "postgres",
"minimumConnections": 1,
"maximumConnections": 5,
"refreshTime": 180,
"timezone": "AUSTRALIA/SYDNEY",
"accessLevelCode": "UNSECURE",
"maxRows": 10000,
"maxAnalysisRows": 0,
"inheritChildSourceFilters": false,
"sourceLogIndicator": false,
"sourceOptions": [
{
"optionKey": "ISOLATIONLEVEL",
"optionValue": "1.0",
"valueDataType": "1"
},
{
"optionKey": "USESCHEMA",
"optionValue": "true",
"valueDataType": "6"
},
{
"optionKey": "HOSTNAME",
"optionValue": "192.168.1.100",
"valueDataType": "2"
},
{
"optionKey": "PORT",
"optionValue": "5432",
"valueDataType": "1"
},
{
"optionKey": "DATABASE",
"optionValue": "testdata",
"valueDataType": "2"
},
{
"optionKey": "YF_DRIVER_SELECTION",
"optionValue": "org.postgresql.Driver",
"valueDataType": "2"
}
]
}
The main body of the model is similar to the model returned by the GET /api/data-sources end-point. If creating a new source similar to the Primary Organization datasource, the data returned by GET /api/data-sources can be used as a template.
The sourceOptions in the creation model differ depending on the database type and are representative of the options that are exposed by the connection wizard when connecting to a new datasource.
It should be noted that although the connectionString is available in the model body, this is regenerated based on the source options when the datasource is created.
The following examples illustrate how to create a new datasource via the POST /api/data-sources end-point:
List Data Sources
A list of datasources can be retrieved with the GET /api/data-sources end-point (https://developers.yellowfinbi.com/dev/api-docs/current/#operation/getAllDataSources ). This will return a datasource model of the following form:
{
"sourceId": 132780,
"sourceName": "PostgreSQL Connection for NEWCLIENT",
"sourceDescription": "PostgreSQL Connection for NEWCLIENT",
"sourceType": "POSTGRESQL",
"connectionType": "JDBC",
"connectionTypeCode": "GENERICUSER",
"connectionDriver": "org.postgresql.Driver",
"connectionPath": "public",
"connectionString": "jdbc:postgresql://localhost:5432/testdata",
"connectionTimeout": 180,
"userName": "postgres",
"minimumConnections": 1,
"maximumConnections": 5,
"refreshTime": 180,
"timezone": "AUSTRALIA/LORD_HOWE",
"accessLevelCode": "UNSECURE",
"maxRows": 10000,
"maxAnalysisRows": 0,
"inheritChildSourceFilters": false,
"sourceLogIndicator": false
}
This service can be used to find the internal integer identifier for the datasource (sourceId) for a given data source name.
The following code examples illustrate how to retrieve a list of data sources from the system:
Java | C# | Go | JavaScript | PHP | Python
Attach a Client Datasource to a Primary Org Datasource for Source Substitution
A Client Datasource can be linked to a Primary Org Datasource (for use with Source Substitution) using the POST /api/data-sources/{primaryOrgSourceId}/client-data-sources/?clientSourceId={clientOrgSourceId} end-point (https://developers.yellowfinbi.com/dev/api-docs/current/#operation/addClientDataSource ).
Where {primaryOrgSourceId} is the integer identifier for the primary organization datasource, and {clientOrgSourceId} is the integer identifier for the client org datasource to link.
The following examples illustrate how to link a Client Datasource (identified by its name) to a Primary Org Datasource (identified by its name):