Custom tables

AD
DM
DS
ST

Advanced reporting users can create their own data tables in the SQL Query Editor. Administrators and Data Managers can also create custom tables by loading data directly from source and target subscriptions.

Example use cases

  • Compare existing data in your Network instance to a source file before a data load.

  • Upload a file instead of using the Lookup Tables feature.

    Lookup tables are available to Administrations and Data Manager users only and the tables are limited to 1GB or 5 million rows. Custom tables are available to all users that have access to advanced reporting and the tables do not have a file size limit.

  • Create a table based on your report results so you can create complex queries that don't time out.

  • Create snapshots of current data in Network.

    Use transformation queries to create a snapshot of data for a data model object, a reporting table, a lookup table, or another custom table.

    For details, see Create snapshots of current data.

  • Load data from a source file into a custom table.

    For example, load data into a custom table that you want to store in your Network instance and report on but does not need to be in custom object (the data does not require search, revision history, data lineage, data change requests, and other features).

    For details, see Loading data directly from a source file.

  • Prevent unnecessary transformation queryClosed The ability to use SQL queries to pre-process or post-process data during the subscription process within Network. output from being included in target export packages

    When target subscriptions include multiple transformation queries that run in sequence, an output is created for each query. To prevent query output from being exported to downstream systems, you can create custom tables as the output of any intermediary queries so that data remains in Network.

    For details, see Prevent intermediate files in subscription packages.

About custom tables

  • Tables can be created for your own use or to share with other users.

  • The tables are available to immediately query after they are created.

  • Table names must be unique in your Network instance. For example, two users cannot have the same table name in their My Custom Table sections.

  • All users with advanced reporting permissions can access the tables in the Shared Tables section.

  • You can create a hierarchy of folders in the custom table sections and move the tables in and out of folders.

  • Queries can be run against the tables in the following Network features:

    • SQL Query Editor

    • Saved Reports

    • Data Quality Reports

    • Data Maintenance subscriptions (Advanced).

    Custom tables cannot be used in the Basic Report Builder or the Aggregate Report Builder features.

Creating custom tables

Custom tables can be created in the following ways:

  • Uploading a file

  • Using report results

  • Loading data from a source or target subscription using data transformation queries

    Note: Custom tables created by loading data from subscriptions can be saved to Shared Custom Tables only. Subscriptions are not applied to individual users so sharing to personal custom table folders is not possible.

    To learn how to create custom tables from subscriptions, see Loading data into custom tables using transformation queries.

Create a table by uploading a source file

The My Custom Tables and Shared Custom Tables categories display in the SQL Query Editor; they are empty by default.

  • My Custom Tables - Data tables that you create for your private use. No other users can access the tables in this category.

  • Shared Custom Tables - Data tables created by you and other users in your Network instance. These tables are available to everyone in your Network instance that has access to SQL Query Editor.

Supported files

Source file requirements:

  • File format - Must be .csv.

  • File size - Unlimited.

  • Column headers - Can contain lowercase letters, numbers, or underscores (_).

    Duplicate column names, blank column names, spaces, and symbols (other than underscores) are not supported.

    To help column headers pass validation, Network converts uppercase letters to lowercase and trims spaces before and after the name.

    Important: Avoid using Network field names as column headers.

Create a table

To create a private or shared custom table:

  1. On the Network menu bar, click Reports > SQL Query Editor.

  2. In the tree view, on the My Custom Tables or Shared Custom Table heading, click Create > Custom Table.

    The Create Custom Table wizard opens.

  3. On Step 1, Table Details, provide the following information:

    • Table Name - Type a meaningful name for this table. The name is automatically appended with the __ct suffix.

      Table names must be unique for all custom tables in the Network instance. If the Table name already in use message displays, another user has used the name for their custom table. A table name can be used again if the custom table has been deleted.

    • Description - Type a meaningful description. The description displays in the table metadata in the tree view.

    • Table Type - The custom table category that you chose is automatically selected and cannot be changed.

    • Save to Folder - If folders have been created in the custom table section, you can select where the table should be saved. Otherwise, the table will be created in the top level of the section.

    • Third Party Data - Indicate if the file that you are uploading contains third party data. If you choose Yes, confirm that you have a TPA in place so Veeva can receive the data.

    • Upload File - Drag your .csv file to the box or click Upload File to choose it from your local computer.

      The file is scanned and validated when it is being uploaded. If any issues are found, warnings or a failed message displays.

      The upload will fail if any of the following issues are found:

      • Malformed Line Found - The file contains a malformed line.

      • Invalid file format - Files must be in .csv format.

      • Invalid header format - Spaces, special characters, or duplicate column names were found.

      • Something went wrong - An unknown issue occurred.

  4. When the file is uploaded, Step 2 of the wizard automatically opens. The File Preview displays a count of the records. This is the total number of rows that will be created in the custom table, not including the column header. The first four rows of the file display so you can preview the data before you create the table.

    If you click Table Details to return to the first step, the Third Party Data setting will be reset and you must re-upload the file. If you click Cancel, the table will not be created.

  5. Click Create Table to generate the custom table.

When the update is complete, the SQL Query Editor opens with the custom table section opened to the new table. Expand the table to review the metadata and the columns that were created.

Note: All fields under custom tables are text data type fields.

You can now use the table and fields in a report query.

Create a table from report results

Using report results to create a custom table enables you to create complex queries that will not timeout.

