Introduction: Helical Insight metadata connects with a database which is predefined. Now there might be certain cases in which we would like the metadata to connect to different data sources based on certain condition.
For example : In healthcare industry the data of the hospital can not go outside the hospital premises. In that case, based on which hospital user has logged in, the metadata should connect to a different physical database.
Note: This solution will work if the DBName, table name, column name and schema is same, even though the physical server, DB credentials etc could be different.
We can switch database based on logged-in username, organization , role and profiles.
Switching the database based on logged-in user name :
In this example we have two mysql databases having names : hiadmin, hiuser
having common table : userdetails
Step1 : Create managed connection for both databases :
Once the Test Connection is successful then Save Datasource
Once the Test Connection is successful then Save Datasource
Once the connection id will be assigned dynamically. In above example 1001 and 1002 are connection id’s.
Step2 : Open Advanced option in data source module and click on “Groovy Managed JdbcDataSource”
Step3 : If you create new connection, it will open a place holder with a sample groovy code for the reference
(We need to write/ change the logic for switch database based on username)
Step4 : Updated sample code logic to switch the database based on username
import groovy.sql.Sql;
import net.sf.json.JSONObject;
import com.helicalinsight.adhoc.metadata.GroovyUsersSession;
public JSONObjectevalCondition() {
JSONObjectresponseJson = new JSONObject();
String userName = GroovyUsersSession.getValue('${user}.name');
userName = userName.replaceAll("'", "");
if (userName.equals("hiadmin")) {
responseJson.put("globalId", 1001);
}
//below is the example - please put the globalId of managed connection as per requirement
if (userName.equals("hiuser")) {
responseJson.put("globalId", 1002);
}
responseJson.put("type", "global.jdbc");
//throw new RuntimeException("This is a test exception" +responseJson);
return responseJson;
}
Additional Information of the groovy code :
String userName = GroovyUsersSession.getValue('${user}.name');
This code will give us value of currently logged-in username
String orgName = GroovyUsersSession.getValue('${org}.name');
This code will give us value of currently logged-in user organization name
String roleName = GroovyUsersSession.getValue('${role}.name');
This code will give us value of currently logged-in user role
String profileValue = GroovyUsersSession.getValue('${profile[\'ProfileName\']}')
This code will give us value of currently logged-in username profile value.
responseJson.put(“globalId”, 1002) – this will set the data source connection mapping to use that specific connection which we created in manged connection.
We can use any of the above parameters like username, org name , roles and profile values for mapping required data source connection ID
Step5 :Provide updated groovy code in place holder and provide Datasouce Name then click on “Browse” option
Create a new folder by clicking below highlighted icon
Select Use This Folder
Click on Test Connection
Once the it is successful then “Save Datasource”
Step 6 : Create metadata
Remove the Catalog Name and save the metadata ( it should be changed dynamically )
Save the metadata in a separate folder
Step7 : Create a report using the metadata , since we are creating report with hiadmin user
So the data connection pointed to hiadmin database and giving the result as expected
Step8 : we need to check the same report with “hiuser” also in order to check if database is switching based on user name or not
For that we should share reports and metadata with that specific user
Step9 : Login with hiuser and open the report
The data is changed by switching the connection based on username. Hence the database switching is worked fine.