This article relates to Client Source Substitution, which you can read about on our Wiki here.
After setting up Client Organisations and Data Source Substitution, there may appear to be an issue with the population of the SQL to query report data, specifically when using schemas within a single database. The original schema (from the default org) is used even if you point to a client org to a different schema. This is intended behavior, however, it does rely on some kind of default schema scope being defined on the datasource.
As views can contain references to tables from multiple schemas, we don't replace the schema attached to the tables.
Generally, if you want to use a View on different datasources (in different schemas), then you will create your master view without Schema references. This allows it to be portable between datasources/schema.
Selecting the Schema in the Yellowfin Data Source does not restrict database select scope to that schema. It only restricts the tables returned in the View builder, so for this to work you need to make sure that the schema-less table references resolve to the correct tables. This function is database specific. Some databases allow you to specify a default schema as a parameter on the JDBC URL, or a user can have a default schema.
For example, this type of functionality has been implemented with Postgres, and the schema scope is defined by Default Schema setting for a user.
In this case, the Primary Org will have connection to a Postgres database with user "admin". A view with the following SQL:
SELECT * FROM MyTable
will select data from "admin"."MyTable".
At the Client Org, if we create a connection to the same datasource with User "test", with a default schema of "test", then when this SQL is run at the Client Org, it actually selects data from "test"."MyTable".
For a couple other DB examples, in SQL Server this can be done with the ALTER USER command to define the default schema for a user. https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-user-transact-sql?view=sql-server-ver15
In Yellowfin's Exasol connection wizard, it is possible to set the Default Schema, but we would need to check that this provides the functionality required for this.. Alternatively, an exasol user can be granted access to only a single schema, rather than all schemas.
As such, this can be resolved by understanding how to set the default schema for user or connection in the source database, and use SQL that does not contain schema references.
For a slightly more detailed look, here is an example of how this has been setup with SQL Server.
This SQL sets up 3 users with access to 3 schemas, and creates a basic table with different data in each schema:
(The important part here is the DEFAULT_SCHEMA for each user created)
Connection at the Primary Org and Client Org:
Of course, a Client Source Substitution link is also setup on the Primary Org source to the Client source.
A View is created at the Primary Org with the example table, and a report is created at the Primary Org against that view.
When the report is run at the Primary Org and Client Org the data is sourced from the different schemas:
(Note that the SQL is the same in both cases, and the data is different)
