Data Catalog

Databases

A Data Catalog is an organized inventory of data assets in the organization. It uses metadata to collect, tag, and store the datasets.

A data catalog is essential to business users as it synthesizes details about data assets across multiple data dictionaries by organizing them into an easy-to-digest format.

In the OvalEdge application, the Data Catalog comprises Databases, Tables, Table Columns, Files, File Columns, Reports, Report Columns, and Queries.

The OvalEdge search engine is built primarily using the following components,

  • OvalEdge Crawler: It connects to various databases and crawls metadata and various statistics.
  • Human Curation: Users enhance various databases and document their descriptions and usage.
  • Relationships: OvalEdge establishes various kinds of relationships with other objects like lineage, entity relationships, pattern relationships, references, etc.
  • OvalEdge Search Algorithm: OvalEdge uses a proprietary algorithm to provide appropriate results based on various factors.

Databases

A Database is an organized collection of structured information or data, stored in a computer system.

In the OvalEdge application, a database consists of schemas. 

When a source database is crawled, the schemas which are saved in that particular database are fetched and displayed on the Data Catalog > Databases.

Database Flow chart

The Database provides information regarding the following:

Screenshot 1 Databases

Icons/Fields

Description 

The System View is the system-defined View displaying a specific set of defined column fields in the grid. Click on the System view to see a list of all three Views - My View (created by you), System View, and Public View (views created by other users). The application displays the System View by default if no other views or created or selected.

The Configure View icon provided in the top right corner helps you to customize the layout of the screen to view the information as per their requirement. By default, the display of the data is set to System View with predefined columns. To view particular columns, which are not included in the system view, you can add those required columns in the layout using the customize plugin option.

The function of the Reset icon is to clear all the filters applied on the Database page.

The Nine dots icon enabled in the top right helps you perform multiple actions to the selected data objects.

Apply filters to view selected options related to search results. 

Click on the search filter to search results based on entered keyword. Also, by clicking upon the search icon, you can apply condition operators by clicking on the dots icon.

Use the sorting icon to display results alphabetically from A-Z / Z-A.

Connector Name

A  Database is an organized collection of structured information or data stored in a computer system. In the OvalEdge application, a database consists of schemas.

Example: The databases available in the OvalEdge application, such as Oracle, Mysql, etc., are displayed here.

Schema

A database schema is a skeleton-like structure representing the entire database's logical view. The schema consists of the table, table column, etc. In the OvalEdge application, the schema column displays the schema name. Users can use the search filter to enter the schema name in the field to see relevant search results.
When you crawl a particular database, the available schemas are displayed here.

Title

Displays the title of the Databases. The title is an editable field. Hover over a specific title to see an edit icon in the Title Column. Click the Edit icon to edit and click the Save button to save.

Example: For the customerorder schema or table, the title's name is customerorder.

Tags

Display the Tags associated with the schema. The Tags field is editable; hover onto a specific tag field to see an edit icon. Click the edit icon to edit and assign tags to the object.

Term

Displays the Term associated with the schema. The Terms field is editable; hover over a specific tag field to see an edit icon. Click the edit icon to edit and assign or remove tags to the object.

Business Description

Displays details on what data the schema holds. It is editable; hover onto a specific object Business Description field, and click on the edit icon to edit the description.

Note: Users with Meta-write access can only edit the Business Descriptions.

Technical Description

Displays the technical parameters or comments defined at the data source. It is editable, hover over a specific object Technical Description field and click on the edit icon to edit the description.

Example: Details about the primary key, foreign key, field data type, and calculations are some of the technical parameters included in the Technical Description. 

Table Count

Displays the total number of tables present in the schema.

Row Count

Displays the total number of records in the Table.

Project Name/ Acess Cart

The projects associated with the data object are displayed in this column.

A Checked icon is displayed if the Database is added to the default project. Hover over the Checked icon to view the Project name. Click on the Add to Default Project icon to add the schema to the default project.

Note: If My access cart is selected as the default project, then the label name will be will be changed from Project to Access Cart, and you can raise an access request using the access cart icon.

Governance Roles

Displays the Governance Roles assigned to the Data Objects. 

Example: The assigned Data Owner, Data Custodian, Data Steward, GovernanceRole4, GovernanceRole5, and GovernanceRole6 for the Data Object is displayed.

