About Tables

Create a table by performing the following steps.

  1. Select “Table” from the + New drop-down menu. A submenu appears.

  2. Select a type for the new table, the “Data Source” window opens at the top of the “New Table” page. For xlsx, csv, tsv, tab and txt files, select “File System”.

  3. Specify a data source or data file.
  4. Turn on the “Incremental” switch button (where available) to enable incremental loading, in which only new data is loaded from the server. If you do not enable incremental loading, this table will always be loaded fully when data is loaded or refreshed. This can result in long loading times.
  5. Specify the datasource parameters:

    • For a SQL datasource, defined the SQL query in the “Query” field. If you have selected incremental loads, define an update query in the “Update Query” field, e.g. “where <Last_Update_Date>>?”, given that a Change Data Capture column exists within the table. Note that ”?” refers to the last successful load start time.
    • For CSV files: Specify the character set, thousands separator and date format. If you have enabled incremental loading, specify which CSV file to use for the incremental changes.
    • For Excel XLSX files: Select a Worksheet. If no Worksheet is specified, the first one will be used. If you have enabled incremental loading, specify which worksheet contains the incremental changes (the delta from the last version loaded). For password-protected Excel files, ensure that this feature is enabled in the cluster management console (CMC) under “Tenant Configurations”. Users will need to enter the password when creating a table based on this file.
    • For SalesForce data, define an SOQL query. If you have selected incremental loads, define an update query.
    • Fetch Size is 300 rows by default; leave this value unset unless you are tuning the performance.
  6. For query-based tables, add or delete columns as needed by modifying the SQL or SOQL (SalesForce) query and then clicking “Update Columns.”
  7. Customize columns in your table by modifying the Label, Type and Dimension as needed.
  8. Scroll down to add any of the following:

    • Formula Columns
    • Runtime Security Filters
    • Parent and Child Joins

NOTE:

You can load up to 3.4 billion records in a table. If you use composite keys, you can load up to 1 TB of records.

Set up a table for incremental loading

This feature enables loading only the data that has been generated since it was last loaded into Incorta Analytics. To use this feature, ensure that a CDC (Change Data Capture) column exists in the table you are loading incrementally. Otherwise, it would be fully loaded even if you select the “Incremental” loading option when loading data in the schema definition page. An SQL update query will be needed to fetch the updated data from the server.

To enable Incremental Loading:

  1. Select “Schema” at the top of the screen, to navigate to the “Schemas” page. Select the “Schemas” tab.

  2. Select the schema that contains the table to be loaded incrementally, to get to the schema definition page.
  3. Select the down arrow corresponding to the table of interest on theright-hand side.

  4. Select “Table Details” to go to the table details page.

  5. Select the datasource where the data is located. The “Data Source” window appears.

  6. Enable incremental loading to show the “Update Query” field.

  7. Copy the query from the “Query” field and paste it into the “Update Query” field, and append it with the update statement “where <CDC_column>>?“. Note that ”?” refers to the last successful load start time.
  8. Select Save to get back to the table definition page.

  9. Select Done in the upper right-hand corner of the screen, or select Cancel to ignore the changes.

Add a Data Source to a Table

With Incorta you can create multi-source tables. To add a source table, select the add button in the data source list at the top of the Table Details page. However, it is important to note that in the case of an existing materialized-view table, this feature, and thus the add button, would be disabled.

Column Definition Features

In the “Columns” section, you can select what columns to appear in the Source Elements List in the Analyzer, and define the column features. When you make changes, you must perform a refresh from snapshot in order for the changes to take effect.

“Name” lists the columns of the table. Uncheck the box for any columns you do not want to include in your schema.

“Label” displays a user-friendly name for the column. This name should be descriptive of the data in the column. Customize labels here as needed. This can be done by clicking in the cell you want to rename and typing the name you can find appropriate.

“Type” indicates the data type. You can choose the data type that the column contains. You can choose it to be either date, double, integer, long, string, or null.

