Need to create an integration that is too complex to achieve with a standard integration channel setup? Use VenaQL to combine sources and perform advanced data transformations.

 

Why use this feature?

VenaQL provides you with a SQL-like query language for advanced data transformation. With VenaQL, you can perform tasks like joining multiple tables, pivoting datasets and building complex logic involving multiple tables. Additionally,  you can use VenaQL to consolidate data from multiple sources and build logic based on certain values in a table (e.g., in-transaction matching, balance sheet activity calculations and dynamic allocations.) 

 

Before you begin

To follow the instructions in this article, you must have Modeler access. If you are unfamiliar with the Vena Integration tool, please read the Integration article series before proceeding.

VenaQL uses a syntax similar to the Queries element of SQL, so familiarity with SQL — particularly SELECT statements—is essential when performing VenaQL queries. Please refer to the notes at the end of this article for more details.

 

Table of contents

About the feature 

• What is VenaQL?
• How do VenaQL sources work?

How to

• Open the VenaQL Query Editor
• Overview: VenaQL Query Editor
• Creating VenaQL queries with the Query Editor
  

Reference Guide

• Best Practices
  • Use Case 1: Sign Flipping
  • Use Case 2: Drill-Through Transaction Tables
• Supported functions
• Source availability
• Notes and limitations
• VenaQL Cheat Sheet
• Mini FAQ

 

 

About the feature

What is VenaQL?

VenaQL is a query language. With VenaQL,  you can query integration sources and permit complex joins, filtering, sorting and other transformations supported by SQL without the complexity associated with managing a database. Within the Integration tool, VenaQL acts as a type of Integration source: a data stream that can feed into an Integration channel, and from there to a destination like a Vena database. But VenaQL is completely unlike the other source types because a VenaQL source itself does not contain any data. Instead, VenaQL provides a framework for manipulating data drawn from other sources, including joining two or more sources, performing multi-step procedures that reference multiple sources and more. VenaQL allows for data transformations beyond those that were previously available with standard Integration channel field mappings. It also eliminates the need for chaining channels when multiple sources need to be referenced.

 VenaQL allows users to leverage VenaQL to provide a framework for manipulating data drawn from other sources (including Vena Tables).

 

How do VenaQL sources work?

VenaQL sources make complex data transformations possible by moving the data processing out of the field mappings and into the source itself. With a VenaQL source, all of this processing work (drawing from sources, joining, transforming, etc.) occurs before data is output from the source to the channel. VenaQL sources, therefore, output data that is fully ready to be integrated into the destination.

This also means that you configure integration jobs involving a VenaQL source almost entirely within the source. To do this, you write VenaQL queries using the VenaQL Query Editor, which is built into the Integration tool and acts as the primary user interface for VenaQL. VenaQL queries are very similar in most respects to SQL SELECT statements (with certain restrictions), except that the FROM clause is used to identify the sources from which you want to draw. VenaQL queries may reference existing Integration sources of any type, including other channels, or even other VenaQL sources.

VenaQL allows you to use an existing source in the same way that you would use a table in standard SQL. However, in VenaQL, you directly reference a source in the FROM clause, and auto-staging takes care of the rest, extracting data from the source to a staging table as needed. This not only makes the query easier to write but also eliminates the requirement to separately stage the data you want to use. Another benefit of VenaQL is that auto-staging captures data snapshots in tables; this ensures that with multi-step ETL jobs data consistency is maintained across all steps of the job. Currently, the system may pull the same data multiple times for a single job. This will result in errors if the source data changes between data pulls.

 

 

How to

Similar to our existing Integration source types, you can create and modify VenaQL sources through the Transform & Load tab in the Integration tool. To work with VenaQL sources, you will leverage the VenaQL Query Editor, which will open whenever you view a VenaQL source. The VenaQL Query Editor also includes functionality to help you develop and validate your queries.

 

To open the VenaQL Query Editor

1. Navigate to the Modeler tab.
   
2. Select Data Transformations from the sidebar.
3. Select Channels from the sidebar tab.
4. Select the Create button in the top right-hand corner of the screen.
5. Hover over Source, and a sub-menu will appear. In this sub-menu, select VenaQL:
   
6. This opens the VenaQL Query Editor.
   
7. Begin by typing in a name for this source in the New Source text field at the top. Once you have entered a name, select the Save button to save your source.
   
   Once you select Save, it will change to a Saved indicator to confirm that your changes have been saved. As soon as you make any further modifications to the source, it will change back to the Save button to prompt you to save new changes.
   
   
8. Select the Close button in the top right corner to close the VenaQL Query Editor and return to the Set up tab.
   

   Caution

   Remember to save before closing! Any changes you have made to the source since you last clicked on Save will not be saved and will be lost as soon as you click on Close.

