Purpose

This article will provide instructions on how to create, modify, search and delete Data Views on the Process Design Studio tool.

Definition

Data Views are queries that can join multiple data sources, used for Workflow rules and/or Analytics and Reporting.

Create

In order to create a Data View, click on the + New Data View button.

• Data Views can be created for two different purposes: Workflow Rules and Analytics and Reporting.

Workflow Rules

Select this purpose if you would like to use your Data View as part of a Workflow Rule.

1. Create Data View

After selecting the Data View Purpose, fill in the Name and Description fields.

Select the Event Source that will trigger the Data View. All previously created Events will be available on the list.

Data transformation is the format in which the data will be converted and displayed on the Data View. Select between Aggregate Values and Original Values.

• Original Values: Your data will only be displayed in Group by mode, which groups the rows that have the same values into summary rows.
• Aggregate Values: It will allow you to select between Group By and any of the aggregate functions (MIN, MAX, AVG, SUM, and COUNT).

Select one or more Processes to relate the Data View with. The options available here are taken from the list of Processes previously created.

After all fields are completed, click on Next.

2. Event Sources

Select the main source for your comparisons. Your source can be a Data Warehouse or another Data View.

Once you set the type of source, all the available Data Warehouses or Data Views will be available under Data Source Name for selection.

After selecting the main source, click on the + New Data Source button.

The first line of comparison will appear. Select the source you want to compare it with.

Select a field for comparison from the main source.

Select the type of comparison you want to apply between fields.

The comparison options available on the drop-down list will be subjected to the type of data contained in the first field.

• = : Equal
• <> : Not Equal
• > : Greater Than
• < : Less Than
• >= : Greater Than or Equal
• <= : Less Than or Equal
• Range
• contains
• not contains
• begins with
• ends with

Select a field for comparison from the second source.

• If you select incompatible fields for comparison, you will receive an error message on the top of the screen and won't be able to continue. Make sure you select fields that contain the same kind of data.

• You can add more comparison lines by clicking on the "+" icon and remove them with the trashcan icon.
• New comparisons added through the "+" icon will be related to the joined source you selected on the first line.

• You can also add more join sources for comparison by clicking on + New Data Source. Then fulfill the line as done with the previous ones.

• Joins can be deleted by selecting them on the left side of the item and clicking on Remove Selected.  The first line cannot be removed, but it can be modified if needed by simply changing the Data Source Type or Data Source Name.  This will remove all lines on the screen. 

• If you need to change the primary source of the comparison, select a different Data Source Name on the top and all linked comparisons will disappear from the screen.

After all fields are completed, click on Next.

3. Selected columns

Within this step you will set all the fields (columns) you would like your resulting table to display. In order to start building a Column, click on the + New Column button.

Field Type

The options available on this drop-down menu will be subjected to the Data Transformation selection done on the first step.

• Aggregate Values: You can add all necessary fields with a single Field Type at once, but you will need to open different Column Builders for each kind of data.  This is because each Field Type has different Available Functions.
• Original Values: Since only Group By option is allowed, there will be no Available Functions, therefore there is an "All" option, in order to enable all Fields available, regardless of their Type.

Available Fields

The drop-down list will show all Fields available from all the sources selected in the previous step. Select as many as needed.

Group or Aggregate

The Aggregate option will only be available if it was previously selected on step #1, under Data Transformation, otherwise, you are only able to select Group By.   

• Group By: Groups rows that have the same values into summary rows.
• Compute Aggregate Value: This allows you to utilize any of the aggregate functions with the (MIN, MAX, AVG, SUM, and COUNT).

Available Functions

• Min. returns the smallest value of the selected column
• Max. returns the largest value of the selected column
• Avg. returns the average value of a numeric column
• Sum. returns the total sum of a numeric column
• Count. returns the number of rows that matches a specified criterion

The functions available on the drop-down list will be subjected to the type of data contained on the field type you selected.

After building the columns, you can still edit them individually by clicking on the pencil icon on the right side of the item.  

Fields available for selection are Group or Aggregate and Available Functions.

