Create a table by performing the following steps.
Select “Table” from the + New drop-down menu. A submenu appears.
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”.
Specify a data source or data file.
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.
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 admin UI 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.
For query-based tables, add or delete columns as needed by modifying the SQL or SOQL (SalesForce) query and then clicking “Update Columns.”
Customize columns in your table by modifying the Label, Type and Dimension as needed.
Scroll down to add any of the following:
- Formula Columns.
- Runtime Security Filters.
- Parent and Child Joins.
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:
Select “Schema” at the top of the screen, to navigate to the “Schemas” page. Select the “Schemas” tab.
- Select the schema that contains the table to be loaded incrementally, to get to the schema definition page.
Select the down arrow corresponding to the table of interest on theright-hand side.
Select “Table Details” to go to the table details page.
Select the datasource where the data is located. The “Data Source” window appears.
Enable incremental loading to show the “Update Query” field.
- 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.
Select Save to get back to the table definition page.
- Select Done in the upper right-hand corner of the screen, or select Cancel to ignore the changes.
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.
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.
To delete a table:
- Select the corresponding down arrow to a table.
- Select “Remove Table”.