OpenText Core Analytics Database

This topic provides an overview of OTCAD.

OpenText™ Core Analytics Database (OTCAD) is a managed database service that delivers a unified, high performance advanced analytics and machine learning platform in the cloud. The database gets automatically provisioned in an OpenText AWS account. OpenText manages the database hardware and environment. OTCAD is optimized for efficiency and query performance, ensuring you meet your service level agreements within your budget.

OTCAD enhances the speed and scalability of a database without the need to manage hardware. OTCAD is a data warehouse and data lake platform that enables organizations to deploy databases securely. To get access to OTCAD, contact your sales representative or contact us.

OTCAD UI tour

You can view and manage the provisioned database. After logging in to the OTCAD application, the home page appears. This page displays the three-dot menu and other SQL editor options as shown in this image:

OTCAD user interface

Option Description
a More options: Displays the More options button with options to access the SQL editor, Settings, Data pipelines, and the Integrations page.
b Stop: Displays the option to stop the database.
c History view: Displays the queries run previously.
d Favorite view: Displays the bookmarked SQL queries.
e Help: Displays OpenText™ Analytics Database user documentation.
f View profile: Displays the user profile and option to sign out of the application.

More options

When you log in to the application, the More options button is displayed with these options:

  1. SQL editor: Select this option to work with the SQL editor.
  2. Settings: Select this option to configure the database settings.
  3. Data pipeline: Select this option to work with data pipelines.
  4. Integrations: Select this option to display the Integrations page.

Database connection options

The Home page displays the option to stop an active database.

  • To stop OTCAD, select Stop. <--Or-->
  • To re-start an inactive database, select Start.

SQL Editor

Use OTCAD SQL editor to write, edit, and execute SQL statements in a query view. You can write and execute analytical queries without using any external tools.

You can use the SQL editor to:

  • gain valuable insights from your data without needing expertise to set up database connections.
  • write, execute, and save queries that need to be repeated.
  • view sample data returned from your queries.
  • validate the execution of SQL queries.
  • view data about the execution of SQL queries.
  • export queries into a spread sheet.
  • execute SQL commands for OTCAD operations, such as creation of schemas, tables, and so on.

Working with SQL editor

After logging in to the OTCAD application, the home page appears.

To launch the SQL editor, select SQL Editor in the three-dots menu. The Schema view displays with a list of available schemas and tables. You can either select a table or create an SQL query.

Create an SQL query

You create SQL queries either in the Home page or the Schema view.

Do one of the following:

  1. Select a table:

    • Select Create query in the Schema view. --Or-->
    • Select Create new query in the Home page.

  2. Enter your query in the SQL editor.

  3. Select Run.

  4. Enter the name of the query at the top.

  5. To view the schema details of a table, select View Schema. The table columns display along with the datatypes.

  6. To filter the SQL query results, select the filter icon. You can add a filter. search the results by the column name and the match type, and select Apply.

  7. To save the SQL query results, do the following: a. Select Export CSV. b. Enter the file name and select Export.

    You can export upto a maximum of 10 thousand records. To import data that exceeds 10 thousand records, modify the SQL query and export again.

History view

The History view tab stores the queries run previously. When you run a query, it appears in the Query History list. Queries of the last 2 weeks only are displayed.

To run a SQL query:

  1. Select ….
  2. Select Run.

To remove a SQL query:

  1. Select ….
  2. Select Remove.

Favorite view

You can bookmark SQL queries and run them again later in the Favorite view tab. Saved SQL queries display as a list of favorites next to the History view tab.

To bookmark a SQL query as a favorite:

  1. Select the SQL query.
  2. Select Run.
  3. Select Favorite.
  4. Enter a name and select Add.

To run a SQL query from the Favorites view:

  1. Select ….
  2. Select Run. After successful execution of the query, the results are displayed in the SQL editor.

To rename a favorite SQL query:

  1. Select the SQL query.
  2. Select ….
  3. Select Rename.
  4. Enter the new name and select Update.

To remove a favorite SQL query:

  1. Select the query.
  2. Select ….
  3. Select Remove.
  4. When prompted for confirmation, select Remove.

In this section