Items can be removed by selecting them on the left side of the chart and clicking on Remove Columns.

Two columns cannot have the same Alias, the box will show a red outline and won't allow you to continue.  Change the column's Alias by overwriting the Alias field.    

The same Source Name can be selected multiple times as long as the Function varies from one column to the other.  

Once your selection is done, click on Next.   

4. Select Order By

Establish the priority of your columns by clicking on the box to the left of each item from the first desired column until the last one.  

The Type option will be enabled as you establish the order of the items.   

• ASC: sorts the result from the lowest value to the highest value
• DSC: sorts the result set from the highest value to the lowest one

Once the order is set, click on Next.

5. Data Filters

Within this step, you will establish the comparison between previously selected sources and, at least, the Event Source chosen on the first step.  You can also add comparisons between sources and specific values, as well as comparisons between them.

In order to start building a Data Filter, click on + New Row.   

Choose between the Data Sources selected on step #2.

Select a Field for comparison.   

Select the type of comparison you want to apply between fields, which will vary depending on the field type.  

Select a second source for the comparison on that line.

• Value: Specific value you will manually set
• Event Source: The only Event available will be the one added on the first step
• Data Source: Choose between all sources used in the previous steps

• For a Data View with Workflow Rule Purpose, it is mandatory to create at least one comparison with the Event Source selected on the first step.

Operators

When more than one line of comparison is added, they can be separated with operators in order to indicate the server exactly what needs to be searched and compared, as well as in which order.

• And: all comparisons, or sets of comparisons, followed by AND need to be TRUE in order for the query to be TRUE. 
• Or: if at least one of the comparisons, or set of comparisons, followed by OR is TRUE, the query will be TRUE.
• Parentheses: are available in case you need to set priority between the different lines or set of lines. Once you open the parenthesis at the beginning of a line, it needs to be closed at the end of the same line or at the end of the group of lines you would like to prioritize. 

• Lines can be deleted by selecting them on the left side of the item and clicking on Remove Selected. 

Once all steps are completed, click on Create for Data View creation.  

A success message will appear on your screen. Click Back to be redirected to the Data Views page.

Analytics and Reporting

Select this purpose if you would like to use your Data View for Analytics and Reporting.

1. Create Data View

After selecting the Data View Purpose, fill in the Name and Description fields.

Data transformation is the format in which the data will be converted and displayed on the Data View.  Select between Aggregate Values and Original Values.

• Aggregate Values: It will allow you to select between Group By and any of the aggregate functions (MIN, MAX, AVG, SUM, and COUNT).
• Original Values: Your data will only be displayed in Group by mode, which groups the rows that have the same values into summary rows

Select one or more Processes to relate the Data View with.  The options available here are taken from the list of Processes previously created.   

After all fields are completed, click on Next.

2. Event Sources

Select the main source you will compare other sources with, in order to generate the Data View.

Your source can be a Data Warehouse or another Data View.  

Once you set the type of source, all the available Data Warehouses or Data Views will be available under Data Source Name for selection.   

After selecting the main source, click on + New Data Source.  

The first line of comparison will appear.  Select the source you want to compare it with.   

Select a field for comparison from the first source.   

Select the type of comparison you want to apply between fields.

The comparison options available on the drop-down list will be subjected to the type of data contained in the first field.

• = : Equal
• <> : Not Equal
• > : Greater Than
• < : Less Than
• >= : Greater Than or Equal
• <= : Less Than or Equal
• Range
• contains
• not contains
• begins with
• ends with

Select a field for comparison from the second source.

• If you select incompatible fields for comparison, you will receive an error message on the top of the screen and won't be able to continue.  Make sure you select fields that contain the same kind of data. 

• You can add more comparison lines by clicking on the + icon and remove them with the trashcan icon.
• New comparisons added through the "+" icon will be related to the joined source you selected on the first line.    
• You can also add more sources for comparison by clicking on + New Data Source.  Then fulfill the line (Join) as done with the previous ones.  
• Joins can be deleted by selecting them on the left side of the item and clicking on Remove Selected.  The first line cannot be removed, but it can be modified if needed by simply changing the Data Source Type or Data Source Name.  This will remove all lines on the screen.

