A table group is a data source that combines tables from many disparate sources. For example, you might have a spreadsheet with sales data, a couple of JDBC databases that have personnel or inventory information, manufacturing information from another division in NetSuite, and then want to use scripting on a table to do complex filtering and calculations, with all these tables linked by SQL joins and additional filtering.
You can set up a table group to import tables from different locations and then use SQL joins and other operations to filter and select information. You must have any prerequisite data sources already set up: for example, if you’re importing from a JDBC-based table, you must have a JDBC data source already set up, including a data agent, that lets you access that database.
To set up a table group data source, follow these steps:
Access the Data Designer by going to Integration > Data Designer.
In the Data Source folder in the Component Library on the right side of the screen, click Create New Data Source.
The Create New dialog box appears.
Select Table Group as the data source type and enter a name for the data source
The center area of the screen displays your new data source’s settings and other information, as shown below.
Enter the data source’s information:
- Log Level: Select a log level from the drop-down list to specify the detail for the logging for this data source. Your options are:
- Error: Only logs serious errors.
- Info: Logs all basic information, such as when the data source was updated.
- Verbose: Provides very detailed information about all phases and actions. (This level is used primarily for debugging or auditing, as it may produce more log information than is practical for typical use.)
- Click Save on the Actions menu.
Adding Tables to a Table Group
After you have set the logging level, you are ready to specify the information you want to import
To add tables to the table group, follow these steps:
Expand the Custom Tables entry in the Data Components menu.
Drag and drop the Join Tables item to the Tables to Import area.
Drag and drop the tables you want to import to the Tables to Import section in the center of the screen. When you drop this item, the Join Tables dialog box appears, as shown below.
4. Enter the join table’s information:
- Name: Enter a name for the table. (A default name is supplied.)
- Primary Table: Click the arrow to the right of this field to display all the data sources currently associated with this login, then expand one and select a table from that data source to use as the primary table.
- Joined Table: Click the arrow to the right of this field to display all the data sources currently associated with this login, then expand one and select a table from that data source to use as the joined table.
- Type: Select Inner Join, Right Join, or Left Join from the drop-down list.
- Join Expression: Click this field to enter a join condition. The Edit SQL Join Expression dialog box appears. (More information on creating SQL joins appearsin Adding a SQL Join Table.)
5. You can click Apply to save the basic information for this table and then edit it later, or you can add SQL join information by clicking Add Join.
6. Click Save to save the changes to the table group data source.
You can edit an individual table by clicking the arrow on the tab to the right of the table name. This displays the Join Table dialog box for the table.
Working with Imported Data in a Table Group
Because a table group is using tables from other data sources, you don’t import data directly in a table group data source. Instead, the table group relies on information in the staging area for the component tables. To clear or refresh the information in the tables appearing in a table group, you must go to the data sources for the component tables and clear or refresh them.
Setting Column Options
While using the Tables to Import section, you can see a preview of the data as it is in the staging area after it has been imported. You can change the options for each column by mousing over its heading and then clicking the drop-down menu next to its name. These options are:
- Sort Ascending: Sort the entire table based on this column in ascending order.
- Sort Descending: Sort the entire table based on this column in descending order.
- Column Settings: This dialog is used to change the properties of the column within the staging table.
- Name: The name of the column, displayed in the header.
- Column Type: The type of data used in the column.
- Convert Column Type: Select the new type from this drop-down menu.
- Exclude column from import: Select this to remove this column from the data being imported.
- Delete custom column: Select this to delete the column. (Available for custom columns only.)
Using the Advanced Filter
Below the Source drop-down menu, you can click Advanced Filter to reveal options for filtering the data in the Tables to Import preview area. The Advanced Filter provides tools to view and browse a subset of the data within a staging table. By changing filter properties, you can change the view of the data shown within the preview window without changing the data in the staging area.
The preview area is specifically for previewing a small portion of the data in the staging area. You can explore the data that is available from the spreadsheet and verify that you are getting the data you expect by examining the staging area. You can also use this to refine the data import filter you want to use. (Filters set in the preview function do not affect the actual data in the staging area.)
The options for the Advanced Filter are:
- Distinct Rows: Check this box to hide duplicate rows.
- Max Rows: Limit the number of rows displayed in the preview area by entering a number in the text box.
- Columns: Check or uncheck columns in this drop-down menu to show or hide the columns displayed.
Removing a column from this list does not remove the column from the import list.
SQL Filter: Click the large text box to display the Edit SQL Filter dialog box (shown earlier), where you can enter a SQL filter to restrict the rows being previewed. Click Apply to automatically check the SQL syntax of the filter. (If there are any errors, the error is indicated in the expression.) The expression you entered appears in the Data Import Filter field. For detailed SQL syntax help, you can click online help in the Notes section.
Click Remove Filter (next to the Source drop-down menu) to clear all advanced filter settings.
As part of the table customization process, you can also create join tables and custom columns using SQL expressions.