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 Vertica analytics 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, you land on the home page. This page displays the side navigation bar, welcome card, profile information, and database card, as shown in this image:

OTCAD user interface

Option Description
a Dashboard: Displays the Welcome card.
b SQL Editor: Displays the SQL Editor.
c Integrations: Displays the Integrations page with a listing of third-party tool integrations.
d Connection Information: Displays the data needed to connect to your database.
e Welcome card: Displays the Welcome card which includes navigation keys and the option to hide the welcome card.
f View support and docs: Provides shortcuts to the OpenText support portal and Vertica user documentation.
g View profile: Displays the user profile and option to sign out of the application.

Welcome card

When you log in to the application or click the Dashboard icon, the welcome card is displayed with these options:

  1. Select Don’t show this again to hide the welcome message when you log in again to OTCAD.
  2. Select Next. The Navigation sidebar opens.
    • Select Next on this sidebar to navigate to the next screen.
    • Select Finish to exit the Navigation sidebar.
  3. Select Skip to hide the Welcome message only when you are logged in to the application.

Connection data options

The Database card shows the database that is active. Expand the Connect drop-down list and select Connection Data.

  1. Copy this information to connect your database with other applications or clients:
    • Domain
    • Database name
    • Access token
    • Refresh token, applicable only for VerticaPy connections. For more information, see VerticaPy documentation
    • Client ID
    • Token URL
  2. Select Close to close the Connection Details screen.

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.

Working with SQL editor

After logging in to the OTCAD application, the home page appears. This page displays the side navigation bar, Welcome card, profile information, and Database card.

To launch the SQL editor, do one of the following:

  • Select SQL Editor in the side navigation bar. --Or-->
  • Select Connect and Open SQL Editor in the Database card.

The Schema view displays with a list of tables. You can either select a table or create an SQL query.

Create an SQL query

In the Schema view, you create SQL queries.

Create SQL query

  1. In the schema list, select a table and do one of the following:
    • Select Create Query. --Or-->
    • Select + in the navigation bar.
  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.

View query history

The Query History view has information about 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. You can open and remove multiple queries simultaneously.

To open multiple queries simultaneously:

  1. Ctrl-click and select the queries in the SQL editor view.
  2. Select … and from the drop-down menu, select Open.

To remove multiple queries simultaneously:

  1. Ctrl-click and select the queries in the SQL editor view.
  2. Select … and from the drop-down menu, select Remove.

Query Favorites view

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

Query Favorites view

To add a query to favorites:

  1. Enter the query in the SQL editor.
  2. Select Run.
  3. Select Favorite.
  4. In the Save Query As dialog, enter the query name and select Save Query. The query is now visible in Favorites.

To rename a favorite query:

  1. Select the query.
  2. Select … and from the drop-down menu, select Rename.
  3. Enter the new query name and select Update.

To remove a favorite query:

  1. Select the query.
  2. Select … and from the drop-down menu, select Remove.
  3. Select Delete.

To run a query from the Favorites view:

  1. Select the query.
  2. Select … and from the drop-down menu, select Run. After successful execution of the query, the results are displayed in the SQL editor.