Data export

Transformation queries

22R1

Network can now transform data so you can export it in the format that downstream systems can easily receive. Previously, you might have used a third-party ETL tool to post-process Network data. Now, you can create SQL queries to transform the data within Network before exporting it using a target subscription.

This feature is enabled by default in your Network instance.

Examples of data transformations

Use transformation queries to post-process data. For example:

Replace values

Example: If a limited relationship in Network is invalidated, the foreign key fields are set to -1, but that value is not supported in Veeva CRM. Replace -1 with #N/A for the CRM Upsert Connector.
Denormalize a complex data model

Example: Flatten a multi-level product hierarchy. The data is transformed to a single .csv file that your downstream system can easily digest.
Join additional fields

Example: Join additional fields from any table in Network reporting, including lookup tables or custom tables
Define field values based on certain conditions
Rename fields
Concatenate or split fields

For detailed use cases and sample queries, see Transformation query examples.

How transformation queries work

Use queries for the following activities:

Transform files exported by target subscriptions.
Extract data only from the reporting database.
Join datasets exported by target subscriptions with datasets from the reporting database.

Note: If you use the query to read from the reporting database, all applicable records from all countries and with all record states will be included in the output file; data visibility profiles are not considered and records are not filtered by their record state.

Query process overview

Transformation queries are SQL based (SELECT statements). Test them in the SQL Query Editor and then link them to target subscriptions.

Write and test your query in the SQL Query Editor (Reports).
Create a transformation query (System Interfaces). Copy and paste the tested SQL query into the transformation query configuration.
Link the transformation query to a target subscription.

Note: If you are using the query to extract data only from the reporting database, the query is still linked to a target subscription. The export settings on the target subscription determine if data is extracted from the Network main database or not.

When the target subscription runs, the following process occurs:
- Extract data - According to the configured export settings on the target subscription, Network extracts the data from the main database. If the target subscription is configured to export no entities, then no data is extracted from the main database at all.
- Apply queries - Inject transformation queries into the job.
- Generate files- The files exported by the target subscription and/or the query output files are packaged and placed on the Network FTP server.

Create a transformation query

Prerequisite

Write and test your query in the SQL Query Editor to ensure that it is valid and performs the data transformation as expected.

Example

This query joins the payer and plan tables. It also replaces the foreign key value on the related_player__v relationships from -1 to #N/A.

The SQL Query Editor validates the SQL syntax and field names that are used in the query.

Adding the transformation query

In the Admin console, click System Interfaces > Transformation Queries.
Click Add Query
Type a Name and Description.
Paste your tested SQL query into the query box.

Query considerations
- Table names - Add the __csv extension to the table names if you want to post-process the files exported by the target subscription. To read directly from the table in the reporting database, do not add the extension.
  
  Examples
  
  To read from the table in the reporting database, do not add the __csv extension to the tables in your query.
```
SELECT vid__v, first_name__v || ' ' || last_name__v from hcp
```
  To access files exported by the target subscription when the transformation query runs, add the __csv extension to the tables in your query.
```
SELECT vid__v, first_name__v || ' ' || last_name__v from hcp__csv
```
- Unique column names - The exported results require unique column names. Identical column names are valid in the SQL Query Editor for reports but must be changed here so the transformation query can be saved.
- Record state - All record states are included in the results if the transformation query reads directly from the table in the reporting database. You can use a WHERE clause to filter records; for example, to only include valid records in the results, add WHERE record_state__v = VALID to your query.
  
  Target subscriptions can be configured to consider record state. This means if the transformation query reads from a file exported by the target subscription, the data might already be filtered by record state depending on your subscription settings.
- Number and date fields - When transformation queries process data that is exported by the target subscription, all fields are internally treated as varchar fields. So, if your transformation query is intended to aggregate numbers or to do calculations on number or data fields, use the CAST function to convert the data type from varchar to the correct data type. This prevents issues because the export file processing uses varchar as the data type. For more information about CAST, see SQL functions in the Veeva Network Online Help.
- LIMIT clause - This LIMIT clause is not supported on the transformation query. If you want to limit the number of rows returned, wrap another query around the query that has the LIMIT clause.
  
  Example: LIMIT clause not supported on the transformation query.
```
SELECT
        vid__v AS vid,
        first_name__v || ' ' || last_name__v AS name,
        alternate_first_name__v || ' ' || alternate_last_name__v AS altname
    FROM
        hcp__csv LIMIT 10
```
  Example: LIMIT clause supported when it is wrapped within another query.
```
SELECT * FROM (
SELECT
        vid__v AS vid,
        first_name__v || ' ' || last_name__v AS name,
        alternate_first_name__v || ' ' || alternate_last_name__v AS altname
    FROM
        hcp__csv LIMIT 10
)
```
Type a name for the output file. The file name is automatically appended with .csv.

File name support:
- can contain numbers (0-9), letters (a-z, A-Z), and underscore (_) characters
- must begin with a letter or underscore (_)
- cannot contain more than 100 characters
- cannot be any SQL keyword
- cannot be reference.csv
Save your query.
When the transformation query is saved, Network validates the query; for example, to ensure that column names are unique, to check if the query contains the LIMIT clause, or if the table names and output files are valid. If warnings display, fix the issues and save your changes.

Saved transformation queries

After you save a transformation query, it is listed on the main page. Each query row contains the name, description, and the modified and created dates by user.

Delete transformation queries

If the query is no longer needed or was created by mistake, click the Delete icon to remove it from your Network instance.

The dialog displays any linked subscriptions . Click Delete to confirm.

When a query is deleted, any related target subscriptions are also updated to remove the link to the query.

