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.
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.
- 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.
NOTE :
– With the version 4.1.1 GA , we have added a new feature “pagination”. On to the left side where all the data sources will be present , when you click on a datasources , the schema will be loaded which contains all the tables.
– From the version 4.1.1 GA onwards, the tables will be loaded page wise as you can see in the below image , there is a pagination provide which is highlighted (yellow portion). Clicking on that icon will load the next set of tables. This functionality is helpful when there are a lot of tables even then the browser page will not hang.
– When you click on that the next page of the tables will be loaded (ex :50 tables in every page) and in the below you can also see the selected tables
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.
NOTE : From version 4.1.1 GA , when we add tables to the metadata , pagination is provided here also. Here you can view 50 tables per page and you can go to the further pages and also to the back pages with the pagination option given . This helps in faster performance
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).
– You can also select the number of joins that you want to see in a single page. At the top right you can see ( Showing page 1/43,10 per page) .Click on that box and you can select number of joins that you wish to view in a page.
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
Note: If the number of tables and columns in your database is huge, then the metadata page automatically becomes really huge and heavy it may cause the browser memory to be full. This in turn will slow down the page and might even cause crashing of the browser page. In order to overcome this, you could implement the following:
1. At the database level, create a user with access to only limited number of tables depending on the reporting requirements, then use this specific user to connect to the database at helical insight datasources layer. Automatically, the performance at metadata will improve due to limited number of tables.
2. Second option is on the datasources page, you can limit how much information gets displayed at the metadata layer (a sort of prefiltering). This can be done by going to “Advanced” option in the datasource connection.
In the “Other Options” placeholder, you can add restricting strings such as below:
?HI_SCHEMA_CONTAINS=hi (this will show only the schema names that contain the string hi)
?HI_CATALOG_CONTAINS=t (this will show only the catalog names that contain t)
?HI_CATALOG=travel_data (this will show only travel_data catalog)
Similar strings and regular expression (regex) can be used to restrict the database tables, schema, cataglos that are visible at the metadata level.
So with above method you can initially restrict the size of listing on the metadata page so that it is fast and make initial metadata. Then you can edit the already created datasource to include other table/schema (using the above mentioned method), then edit the metadata created earlier (refresh the cache) and then select additional tables required and make it part of the metadata by using the merge option. This way your create-metadata browser page will never be so heavy and will work fine.
For more info, contact us at support@helicalinsight.com