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. This list is already cached. You can abort the operation by clicking on the line that appears below the catalog/schema name while loading the table list.
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.
- 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.
- Clicking on Refresh will delete the existing cache entry and prepare a new cache for the connection. Due to this, some time may be taken for loading the tables.
Step 5: By default, none of the tables and associated columns are selected. Select the tables you are interested to keep in the metadata. It is always advisable to keep only the limited tables in the metadata which are 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 tables are added to the metadata, on clicking on a particular table name, the columns within that table will be loaded. This operation can be aborted by clicking on the line that appears below the table name while loading.
You can right-click on the table/column names 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 a view named My 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.
Note: Save Metadata creates a copy of the tables and columns in the cache. Hence, saving metadata may take some time.
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
To edit the metadata we have two methods:
Method 1: Directly from the file browser we have to navigate to the folder wherever we have saved the metadata, by right-clicking, we will get an option called “Edit Metadata” click on that and edit the metadata.
Method 2: From the top ribbon you can directly click on the “Metadata” tab that will redirect us to the metadata page. Once on the metadata-create page click on “Edit” which will open the file browser and from there we have to navigate to the folder where we have saved the metadata either double-click on the metadata or we can right click and edit the metadata.
Changing the Data Source
Step 1: In order to change the data source that the metadata is pointing to, click on Change Data Source in the Info section. You will be asked to select the data source.
Step 2:Select the type of connection.
Step 3:Select the data source from the connection list.
You can also type the schema and catalog names by entering the relevant names in the boxes provided.
Adding Tables to Metadata
Select the tables that you want to add to metadata. Then right click and select Add to Metadata.
Select whether you want to Merge or Reload.
Merge will append the new tables to the list of existing tables in the Metadata.
Reload will delete the original tables and load the tables that have been selected.
After adding new tables, on clicking on Joins, there is again a prompt to select Merge or Reload.
Merge will add the new joins to the list of existing joins in the Metadata.
Reload will delete the original joins and load the new joins according to the tables that are part of the Metadata.
For more information you can email on firstname.lastname@example.org