9. From the Set up tab, select the name of a VenaQL source (identifiable by the VenaQL logo in the Source Type column) to reopen the VenaQL Query Editor and view that source.
10. With the source open in the Query Editor, you can begin building your query.

 

 

Overview: VenaQL Query Editor

A. Source name field: Used to enter a name for the VenaQL source. You cannot save a VenaQL source without naming it.

B. Channels and Sources Pane: Lists all available channels and sources which can be referenced in the VenaQL query. Also allows you to extract/preview individual sources. See the note below on source availability.

C. Query Pane: Used to create the VenaQL query using VenaQL syntax. See the Reference Guide below for help.

D. Refresh All button: Refreshes all source data for the VenaQL query. Must be done at least once before previewing the source.

E. Preview Pane: Displays previews of the output of the VenaQL query as well as source previews. The VenaQL query output is displayed under the Current Source tab, while a source preview is shown under an additional tab with the name of the previewed source. The Current Source tab is always visible; only one source preview tab may be viewed at a time (in addition to the Current Source tab).

F. Preview VenaQL source button: Generates a preview of the output of the VenaQL source, using the query as currently configured in the Query Pane. Once generated, this preview is displayed under the Current Source tab in the Preview Pane.

G. Save button: Allows you to save the query, regardless of state (including incomplete/invalid queries). When checked, the Save and Validate option will also validate the query when saving (this option is unchecked by default).

H. Pane resize handles: Click-and-drag to resize each of the Channels and Sources, Query and Preview Panes.

I. Close button: Closes the VenaQL Query Editor and returns you to the Integration Set up tab.

 

Creating VenaQL queries with the Query Editor

Similar to a SQL SELECT statement, a VenaQL query always follows the basic SELECT statement structure: SELECT x FROM y (note that optional SQL SELECT clauses like WHERE are also supported; see the Reference Guide below for more information).

 

A VenaQL query selects specific, pre-defined rows from a table of data — you don’t need to create and manage tables, that is done automatically within the application.  In its most basic form, a SQL SELECT statement does this by specifying one or more table columns (i.e., SELECT x) along with the name of the SQL database table to query (i.e., FROM y) to select the matching rows of data. Because Integration sources are also data tables, a VenaQL query works the same way: with VenaQL, you simply specify an Integration source instead of a SQL database table.

The general process for creating a VenaQL query is as follows:

1. Extract and preview the sources you want to reference in the query.
2. Using the Query Pane, write the VenaQL query according to SELECT statement syntax and structure.
3. As needed, reference source names and column names in the query by copying and pasting from the Channels and Sources Panel and the Preview Panel.
4. Once you have completed the query, use the Save and Validate function to validate it. You can also use Save without the validation option to save your work in progress at any point.
5. Debug and review the query as needed.

We'll look at each of these steps in more detail below. Once your query is finished, you can then deploy it by using its corresponding VenaQL source as part of an Integration channel.

 

Step 1: Extracting and previewing sources

In order to write a VenaQL query, you must reference the appropriate column names in the source(s) from which you want to select data. It should be noted that SELECT * is not supported in VenaQL. VenaQL sources are meant for setting up automated data transformations and to ensure the efficiency of these transformations, it is important to explicitly identify what data it is pulling in. The Query Editor has a built-in source preview function to enable you to view (and one-click copy) column names. Please refer to the list of limitations at the end of this article for more details on which queries VenaQL supports.

 

In the VenaQL Query Editor, hover your mouse over the source you want to preview in the Channels and Sources Pane. You will see three additional options appear. From left to right, these options are:

1. Copy to clipboard: Copies the name of the source to the clipboard, allowing you to easily paste it into the query rather than typing it out.
2. Preview: Displays a preview of extracted data from the source in the Preview Pane.
3. Refresh data: Refreshes the data from the source and moves it to a staging table, allowing it to be previewed.
   

1. Before you can preview a source, you must first extract its inputs to the staging area. To do this, select the (Refresh data) button. This will open a tab in the Preview Pane for that source, and a message will be displayed indicating that the refresh is in progress:
   
   
2. You can check on the progress of the extraction by visiting the History tab of the Integration tool. Go there directly by selecting the provided Jobs link in the Preview Pane.
3. Repeat this extraction process for any other source you want to use in your query.
4. When the extraction jobs show as Completed, you can return to the Query Editor and select Preview next to each of the extracted sources:
   
   
5. This opens a tab for that source in the Preview Pane, but will populate with preview data from that source. All of the column names are also shown:
   
   
6. With your sources extracted, you can now proceed to the Step 2: Write the VQL query. 

 

Step 2: Write the VQL query

With the source and column names at hand, you now have everything you need to begin writing the query.

1. In the VenaQL Query Editor, click anywhere within the Query Pane. The cursor should begin blinking, indicating that the Query Pane is active.
2. Begin typing your VenaQL query (e.g., SELECT...):
   
   
3. For syntax help, see the Reference Guide.

 