User Actions

The following are the User Actions that can be performed on the Database using the Nine dots option.

Field

Description

Add to my WatchList

One, or multiple schema/s can be added to the My Watchlist to get regular alert notifications on any metadata and data changes.

When you add a data object to my watch list, the selected object will be available on the My Resources >  My Watchlist page. If a data object is watchlisted, you will be given options to notify of significant data changes or metadata changes from the source. If metadata changes are made at the application level user will be notified.

Remove from my Watchlist

One or multiple schema/s can be removed from the My Resources > My Watchlist.  It disables all the alerts.

Add Tag

Tags can be added to one/ more schema/s using this feature.

Remove Tag

Tags added to single/ multiple schema/s can be removed using this feature.

Add Term

Terms can be added to single/ multiple schema/s using this feature.

Remove Term

The Terms added to single/ multiple schema/s can be removed by using this feature.

Add to Default Project

The selected schema/s can be associated with the default project using this feature.

Remove from Default Project

The associated default project can be removed from the data objects by using this feature. Remove the object from the default Project set in the Projects module.

Update Governance Roles

Governance roles such as Steward, Custodian, and Owner can be added to the selected data objects by using this 9 dot feature.

Quick Tips

It gives a few insights about the Database.

Databases Summary

A database schema is a structure that represents the entire database. Database Summary makes it easier for OvalEdge users to go through the list of database objects quickly and find relevant information. The purpose of the Database summary is to display all the metadata such as Business description, Technical Description, associated Tags and Terms, and statistical details such as row count, column count, popularity, importance score, etc. of the schema.

The Database summary also displays the Tables, Queries, Functions, Views, Procedures, Triggers, and Synonyms available within the schema. You can get the statistical data for these when you click on each tab.

Screenshot 2 Database summary

More Features:

  • Collaboration Message - The collaboration feature helps to provide suggestions regarding the schema to the steward of the data object. You can also write a query in the collaboration message. The query raised by you can also be visible to other users.
  • Endorsements-  In the OvalEdge application, It is a great way to share the integrity of data objects with other OvalEdge users to explore the data sources. You can endorse the selected schema using this endorsement feature.
    You can endorse the selected schema using this endorsement feature.
    Note: Any user is allowed to raise a red flag, and the steward of the data object will be notified. However, the steward, owner, custodian, or any user with the meta write permission, and the user who has raised the red flag can remove the reg flag.
  • Add to Project- The Add to Project feature allows you to add the Schema to the Default project.
    When you select the default project as My Access Cart, The Add to Project option is replaced with the My Access Cart. You can add the schema to My Access Cart to raise an access request.

User Actions

The Nine dots available on the database summary help to perform multiple actions as mentioned below:

Field

Description

Profile

You can profile the data object using this Nine dots feature.

Profile Unprofiled

You can profile the newly added data, and also the old data which is not profiled earlier using this feature.

Add to My Watch List

You can add the selected data object to the My Watchlist window to get regular alert notifications on any metadata and data changes, by using this feature.

Download Description

You  can download the metadata, and descriptions by using this feature.

Governance Roles

You can update the governance roles associated with the data object, by using this option.

Service Desk

You can raise an access request on the Database to access (Preview, read, and write) the data.

Configure Search Keywords

Configure Search Keywords are searchable or relevant keywords added to the objects for the end-users to search the content they are looking for from the database effectively.

The Database summary provides information regarding the Business Description, Technical Description, associated Tags, Terms, Access Instructions, Crawled date and time, Profiling status, Table count, Row Count, and Importance Score of the schema.

When you crawl a Database connection, the Tables, Queries, Functions, Views, Procedures, Triggers, Others, and Synonyms available within the connection are displayed on the Database summary.

Tables 

After crawling a database in the Administration > Crawler, all the tables available in the schema will be displayed on this page with information on Tile, Term, Row count, column count, Popularity, Importance score, Status, Last Profile Date, and Crawl Type.

Field

Description

Table

Displays the name of tables available in the Database.

Title

Displays the title of the tables.

Term

Displays the terms associated with the table.

Row Count

Displays the number of rows present in the table.

Column Count

Displays the number of columns available in the table.

Popularity

Displays the Popularity Score of the table.

