OpenText Core Analytics Database

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:

Option	Description
a	More options: Displays the More options button with options to access the SQL editor, Settings, Data pipelines, User-defined extensions, Database designer, Dashboards, Restore points, 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	Create table: Displays options to create the table and schema.
f	Notifications: Displays message alert notifications.
g	Help: Displays OpenText™ Analytics Database user documentation.
h	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. User-defined extensions: Select this option to work with user-defined extensions.
5. Database designer: Select this option to work with the database designer.
6. Dashboards: Select this option to work with dashboards.
7. Restore points: Select this option to work with restore points.
8. 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.

1. Select Create a new query in the Home page.

2. Enter your query in the SQL editor.

3. From the Run list, do one of the following:

   • Select Run last query to run the SQL query that was last executed. This option is helpful when you have multiple SQL queries but need to run only the latest SQL query.
   • Select Run from cursor to run an SQL query using a database cursor to manage and process the results of a statement one row at a time, rather than as a single set of data.
   • Select Run selection to run only the selected SQL query. This option is helpful when you have multiple SQL queries but need to run only one SQL query.

4. Select the SQL query in the editor and click Format to arrange and format the SQL query for better readability.

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

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

7. 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.

8. 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.

Upload and download scripts

You can create SQL queries offline and upload them to the system.

1. In the SQL query page, select ⋮ and Upload script. The Upload script dialog appears.
2. Select Browse or +.
3. Select Upload.

   Note

      All uploaded code or content is the sole responsibility of the uploading party. OpenText does not review, endorse, or accept any liability for uploaded materials. Uploads are made at the uploading party's own risk.
   

   The file content is loaded to the SQL editor.

You can also download SQL queries in the system.

1. In the SQL query page, select ⋮ and Download script. The SQL query is downloaded in a file with .sql extension in the Downloads folder.

Create a table

You can create tables in the database logical schema.

1. In the Schema view, do one of the following:

   • Select +Create table.

     OR

   • Expand a schema in the Schema view and click + for Tables. The Create table page appears.

2. In the Table name field, enter the name of the table to create. This name must be unique among names of all sequences, tables, and projections within the schema. Ensure that the table name meets the following requirements:

   • Must comply with keyword restrictions and rules for Identifiers.
   • Cannot begin with v_; this prefix is reserved for OTCAD system tables.

3. From the Schema list, select an existing schema for which the table needs to be created. If no schema is specified, the table is created in the public schema.

4. In the Column name field, enter the name of the column to be created. A table can have up to 9800 columns.

   Note

      Provide unique column names for all columns. You cannot provide the same column name for multiple columns in the table.
   

5. From the Type list, select an internal data type.

6. In the Length field, enter the maximum number of characters that the column can store. It is a crucial aspect of database design that helps enforce data integrity, optimize storage, and ensure data consistency. For more information about the data types and their corresponding length or size, see data type.

   Note

      Provide a valid number. The system does not consider invalid numbers or characters.
   

7. Select the Nullable option to permit this column to store NULL values, representing missing, unknown, or optional data. Selecting this option allows rows to be inserted without providing a value for that specific column.

8. Select the Primary option to designate this column as the primary key. The primary key is a column in a database table that uniquely identifies each row, ensuring data integrity by prohibiting duplicate or NULL values. It enables efficient data retrieval and acts as a target for foreign keys to establish table relationships. Every table must have one primary key.

9. Select the Unique option to specify a unique constraint on a column so that each value in that column is unique among all other values.

10. Select the Order by option to sort the result set of a query in a specific order based on the values in one or more columns. This allows you to control the presentation of the data for better analysis and readability, as database rows are not guaranteed to be in any specific order by default.

11. Select More. The Advance configuration page appears. You can set the foreign key references to link one table with another in this page. This page is enabled only when you enter the column name.

12. In the Reference tab, do the following:

    • From the Reference schema list, select a reference schema which is a specification that defines the structure, data types, and relationships for data, often including mechanisms to link to or reuse other schemas or subschemas.
    • From the Reference table list, select a table that links to the primary key of another table. It ensures that the data in both tables stays connected and valid. It acts as a primary source for validating input, mapping values, and normalizing data, allowing other tables to reference it through foreign keys. If you omit this column, the database uses the table's primary key. Only those columns that have at least one primary key in the reference table are listed.
    • From the Reference column list, select a reference column. Updates to the referenced data are automatically reflected in all locations. Primary key columns only are listed.

      Note

         When linking a new table with an existing table, ensure that the data types of the primary key and foreign key match. A mismatch in the data types of the primary key and foreign key results in an error.
      

13. In the Sequence tab, from the Sequence list, select the name of the sequence. Use sequences to set the default values of columns to sequential integer values.

14. In the Column constraints tab, enter the boolean expression that evaluates a column's value on each row in the Check constraintsfield.

15. Select Save.

16. To import data from a csv file, do the following:

    • Select Import from CSV.
    • Select Browse.
    • Navigate to the folder containing the CSV file and click Open.

      Note

         Only the header rows in the CSV file are saved as column names. The system does not infer data types from the data in the CSV file. All uploaded code or content is the sole responsibility of the uploading party. OpenText does not review, endorse, or accept any liability for uploaded materials. Uploads are made at the uploading party's own risk.
      

    • Select Save.

17. Select SQL preview. A real-time preview of the SQL statement is displayed. The SQL syntax is validated and errors are displayed. This helps you identify errors and fix them before running the SQL queries.

18. Click the Download icon to download the SQL query to a file with .sql extension.

19. In the Partition expression field, specify a partition key that is derived from one or more table columns. Creating partitions decreases the SQL query execution time and improves table efficiency. For information about partitioning tables, see Partition clause.

20. Select Create. The table is created and displayed under the schema where you created it.

Create a schema

You can create as many schemas as necessary for your database. For example, you can create a schema for each database user. By default, only an administrator can create a schema or give a user the right to create a schema.

The database logical schema comprises a set of tables and referential integrity constraints in the database. The objects in the logical schema are visible to SQL users. The logical schema does not include projections, which make up the physical schema. The physical schema comprises a set of projections used to store data on the disk. The projections in the physical schema are based on the objects in the logical schema.

1. In the Schema view, expand the +Create table list and select Schema. The Create schema page appears.

2. In the Schema name field, enter the name of the schema. Ensure that the schema name meets the following requirements:

   • Must be unique among all other schema names in the namespace under which the schema is created.
   • Must comply with keyword restrictions and rules for Identifiers.
   • Cannot begin with v_; this prefix is reserved for OTCAD system tables.
   • If the schema name contains a period, the part before the period cannot be the same as the name of an existing namespace.

   Inheritance of privileges specifies whether to enable or disable default inheritance of privileges for new tables in the schema.

3. Do one of the following:

   • Select Include to grant tables in the specified schema the same privileges granted to that schema. This option has no effect on existing tables in the schema.

   OR

   • Select Exclude to disable inheritance of schema privileges. By default, inheritance of schema privileges is disabled.

4. Select Create. The schema is created and displayed in the Schema view.