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.

In the SQL Query Editor (Reports), the following custom table sections display in the tree view:

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

These sections are empty by default.

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)

    • Transformation queries

    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.

Supported files

Source file requirements:

  • File format

    • .csv

    • .gz

    • .gzip

    • .tgz

    • .xlsx

    • .zip

    Considerations for compressed files

    • Column headers - All of the files in a compressed file must share the same column headers.

      If a compressed file contains multiple files and issues are found with one or more of the files (for example, different headers or file issues), you can choose to drop the problem files and load only the valid files.

    • Hidden files - When files are compressed on Apple® Macintosh® devices, hidden files are added to the compressed file, for example, __MACOSX/ files. These files are automatically dropped when you upload the compressed file. A message displays to advise you that these hidden files have not been uploaded. You can proceed to upload the file (Ignore Warning, and Skip Invalid Files) or re-upload the file.

      Tip: These hidden files cannot be viewed in Finder. Use Terminal to view the files.

      The Invalid files not uploaded message also displays if one of the compressed files has an unsupported type, for example, if the file is an .xls file instead of an .xlsx file.

  • File size - Unlimited

  • Delimiters

    • Comma (,)

    • Semi-Colon (;)

    • Tab

    • Pipe (|)

    • Colon (:)

  • Column headers - Column names must be unique, must start with a letter, and can contain only the supported characters.

    Supported characters

    • lowercase letters (a-z)

    • numbers (0-9)

    • underscores (_)

    Unsupported characters

    • spaces

    • all other characters (for example, periods (.) and brackets ())

    Duplicate column names and blank column names are not supported.

    Important: Avoid using Network field names as column headers.

    Column header validation

    To help column headers pass validation, Network does the following:

    • Converts uppercase letters to lowercase

    • Trims spaces before and after the name

    • Provides alternate names to fix issues. The suggested names can be accepted or changed during the file validation.

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 either have a TPA in place, or that your agreement with the third-party data provider does not require a TPA for Veeva to receive the data.

      The responses to these questions are logged in the System Audit Log. For details, see Confirming third-party agreements.

    • Delimiter - If you are uploading a .csv file or a compressed file containing .csv files, you can define the delimiter used in the file.

      Note: If you upload a compressed file that contains multiple .csv files, the delimiter must be the same for all files. If the delimiter is different, none of the files will be loaded.

    • File upload - Choose how to proceed after you upload the file.

      • show file preview before creating custom table (default) - Display a sample of the table so you can preview the data before creating the table.

      • skip file preview and create custom table - Immediately create the table.

        The file is still scanned and validated. If any issues are found with the file or with the column headers, they must be fixed before the table is created.

    • Upload File - Drag your 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. If column name issues or duplicate column names are found, a pop-up displays so you can change the names that are flagged.

    • Unsupported characters or reserved words - Suggested updates are provided in the Updated Column Name field.

      Edit the column name or accept the suggestion by clicking Save & Continue.

    • Duplicate column names - Manually edit the name in the Updated Column Name field.

    Invalid Column Name Updated Column Name Details
    hcp.first_name__v (First Name) hcp_first_name__v _first_name Issue: Unsupported characters found: period (.), space, uppercase letters, brackets ()
    Fix:
    • Uppercase letters are automatically converted to lowercase letters
    • Unsupported characters are replaced with underscore (_)
    • Brackets () are removed.
    oid oid_1 Issue: Reserved word in column name.
    Fix:
    • _1 is automatically appended to the reserved name.
    hcp_first_name__v hcp_first_name__v Issue: Duplicate column name.
    Fix:
    • You must manually change the column name.

    If you edit the column name, it is validated again to ensure that it is not a duplicate column name and that it doesn't contain unsupported characters.

  5. If you chose to skip the file preview, the table is created.

  6. If you chose to preview the file after it is uploaded, theFile Preview tab opens. 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.

    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.

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