Importance

Displays the Importance Score of the table.

Status

Displays the profiling information of the table. If the table is profiled, then the status will be Profiled and if it is not profiled, then the status will be Unprofiled.

Last Profile Date

Displays the latest date on which the table was profiled.

Crawl Type

Displays the type of crawling

Screenshot1.2.2 Database-Tables

Codes

When a particular connection is crawled, the queries associated with the database will be displayed over here.

Example: If a query is created to view the certified salary account data.   

CREATE VIEW [salaryaccount] AS

SELECT CASE_STATUS

FROM H1Bdatabase.dbo.salary_data

WHERE CASE_STATUS = 'certified'

This query will be fetched and displayed in the Queries Tab.

You can view the Query Name, Title, Query (Content of the SQL Query), and Created date (the date on which the query was created in the source system), on the Data Catalog > Databases > Summary > Queries.

Screenshot1.2.3 Database-Codes

Functions

Functions are database objects that contain a set of SQL statements to perform a specific task. When a connection is crawled, the functions available in the database are displayed here.

Example: You can create a function to calculate the net sales based on the quantity, price, and discount value, by using the below function:

CREATE FUNCTION udfNet_Sales(  

    @quantity INT,  

    @price DEC(10,2),  

    @discount DEC(3,2)  

)  

RETURNS DEC(10,2)  

AS   

BEGIN  

 RETURN @quantity * @price * (1 - @discount);  

END

You can view the Function Name, Title of the Function, Function details, and created date (the date in which the function was created in the source system) on the Data Catalog > Databases > Summary > Function.

Screenshot1.2.4 Database-Functions

Views

The View is a virtual table created by a query by joining one or more tables.

In the OvalEdge application, to reuse a query you can create a virtual table and store the query in that table for future use.

All the views associated with the schema will be displayed, after crawling the database.

Example: The following SQL creates a view that shows all customers from Brazil:

CREATE VIEW [Brazil Customers] AS

SELECT CustomerName, ContactName

FROM Customers

WHERE Country = 'Brazil';

You can view the View Name, Title of the View, Type (Table/view/API/Materialized view), View Query (displays the SQL query), and created date (the date in which the view was created in the source system) on the Data Catalog > Databases > Summary > Views.

Screenshot1.2.5 Database-views

Procedures

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. All the procedures associated with the schema will be displayed, after crawling the database.

Example: The following SQL statement creates a stored procedure that selects Customers from a particular City from the "Customers" table:

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)

AS

SELECT * FROM Customers WHERE City = @City

GO;

The details of the procedure, such as Name, Type, Title, Procedure, and Created date, is displayed on the Data Catalog > Databases > Summary > Procedures.

Screenshot1.2.6 Database-Procedures

Trigger

A trigger is a set of SQL statements that reside in system memory with unique names. It is a specialized category of stored procedure that is called automatically when a database server event occurs. Each trigger is always associated with a table.

All the Triggers associated with the schema will be displayed, after crawling the database.

Example: You can create a trigger that stores transaction records of each insert operation on the Employee table into the Employee_Audit_Test table, using the below statement:

CREATE TRIGGER trInsertEmployee   

ON Employee  

FOR INSERT  

AS  

BEGIN  

  Declare @Id int  

  SELECT @Id = Id from inserted  

  INSERT INTO Employee_Audit_Test  

  VALUES ('New employee with Id = ' + CAST(@Id AS VARCHAR(10)) + ' is added at ' + CAST(Getdate() AS VARCHAR(22)))  

END

Screenshot1.2.7 Database-Trigger

Other

If the different connectors have different types of queries and if you are not able to bucket them as procedures, views, triggers, or queries, then you can categorize those in others. 

Synonyms

An alias or alternative name can be given to any of the database objects like a table, view, stored procedure, user-defined function, and sequence with the help of a Synonym.

When a connection is crawled, the synonyms available in the database are displayed here.

Example: Whenever you want to refer to the transactions table, first you need to write the database name followed by the schema name and then the name of the table. To reduce this overhead, you can create a synonym with the syntax:

CREATE SYNONYM transactions FOR salesdb.grocery.transactions.

Screenshot1.2.9 Database-Synonyms


Copyright © 2019, OvalEdge LLC, Peachtree Corners GA USA