Dynamic switching of JSON file

Dashboard Designer
#1

Hello Helical Insight,

Every user is having a JSON file of his name. So what we want is a the report dashboard and metadata should change dynamically Here in we are covering a use case of how based on logeedin username the datasource changes dynamically to other JSON file.

Thank You,
Kaji.

#2

Hello Kaji,

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, different organization etc. Here we have explained how the metadata is connected to different data sources based on logged in organization or user.

Using Plain Groovy JDBC Connection.

Install apache drill 1.14, Enable middleware, so that JSON icon option appears

b. Save all the JSON files at the system location of helicalinsight installation.

  1. Go to the location “..\hi\hi-repository” and create a new folder here from the backend winscp. Then you have to create an EFWD file and save it in this newly created folder. On step 2 EFWD file content is specified. The idea of creating it from the backend is so that the folder is public and it does not explicitly require sharing.

  2. Copy the code in the file and save.

    com.helicalinsight.json hiadmin.json

  3. In the above code based on the loggedin user connection to the JSON will automatically switch / change.

  4. Create metadata using the plaingroovy jdbc connection the way you create normally. For example in EFWD code we can see connectionid given is 4, and in the below image we can infront of JSON connection id 4 is mentioned. From that create a normal metadata and save it.

image

  1. Now the metadata which you have saved, open the physical file of that metadata in any text editor. You can find the path of the metadata by going to home, clicking on file browser, navigating to metadata file, right clicking on the metadata, going to properties. Then you can see the metadata file name and the path.

image

  1. When that metadata file is open then changes at 2 places are required.
    Line no 14 tablename and alias is written. Please remove it and replace it with some pattern. For example we have JSON file name which is same as the username who is loggin in. So in that case we will replace it with #username# and on line no 7 replace the dialect with below line

<dialect>com.helicalinsight.adhoc.genericsql.AapacheDrillSqlDialect</dialect>
Save this file. Admin permission might be required for making changes and save.

  1. Now go to the following location and open JS file of the database you are using. In our case you will open drill.js file.
    Location: \hi\hi-repository\System\Admin\SqlFunctions
    Add below 2 lines at Line 21 and 22

     *var userName = obj.userName;*

 *query = query.split("#username#").join(userName+".json");*

  2. Now you can create various users. In this example the username is the name of the JSON files.

  3. At the adhoc level when you are creating report make sure that you have removed “prepend table name” using the settings option.
    image

  4. when the reports are created the metadata, as we have made changes in dialect file of drill, the #username# will get replaced with the current loggedin user, and hence the metadata will point to that specific JSON file dynamically. Hence even if the report dashboard metadata etc are remaining the same the JSON file is getting changed dynamically based on the loggedin user. Also please share the folder within which reports dashboards metadata with other users so that they when they login they can see the same in file browser. Refer to this link to learn more about sharing Sharing Reports in Helical Insight - Helical Insight

In the current example we are replacing with simple username, in a similar way organization name, profilename’s values etc can also be used.

Thank You,
Helical Insight.