“Function” indicates whether the column is a key, dimension or measure. Incorta Analytics’ auto-intelligence assigns a default value. To change the function, select the current value and choose from the drop-down list.

Key - Assign the Key function to columns used to uniquely identify each record. Keys are used for table joins and for incremental refresh of data. During an incremental load, each record with an existing key is updated, and each record with no matching key is inserted.

Dimension - Assign the Dimension function to columns used to categorize or group data, such as country, state and city, or days, months and years. In a line graph or bar chart, a dimension used for grouping is plotted on the x-axis, while a dimension used to give a count is plotted on the y-axis.

Measure - Assign the Measure function to columns used to measure and aggregate data. Data from these columns must be plotted on the y-axis and with the proper aggregation function.

If you are not sure which function to assign, you can change the value at any time after the schema has been created.

Delete a Table

To delete a table:

  1. Select the corresponding down arrow to a table.
  2. Select “Remove Table”.

Load tables in the staging area

Users can control whether to load schema tables in Incorta’s memory or the staging area, by toggling the Performance Optimized button in any table’s definition page.

To disable performance optimization and load tables in the staging area:

  1. Navigate to “Schema” page.

  2. Select a schema.
  3. Select the down arrow in the upper right-hand corner corresponding to a table.

  4. Select “Table Details” from the drop-down menu.
  5. Disable the Performance Optimized option in the upper-right hand corner of the screen.

  6. Select Done, in the upper right-hand corner of the screen.
  7. Select Load, and choose “Full Load”. Upon successful loading, tables with disabled performance optimization get loaded into the staging area. Those tables with enabled performance optimization get loaded into Incorta’s memory.

Selecting tables within a schema to load into memory

You can load one or more tables in a schema to the staging area by using the settings menu. Loading to staging allows users to query only the needed data to be loaded into Incorta’s memory.

To select/deselect tables to load to the staging area:

  1. Navigate to the “Schema” page.
  2. Select the schema containing the tables you of interest.

  3. Select the settings menu (gear icon) in the upper right-hand corner of the screen. The schema settings window opens.

  4. Deselect or search for the tables to be unloaded from memor, e.g. “Customers”. Tables in the schema will be shown selected by default as they are loaded to the engine memory.

  5. Select Save Changes. A confirmation window appears.

  6. Select Confirm Changes.

Create a schema containing query tables

If you load your data into the staging area, you have the option to run a query and load only the returned data to Incorta’s memory. To do that, create a schema based on query tables, and load it.

Create a query table

Create a table based on the data returned by a query.

  1. Choose a database schema from the left-hand side of the schema wizard, e.g. “Sales”.

  2. Select one or more tables to add to your schema. Each table you add appears in the middle section of the page.

  3. Select the ”+“icon in the upper right-hand corner of the schema wizard window, to create a custom query table.

  4. Provide a “Table Name” and a query in the empty field.

  5. Select Execute.

Edit an existing query in a schema

To edit a custom query table in a schema, the following example will be used to edit a table querying the revenue per product:

  1. Choose a database schema from the left-hand side of the schema wizard, e.g. “Sales”.

  2. Select the icon in the upper right-hand corner of the schema wizard window.
  3. Edit query.
  4. Select Execute.

Entity Relationship Diagram

Incorta Analytics provides a convenient way to show the relationship diagram of the tables used within a specific schema. Follow the steps below to learn more:

  1. Navigate to the “Schemas” tab in the “Schema” page.

  2. Select a schema to see its ERD by clicking on its row, not the checkbox. The schema definition page opens.

  3. Select Diagram in the upper right-hand corner of the screen. Select List to exit the diagram view.

  4. Add the table keys to the diagram, by enabling “Show Keys” in the settings menu at the top.

  5. Add the table columns to the diagram, by enabling “Show Columns” in the settings menu at the top.

  6. Change the diagram orientation, by enabling selecting the desired orientation in the settings menu at the top.


© Incorta, Inc. All Rights Reserved.