Step 3: Reference source and column names with copy/paste

When you need to reference a source or a column name in the query, you do not need to type them in manually. Instead, copy and paste to save time and avoid typos.

1. When you need to reference a source name in your query, hover your mouse over that source in the Channels and Sources Pane and select the (Copy to clipboard) icon.
   
   
2. Select the Query Pane and move the cursor to the place where you want to insert the name and press Ctrl+V on your keyboard to paste the name in:
   
   
3. When you need to reference a column name in your query, hover your mouse over the relevant source in the Channels and Sources Pane and select the (Preview) icon:
   • Remember that you must refresh the data first.
     
     
4. When the preview appears in the Preview Pane, select any column name to copy it to the clipboard:
   
   
5. Select the Query Pane and move the cursor to the place where you want to insert the name and press Ctrl+Von your keyboard to paste the name in:
   
   

 

 

Step 4: Preview and validate the query

When your query is finished, you can preview it and check that it is valid.

1. At any time, you can save your work by selecting the Save button in the top-right corner of the VenaQL Query Editor:
   
   
2. When you save, you also have the option of validating the query at the same time. To do this, simply check the box next to Save and Validate.
   
   
3. When you select Save with the validation box checked, the VQL Query Editor will perform a validation of the query, checking for syntax errors, whether the source is valid, etc. Once done, it will display the results in a new window:
   
   
4. You can also preview the query at any time to check if it is returning the expected results in the current configuration. To do so, select the Preview button in the top right corner. The preview will appear under the Current Source tab in the Preview Pane:
   
   
5. In order for the Preview function to work, you must have first refreshed the data referenced in the query, as described in Step 1.

 

Step 5: Debug and finish

Use the validation and preview functions to debug your query as needed. When it is functioning correctly (returning the expected results), you can save the query and close the VQL Query Editor by selecting the Close button in the top right.

Your completed VQL source will appear in the Setup tab, and you can deploy it as part of an Integration channel like any other source.

 

 

Reference guide

 

Best practices

We recommend that you insert comments into your VenaQL code to describe the purpose of your VenaQL source. This helps to provide context for your team as well as for Vena Support in the event that they are required for troubleshooting.

 

Recommended details:

• Author, date, description, purpose
• Remember to start with "--"

Example:

----------------------------------

-- Author:

-- Date:

-- Description:

-- Flip signs for Accounts start with 2, 3, and 4

----------------------------------

 

Use case 1: Sign Flipping

Certain accounts need to have their sign flipped depending on how they are recorded in the source system. Logic is required to flag the accounts that should be flipped from negative to positive in order to roll up efficiently. In the example below the logic dictates that if the account number starts with 2, 3 or 4 then flip the sign (Multiply by -1).

 

VenaQL Query

----------------------------------

-- Author:

-- Date:

-- Description:

--   Flip signs for Accounts start with 2, 3, and 4

----------------------------------

SELECT

     "Account",

     "Entity",

     "Department",

     "Placeholder 1",

     "Placeholder 2",

     "Placeholder 3",

     "Placeholder 4",

     "Year",

     "Period",

     'Actual' as "Scenario",

     "Currency",

     "Measure",

     "Value",

         CASE WHEN LEFT("Account",1) in ('2','3','4') THEN -1 * Cast("Value" as decimal(18,2))

         ELSE CAST("Value" as decimal(18,2)) END

       as "Amount",

       "Document Type",

       "Document Voucher",

       "Reference"

FROM   "ts_GL_Data"

 

Channels and Sources to use

• On the left there are channels and sources available for you to reference in your Query.
• Sources can be refreshed one by one or sources can be entered in the query and the Refresh all button will refresh all nested sources in the query.
  
  1. State the columns to Query. 
  2. From denotes the table the columns are coming from. 

 

Cast

• Why? All values through VenaQL are treated as strings. As such, all numeric values must be cast into a numeric form. We recommend that you double-check any columns that require summation in the cube such as value, salary, amounts, rates and percentages
• How? (see string, below)

Cast("Value" as decimal(18,2))

 

Rule-based formatting

-- Think IF value in a column is x then present as y otherwise z

         CASE WHEN LEFT("Account",1) in ('2','3','4') THEN -1 * Cast("Value" as decimal(18,2))

         ELSE CAST("Value" as decimal(18,2)) END

 

Rule-based math

Often for allocations clients use +,-,/,x to apply a calculation column-wide. To use math in specific cases use a Case When clause.

Example: 

	Issue: My data is in different tables but I only want one on a Template Multiple
	Solution:  Multiple tables can be joined on columns that they have in column (See left join example)

 

 

 

 

Use case 2: Drill-Through Transaction Tables

