Metadata is basically a logical DB schema which gets stored locally in Helical Insight server (as a .metadata extension) and allows users to define which tables, columns they would want to keep. Thus, different Metadata can be created for the different set of users like finance metadata, sales metadata, operations metadata which then can be shared with the relevant set of people. This will not only increase the performance but also help users to see only relevant tables and columns they are interested in, thus aiding in ease of usage.
Metadata further also allows users to define/edit “Joins” (over and above present in the database which can also be edited at metadata level), give “Alias” name for the tables and column and apply “Data security”.
To create a metadata, a user has to create a data source initially. Without a data source, metadata cannot be created. There are two methods of creating metadata.
Method 1: Once the data source is created then on the “view” tab you can see the listings of the data source for that specific database. You can click on “Create Metadata” icon and you will get navigated to create-metadata page.
Method 2: You can click on the “Metadata” button on the top ribbon. Then it will navigate to created metadata page.
Step 2: After you have finished first step (using either method 1 or method 2) you will be on the metadata page and on the left side you can see listings of the data sources created by you or shared with you. If the datasource has been shared with you as “Execute Only” permission then it will not appear here in this list. Navigate to the required data source and click on it.
Step 3: You can see the list of catalogs and schemas present. Select the required schema.
Step 4: Now you can click on the catalog and see the list of tables present in that catalog. Double clicking on the table will also display the list of columns present in that table.
Search:There is a search option available which also supports wild character (regex) search which can also be used for quicker navigation.
More Functions: Clicking on the three dots allows you certain additional functionalities like to refresh the connection (especially useful if meanwhile more tables and columns have been added), edit the data source.
Note: If the data source connection credentials have been changed at the data source page and the application is not able to make the connection to the data source it will appear as red.
Step 5: By default, all the tables and associated columns are selected. Right click and press “Reset”. Then select the tables and columns you are interested to keep in the metadata. It is always advisable to keep limited tables and columns in the metadata which are only required for reporting purposes. You can also create multiple metadata’s for different set of roles/functional requirements.
Once the selections are made you can either drag into the second selection interface or right click and use the option “Add to Metadata”. The selected tables and columns will appear like the below.
Step 6: Once the selections are made then you can right-click in the second column and perform certain operations.
The first operation is “Alias”. You can rename the tables and columns in order to give it a simpler name (This is especially crucial since the metadata might be used by non-tech users for reports creation). You can rename any table/column this way.
The second operation is “Duplicate”. You can make a duplicate of a table or column by this operation. Name of the duplicate table/column is the same as the original table/column with an additional _1 added to the file name. This can be renamed as explained above. Duplicate of a column appears in the same table with the nomenclature as described.
Note: Before you make a duplicate of already duplicated table/column it is important to save the metadata.
The third operation is “Remove”. This will allow you to remove any table/column from the metadata.
Naming the metadata file: You can double click on the metadata file name and give it another different file name.
Search: It is also having a search operation supported allowing wild character (regex) search as well of the tables and columns names.
Step 7: Info: Info tab allows you to change the metadata data source connection detail. For example, you have created metadata and reports from the development database, now you want the metadata to point to the production database. In that case, you can click on “Change Datasource” and change the metadata to now point to your production database.
Step 8: Joins: Based on the kind of Primary Keys and Foreign keys present at the database it will show you list of joins. In case if there is any joining condition which is present on any column which is not part of the metadata it will appear as highlighted red (as shown below). Those can be deleted. You can also use “Delete Invalid Joins” functionality present near the “Add” button.
You can delete the already existing joins by pressing on delete button. You could also change the join type as well.
Also you can add more joins by clicking on “Add” Button, for specifying the columns you can either drag the columns from the metadata selection panel into the selection box or merely type the name of a column and then select from the drop-down. The sequence of joins can also be changed by dragging them (present next to the selection checkbox).
Step 9: Views: In certain cases certain reports might require a lot of complex calculations. Hence in those cases “Views” can be used. Views allow you to write your own SQL queries and the output of that SQLQuery will be visible as a listing (view) along with tables and columns in the metadata. This view can also be used by an end user to create reports.
A metadata can also have multiple views. For example as shown in below image we have created an Events view.
Note: Whatever we define at the metadata level (as the alias, joins, views etc) is stored at the application level and it does not make any changes in the database level.
Step 10: Security: You can use the same metadata to create report/dashboard which will be shared with multiple users. In that case, we may want to make sure that even though same report/dashboard is shared with everyone they get to see their own data only. In those cases, metadata security conditions need to be implemented.
Table level, row level, and column level security conditions can be implemented over an organization, roles, user or profile.
To learn more about metadata security implementation click here
To learn metadata security conditions usage click here
Step 11: Saving the metadata: Once the metadata is created with all the joins, security conditions, views, naming etc then you can click on “Save”, the file browser will appear. You can save the metadata in a folder of your choice. By right clicking in file browser you can also create your own folder inside which you can save the metadata.
Please note that if your metadata is having any invalid joins or blank joins then it will throw appropriate message.
Step 12: Share: You can click on the share button to share the metadata. If you want to share a report with a user it is also essential to share the metadata from which the report is created.
In order to share the metadata it is important that the metadata is saved first.
Once you click on the share you will have the option of sharing the metadata with an individual user, a role or an organization also. Please note that you can only share with a role/user who is part of your organization and not outside the organization. Whereas a superadmin can share it across anyone.
While sharing you can also specify the permission level.
You are also having the option of sharing from the file browser also. Please refer to Sharing Metadata with Organization
For more information you can email on firstname.lastname@example.org