Transforming outbound data

AD
DM

Network can transform data so you can export it in the format that downstream systems can easily receive. You can create SQL queries to transform the data within Network before exporting it using a target subscription instead of using a third-party ETL tool to post-process Network data.

Queries can also be used to pre-process data before it is processed and loaded in source subscriptions. For more information, see Transforming inbound data.

Examples of outbound data transformations

Use transformation queries to post-process data.

Examples:

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
Take a snapshot of the current data

Export an object or a table so you can take a snapshot of the current data. The transformation query reads from the file export or table and persists the query output as a custom table.
Prevent intermediate files in target subscription export packages

Use the custom table output type for sequenced queries so unnecessary files are not exported.
Import flat hierarchy table to data warehouse

Use transformation queries in your target subscription to include the flat_hierarchy reporting table to your data warehouse.
Post-process DCR export files (third-party owned DCRs)

Transform the DCR export files so you can export it in the format that third party systems expect to receive.
Post-process source view files

Save the exported source data. For example, save the output as custom table so you can report on data lineage.

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

How transformation queries work

Use queries for the following activities:

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

Reporting database considerations

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.
Reference aliases are only applicable to source and target subscriptions. They do not apply when extracting data from the reporting database. If you have a requirement to apply reference aliases, run your transformation query against data exported through the target subscription.

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 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:
- Data is extracted - 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.
- Queries are applied - The transformation queries are injected into the job.
- Files are generated- The files exported by the target subscription and/or the query output files are packaged and placed on the Network FTP server.
  
  Tip: If the subscription uses multiple queries, prevent the output files from being exported to downstream systems by creating custom tables as the output of the intermediary queries. This ensures that the data remains in Network as a custom table and unnecessary files are not exported. See Prevent intermediate files in subscription packages.

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.

Letter case considerations

Transformation queries make all column headers lower case. For example, if the URL_1__v field is included, the query output changes the column name to url_1__v. If column name casing matters in downstream systems, explicitly define column aliases (AS statement) in the query.

Example:

 select url_1__v as URL_1__v from hcp

This will ensure that the column name has uppercase characters in the query output.

Adding the transformation query

In the Admin console, click System Interfaces > Transformation Queries.
Click Add Query
Type a Name and Description.
In the Query Output section, select CSV File, Custom Table, or both options.

Example - both output types
- CSV File - If selected, type a File Name for the output file. The file name is automatically appended with .csv.
  
  File and table 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
- Custom Table - If selected, complete the following:
  - Type a Table Name and Table Description. The table name will automatically be appended with the __ct suffix.
  - Choose the folder where the table will be saved. If you have folders created in the Shared Folders category in the SQL Query Editor, they will display in this list.
    
    Transformation queries cannot be saved to personal folders (My Custom Tables) because the data is loaded through subscriptions, which are not applied to users.
  - Choose Table Name Options.
    - Static table name - The table name is always the same, so it means any existing custom table with the same name is replaced each time the query runs. For example: hco_table__ct
    - Add timestamp to table name - Append a timestamp to the table name. For example: hco_table_20221102t045341z__ct.
    - Add job ID to table name - Append the subscription job ID to the table name. For example: hco_table_15953__ct
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.
- 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.
  Examples
  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
)
```
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, query type (inbound or outbound), 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.

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 Add a target subscription.
File & Field Selection section - Choose one of the following options. All objects will be set to that option.

Tip: You can select one of the options to change all objects and then choose another option. For example, if you want to export only Payer and Plan objects, select Export None to change all of the objects, and then click Select Which Objects and Fields to Export to change the Payer and Plan objects to Export All Fields.
- 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 that displays, 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.

The Query Output column lists the .csv file and/or custom table that will be created when the job runs. If the file name is truncated, hover over the column to see a tooltip with the complete file names.
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.

Transform DCR export files

Transformation queries can post-process DCR export files (third-party owned DCRs) so you can export it in the format that third party systems expect to receive.

Example

To transform third-party owned DCRs after the files are exported, create a transformation query that references the DCR export files:

DCR_BATCH (header file)
DCR_FIELD_BATCH (line item file)

In the transformation query, omit the UNIX timestamp that is automatically applied to the export files in the archive (for example, a file name is DCR_BATCH_1669990415331.csv).

Add the __csv extension to each file name in the query.

This query joins the header file with the field file to produce a single export file instead of two.

In the DCR type target subscription, add the transformation query to the configuration.

After the job runs, you can view the Job Details to see the records that were exported and the transformation queries that were applied.

Job details

Note: The Data Flow Diagram that displays on Data type target subscriptions is not available on DCR type subscriptions.