As part of their processes, some users may want to add sub-GL level details that do not necessarily have to live in the Vena cube as intersections. Instead, these details can be uploaded to a table and referenced through a drill-through table in a template. Users typically use these to store project details or sales details such as contract numbers or Salesforce invoice references.

• Create a venaQL to consolidate and transform data:
• Sample case: My data is in different tables but I only want one on a Template Multiple tables can be joined on columns that they have in the column (See left join example). 

 

Set up

For the following steps you will need Manager access to the process that contains the template you would like to add the table to. You will need Modeler access and Application and Data permissions to the model assigned to the Vena Table you would like to add.

1. Create a Vena Table.
2. Ensure that the first set of Vena Table fields refers to Model Dimensions in order. The subsequent columns can support documentation needed for transactions.
   
   

   Note

   If the Vena Table requires transformations to get the data into a format that matches members, create a destination to use in a Vena Table Channel.

3. Adding Clear Slices: To ensure that no duplicates are added to the Vena Table, dynamically clear rows based on incoming values from the file. For more information, read this article to see Vena's Clear Slices documentation.
4. Next, add your Vena Table to your desired template. To do this in Vena Desktop, select Data Models, then click Set Drill Transaction Tables from the drop-down menu.
5. In the pop-up window select the Section and Block you would like to apply the Drill Through Transaction Table to, select the appropriate Table to display in the drop-down and select OK.

 

 

 

 

Using a Drill Through Transaction Table through a template

1. Select a cell in a Drill Through Transaction Table-enabled section and block it in your template.
   
   
   
   
   
2. Navigate to the Vena Desktop ribbon, select Drill, then select Transaction from the drop-down menu.
   
   
   
3. A table should appear in an additional tab showing the records that roll up to that intersection. Note the dimensions in the first set of columns and the transaction details that follow.
   
   

 

 

 

Supported functions:

String functions

• TRIM, LTRIM, RTRIM
• LEFT, RIGHT, SUBSTRING
• CHARINDEX, STRPOS, LENGTH
• CONCAT, REPLACE
• UPPER, LOWER

 

Aggregate functions

• COUNT, AVG, MIN, MAX, SUM
• STDEV, STDEVP, VAR, VARP

Other functions

• CAST, COALESCE

Source availability

Any source with a unique name can be queried. However, it should be noted that all sources must receive unique identifiers as two sources with the same name cannot be queried. It is also worth noting that channels are considered sources and you can reference the output of a channel from VenaQL.

 

Notes 

While VenaQL empowers users to manipulate data independently, there are a few limitations that users should be aware of when using this tool.

 

1. Users can insert multi line comments into VenaQL using the `/* <multi line comment> */` syntax.
2. Pivot/unpivot syntax is not yet available. However, this functionality can be achieved by VQL queries.
3. The columns are all currently TEXT.
4. Expressions can only be TEXT or DECIMAL at this time. Additionally, DATE and TIME types are not yet available.
5. Existing staging data and procedures are not automatically available via Redshift; they must be ported over by Vena Consultants. If you would like to move existing staging data and procedures please reach out to your assigned Customer Success Manager.
6. VQL supports only SELECT statements. Users cannot create, update, delete, or insert tables.
7. Foreign keys, triggers, stored processes and constraints are not yet supported.
8. SELECT * is not supported. NOTE: This is intentional and it will never be supported in VQL.
9. Users cannot preview VQL sources that reference other VQL sources without first extracting all sources to staging.
10. Windows functions are not yet supported. 
11. The following functions are supported:
12. Users can only comment out lines with --
13. Multi-line comments will not be supported. 
14. It should be noted that VenaQL uses case-sensitive string comparison; this means that if you have text values whose cases must be ignored, you can use the LOWER or UPPER functions to normalize the case. Using them in the SELECT statement is recommended because then the normalized values will be stored that way and subsequent processing will be simpler and faster. However, if you want values stored in the original case, then they will need to use LOWER or UPPER in their WHERE, HAVING, ON,and ORDER BY clauses. You will also want to normalize the case of data reaching a Vena Table via file import or a Vena Table Destination in an integration channel. 
15. The query SELECT DISTINCT is supported by Vena.

VenaQL cheat sheet

 
Sample Query

--Joint table multiply allocation values and ratesselect [av].[Entity], [av].[Year], [av].[Period], [ar].[Department],
		(CAST([ar.[AllocRate] as Decimal(18,2))* CAST([av].[value] as Decimal(18,2))) as NewValue
from "Allocation Values Source" as av
left join "Allocation Rates Source" as ar
on [av].[Entity] = [ar].[Entity]
and [av].[AllocType] = [ar].[AllocType]

 The top line marked by [--] indicates comments. However it should be note that queries only support one (1) line comments and you may not type a comment line as the last line in a query. If you do, the query will not save.