To create a table using results:

  1. In the SQL Query Editor, write a valid query in the query box.

  2. Click Run Query.

  3. In the Report Results section, click Create Custom Table.

    The Create Custom Table wizard opens.

  4. On Step 1, Table Details, define the settings for the custom table.

    Define the following information:

    • Table Name - Type a meaningful name for this table. The name is automatically appended with the __ct suffix.

      Table names must be unique for all custom tables in the Network instance. If the Table name already in use message displays, another user has used the name for their custom table. A table name can be used again if the custom table has been deleted.

    • Description - Type a meaningful description. This displays in the table metadata in the tree view.

    • Table Type - Choose the type of custom table to create: My Custom Table or Shared Custom Table.

    • Save to Folder - If folders have been created in the custom table section, you can select where the table should be saved. Otherwise, the table will be created in the top level of the section.

    • Export Dropdown List (Reference Value) Fields As - Specify whether reference codes appear in the results, or their corresponding localized labels. Labels will display in the language you select from the drop-down list.

  5. Click Next.

  6. On Step 2, Table Preview, a count of the records displays. This is the total number of rows that will be created in the custom table, not including the column header. The first four rows of the file display so you can preview the data before you create the table.

    Column header validation

    Network validates the column headers so the custom table is created without issues. If the column header names have issues, the table cannot be created; the Create Table button is dimmed.

    Column name requirements:

    • Must start with a lowercase letter.

      Network trims spaces before and after the column header name and automatically converts uppercase letters to lowercase.

    • Can contain only lowercase letters, numbers, and underscores (_).

    • Can be SQL reserved words.

    Errors will display for any of the following issues:

    • Duplicate or blank column names

    • Spaces within the column names

    • Special characters are used. Only underscores are supported.

    Validation issue example

    The column names that display are the data model field names from the report query, not the field label.

    In the report query, the created_date__v field is used for both the HCO and Address object, so the field name becomes a duplicate column name.

    To fix the issue, edit the column name. For example, add an hco_ prefix to the created_date__v column name. When the issue is resolved, the validation icon updates to a green checkmark.

  7. When any column header validation issues have been resolved, click Create Table. The table will be added to the custom table section that you specified.

    Tip: To share the tables you created in the My Custom Tables section, you can drag and drop them into the Shared Custom Tables section.

    Expand the table name to view the table metadata and the columns that are available to use in your queries.

    Note: All fields under custom tables are text data type fields.

Retrieving the source query

If you create a custom table using report results, you can retrieve the query that was used to create the table.

  • Hover over the custom table name to display the action buttons. Click the Copy Source Query to Clipboard icon to copy the query.

Table actions

Highlight a table to use the action buttons, Copy to Clipboard, Add to Query, and Delete.

Note: Table names must be unique. If you delete a custom table, the name can be re-used.

Delete tables

You can clean up the custom tables that you no longer use by deleting them.

  • To delete a table, hover over the custom table name to display the action buttons. Click the Delete icon.

My Custom Tables

All of the tables in this section can be deleted because you created them.

Shared Custom Tables

Tables in this section can be deleted if you created them. System Administrators and System and Data Admin users can delete any custom table.

If the Delete icon is dimmed, you do not have access to delete the table.

Create folders

To organize your tables, you can create folders and sub-folders in the custom table sections.

To create a folder:

  1. In the My Custom Tables or Shared Custom Tables section, click Create and choose Folder.

  2. On the Create Folder pop-up, type the folder name. Click Create Folder.

    The folder is added to the section above any existing tables.

  3. To organize your existing tables, drag them into the new folder. The hierarchy will be updated.

  4. You can also create sub-folders within a folder. Hover over the existing folder and click the Create (+) icon and click Subfolder.

    The Create Subfolder pop-up displays the hierarchy so you can choose where to add the sub-folder.

Edit folder names

Folders and sub-folder names can be changed.

  • Hover over the folder and click the Pencil icon. Make your updates in the name field. Folder names must be unique within the hierarchy; for example, in a top-level folder, two sub-folders cannot contain the same name.

Move folders

Folders can be moved into other folders so you can easily organize the hierarchy within your My Custom Tables and Shared Custom Tables sections.

  • Drag and drop a folder into another folder.

The contents of the folder and any sub-folders will also move. Folders cannot be moved between the two sections.

Delete folders

Folders and sub-folders in the Shared Custom Tables sections can be deleted by all reporting users. You can delete all folders in your My Custom Tables sections.

  • Hover over the folder and click the Delete icon. In the confirmation pop-up, click Yes, Delete Folder. The folder is removed from the custom table section.

The icon is dimmed if the folder contains tables. When you delete a folder that contains empty sub-folders, all of the folders are deleted at the same time.

Search for tables

Use the search bar in the tree view to find custom tables and their fields. Folders do not display in the search results.

Your search term displays below the search bar so you can see what the results are filtered on. Click Clear Search to clear the filtering and view the entire tree view again.

Logs

All of the actions (creating, deleting, and editing table names and folders) are tracked in the System Audit History (Logs).

Saved reports considerations

Tables from the My Custom Table category can be used in Saved Reports. Saved Reports use the data permissions of the user who last modified the report. When the user who last modified the report is the creator of the private custom table the report will run successfully. If the Saved Report results are shared with other users, those users might see data from the private custom table. This is expected behavior.

If the Saved Report contains a private custom table and the user that last modified the report is not the creator, then the SQL validation will fail or the user will see an error.