Edit transformation queries

Click the transformation query to open it and view the details or make changes. All the fields, except the Code (used for exporting configurations) can be edited.

The Subscriptions section displays any target subscriptions that use that transformation query.

Configure target subscriptions to export files

After the transformation query is created, add it to a target subscription so you can post-process the exported files.

Create or edit a target subscription

In the Admin console, click System Interfaces > Target Subscriptions.
Open an existing subscription or click Add Subscription to create a new one.
Details section
- Type a Name and Description for the subscription.
- Type - Choose Data. Transformation queries are not supported for subscriptions that export DCRs.
- System - Choose the source system.
- Status - The subscription is enabled by default.
General Export Options section
- Targeted Record Options - Use the settings to define the extent of the data export.
  
  Use the Record State setting to filter the records if you want to export only Valid and Under Review records. You could have also added a record state filter to your transformation query.
- Hierarchy - To avoid exporting unwanted records, select Apply Export Options to the target records related entities.
  
  Note: The Targeted Record Options and Hierarchy sections do not apply if you are extracting data from the reporting database only. These sections will be removed from the configuration if you choose the Export None option in the File & Field Selection section.
- Reference Data - Choose to Include Reference Data Files.
  
  Tip: If you include reference data, there is an internal table called reference__csv that you can use in your transformation query to look up reference labels.
- File Format - Define how you want the export file to be formatted.
- Export Locations - Choose to export the file to an FTP path or to cloud storage.
For detailed information about the settings in these sections see the Add a target subscription topic in the Veeva Network Online Help.
File & Field Selection section

This section is updated to support transformation queries that export data from the reporting database only.

The following options are now available for objects that are enabled in your Network instance:
- Select Which Objects and Fields to Export - Selected by default in new target subscriptions. When you select this option, you can choose to export all, some, or no fields for each object.
- Export All Objects and Field - All fields are exported for all objects. The lists beside each object are inactive.
- Export None - No files will be exported from the Network main database. Choose this option if you are extracting data from the reporting database only. The lists beside each object are inactive.
  
  If you choose Export None, the Targeted Record Options and Hierarchy sections in General Export Options are immediately removed from the configuration because they no longer apply.
Object Export Options - For each object, choose to export All Records or Select Records. If you choose to export select records, define the filters for the records.
Transformation Queries section - Click Add Query to add a transformation query to the subscription.

You can apply multiple queries to each subscription. The queries will always run in sequence as they are listed in the subscription. Change the order of the queries using the Handle icon.
Job Trigger Configuration - Optionally add a schedule and any emails or jobs that should be triggered for this subscription.
Save your changes.

Data Flow View

After you save a target subscription, a new Data Flow View is added to the Details section to visualize all of the steps of the job, including the input and output files.

Click the Data Flow View thumbnail to open the view.

These stages of the job are defined:

Start - Indicates if the job has been triggered by another job.
Extra Data From Main Database - An overview of the files and settings that are defined in the target subscription. If the job is configured to extract data only from the reporting database, no files display.
Apply Transformation Queries - Details about each query that is applied to the subscription. If there are multiple queries, they are listed in the order that they run. Click the query name to open the transformation query. Click View Query to see the query. This step does not display if transformation queries are not applied.
Generate File - The output files, their format, and exported location. This includes any files exported by the target subscription as well as any query output files.
Export to Cloud Storage - Links to the Amazon S3 bucket that the files are exported to. This step does not display if files are not exported to cloud storage.
End - Indicates if this job triggers email notification or another job.

To return to the job configuration, click Back to Subscription Page.

Unsaved changes

If you have made changes to the target subscription configuration, the Data Flow View does not reflect those changes until the subscription is saved. A message displays if you open the view before saving the subscription.

Note: Transformation queries and the Data Flow View do not apply to target subscriptions that export DCRs.

Job details

After a target subscription completes, the Job Details page displays a Transformation Queries section if one or more queries was applied to the job.

The details include the query name and description, the output file and the number of records that were returned by the transformation query. If multiple transformation queries were applied, they display in the sequence that they ran.

Click View Query to display a snapshot of the query that was applied when this job ran. It might be different than the query that is currently saved in Network. Viewing the query as it was at run-time can help you to troubleshoot any issues that might have occurred.

Job errors

When a target subscription runs with a transformation query that lead to a runtime error because of an invalid or incorrect SQL statement, the job will fail and an error is logged to help you to troubleshoot.

For example, if a transformation query created for HCPs is applied to a target subscription for exporting HCOs, a run-time error will occur.

Considerations for opted out HCPs

Opted out HCPs are masked when they are queried through the SQL Query Editor (Reports), because data visibility profiles are applied to Network Reporting.

When opted out HCPs are retrieved from the reporting database through a transformation query, they are not masked. Data visibility profiles are not applied to transformation queries because target subscriptions run under system user permissions.

There is a target subscription setting to mask/unmask opted out records during export. This means if a transformation query processes opt outs exported by the target subscription, they can be masked depending on that export setting.

If transformation queries retrieve opted out records directly from the reporting database, then they are never masked; the target subscription setting does not apply.

To avoid data privacy issues when you need to transform opted out HCPs, export them using the target subscription with masking enabled. You can then define a transformation query that post-processes the masked HCPs exported by the target subscription.

Exporting configurations

Administrators can move transformation queries to target environments using export packages (Settings > Configuration Export). For example, you might create and test transformation queries in your Sandbox instance and then move them to your Production instance.

When you create a package, a new category has been created for Transformation Queries. Expand the category and choose the queries that you want to add to the package.