• If you need to change the primary source of the comparison, select a different Data Source Name on the top and all linked comparisons will disappear from the screen.  

After all fields are completed, click on Next.

3. Selected columns

Within this step you will set all the fields (columns) you would like your resulting table to display.

In order to start building a Column, click on + New Column.   

Field Type

The options available on this drop-down menu will be subjected to the Data Transformation selection done on the first step. If the option selected was:

• Aggregate Values: You can add all necessary fields with a single Field Type at once, but you will need to open different Column Builders for each kind of data.  This is because each Field Type has different Available Functions.
• Original Values: Since only Group By option is allowed, there will be no Available Functions, therefore there is an “All” option, in order to enable all Fields available, regardless of their Type. 

Available Fields

The drop-down list will show all Fields available from all the sources selected in the previous step.  Select as many as needed.    

Group or Aggregate

The Aggregate option will only be available if it was previously selected on step #1, under Data Transformation, otherwise, you are only able to select Group By.   

• Group By: Groups rows that have the same values into summary rows.
• Compute Aggregate Value: This allows you to utilize any of the aggregate functions with the (MIN, MAX, AVG, SUM, and COUNT).

The Available Functions available on the drop-down list will be subjected to the type of data contained on the field type you selected.

• Min. returns the smallest value of the selected column
• Max. returns the largest value of the selected column
• Avg. returns the average value of a numeric column
• Sum. returns the total sum of a numeric column
• Count. returns the number of rows that matches a specified criterion

After building the columns, you can still edit them individually by clicking on the pencil icon on the right side of the item.   

Fields available for selection are Group or Aggregate and Available Functions.

Items can be removed by selecting them on the left side of the chart and clicking on Remove Columns.

Two columns cannot have the same Alias, the box will show a red outline and won't allow you to continue.  Change the column's Alias by overwriting the Alias field.  

The same Source Name can be selected multiple times as long as the Function varies from one column to the other.  

Once your selection is done, click on Next.   

4. Select Order By

This step does not apply to this Purpose.  Click on Next to continue.  

5. Data Filters

Within this step, you will establish the comparison between previously selected sources and specific values, as well as comparisons between them.

In order to start building a Data Filter, click on + New Row.

Choose between the Data Sources selected in step #2.   

Select a Field for comparison.   

Select the type of comparison you want to apply between fields, which will vary depending on the field type.   

Select a second source for the comparison on that line.

• Value: Specific value you will manually set
• Data Source: Choose between all sources used in the previous steps  

Operators

 When more than one line of comparison is added, they can be separated with operators in order to indicate the server exactly what needs to be searched and compared, as well as in which order.

• And: all comparisons, or sets of comparisons, followed by AND need to be TRUE in order for the query to be TRUE. 
• Or: if at least one of the comparisons, or set of comparisons, followed by OR is TRUE, the query will be TRUE.
• Parentheses: are available in case you need to set priority between the different lines or set of lines. Once you open the parenthesis at the beginning of a line, it needs to be closed at the end of the same line or at the end of the group of lines you would like to prioritize. 

Lines can be deleted by selecting them on the left side of the item and clicking on Remove Selected. 

Once all steps are completed, click on Create for Data View creation.   

A success message will appear on your screen.  Click Back to be redirected to the Data Views page.

Modify

In order to modify a Data View, find the item on the list and click on the pencil icon on the right side of the line.  All settings can be edited.   

If the Data View's item has a padlock icon on the left side, it means it is being used by a Workflow or another Data View.  A message appears when you open the item for edition and the modifications are not allowed.   

Search

When using the search box on the top left of the screen, the page searches for the text under Data View Name as well as under Description.

Delete

In order to delete a Data View, select the item on the list and then click on the Delete Data Views bottom on the top right of the screen.  

• If the item has a padlock icon, it cannot be selected for deletion.

 You will be prompted to confirm the deletion and then redirected to the Data Views page.