Dynamic Field Names
This project shows a sample implementation of the Metadata Translator plugin, in order to show field names specific to the end user. In this use case, the end users belong to different companies, they are logging into their Candidate Recruitment software in order to track the progress of potential job candidates. Each company is allowed to create their own custom attributes to better describe their candidates, and they want to have reporting that reflects these custom attributes.
In order to achieve this, there are a few components
Transforming the Data: The data is stored in the database within two tables, with the following structure
customFields
candidateCustomFields
In order to work with this data, we need to pull in two tables into the Elasticube with the following structures.
Candidate Custom Fields
Custom Fields
The data can be transformed into this structure in many ways, depending on the source database. Since this example uses MySQL, there are stored procedures that use the GROUP_CONCAT() function to pivot the rows of data into columns. Check out the mysql-dump.sql file for details on how this works.
Building the Elasticube: Now that we have access to the data in the proper format, we can import it into the Elasticube. Continuing our assumption of MySQL, start by pulling in the regular tables associated with your dataset. Next, pull in the datasets generated by the stored procedures. In order to get this data into Sisense, pick any table from the list of available tables and just override the SQL with something like the following:
call sp_my_stored_procedure();This will tell Sisense to execute the stored procedure during each build, and populate a table from the results. The end result should look similar to the included screenshot (elasticube.png). The important part here is that the attribute table (Candidate Custom Fields) has generic column names like c1, c2, c3, etc.
Data Security - Our Elasticube has data from all companies, so we need to limit what data is visible on a per-company basis. You can use Sisense's standard row level security to lock down the data. Make sure to add data security rules for the company table's companyId field.
Metadata Plugin - Now that we have an elasticube with those generic column names, we need to translate them on the fly based on the logged in user. The attached plugin (the metadata directory), should be copied to your C:\program files\Sisense\PrismWeb\Plugins directory. This plugin is configured to run when the application is first loading, and make an Elasticube query for the contents of the Custom Fields table. Once we have these results, it creates a metadata mapping which replaces the default column names (c1 -> Site, c2 -> Years Experience, etc).
Now when we create dashboards, we can share them with lots of users from different companies and they will see the field names of their specific attributes.
Dashboard from Company A's perspective
Dashboard from Company B's perspective
This sample has been confirmed working on Sisense version 6.7.1, and should be backwards compatible with the previous version.
Download: Attachments
