List of transforms

Contents[Hide]

1. Overview

This article describes the transforms you can use when creating a data cube (ETL process).

When editing a data cube, most transforms can be accessed from the toolbar depending on your current canvas selection. For example, to insert a Join transform, first select the connection link between two already connected transforms.

2. Input transforms

TransformDescriptionInputs
Data Cube This transform uses the output of another data cube as an input, created when you drag another data cube onto the canvas. N/A
Data Input Reference a warehouse data storage area containing user input data. You can use the Data Input transform to add and modify data from inside Dundas BI. N/A
Manual Select Enter a SQL or MDX statement to make a selection from a data connector instead of dragging native structures onto the canvas. N/A
MDX Select Drag and drop to select a tabular data result from an OLAP cube as input to a Dundas BI data cube. N/A
Python Data Generator Generate data by writing scripts using the Python programming language. N/A
R Data Generator Generate data by writing scripts using the R statistical programming language. N/A
SQL Select This transform is created when a structure is dragged onto the canvas from a data connector that supports relational queries. The columns to be used in the data cube can then be selected or unselected and aggregators can be set up per element. When retrieving data, these settings will then be translated into a SQL statement and sent to the data source. N/A
Stored Procedure Select This transform lets you retrieve data using a relational stored procedure. It is created automatically when you drag a stored procedure from a data connector in Explore to the data cube canvas. N/A
Tabular Select Similar to the SQL Select, this transform is created when dragging structures onto the canvas from a data connector that supports tabular data (e.g. XML, CSV). N/A

 

3. Common transforms

TransformDescriptionInputs
Aggregate Apply aggregate functions to the columns of the previous transform such as SUM, AVG, COUNT, MIN, and MAX. Also allows setting GROUP BY functionality for the columns that are not aggregated. 1
Calculated Element Create a new column by supplying an expression. The expression can contain placeholders representing columns using $columnName$ notation, and use functions such as MEDIAN and RATECHANGE. 1
Data Conversion Change the data type of a column to another data type. For example, data coming from a CSV file may be recognized as a String but it is known to be an Integer. 1
Filter Filter out rows that do not meet the configured criteria/settings. For example, use this transform when all of the data being read from a data source is not required and the data source doesn’t allow querying (e.g. XML). Another example usage would be to place a filter on an ID column with the setting Less Than and the value 1000 which would result in taking only records with an ID value of less than 1000. Available filter operators are: Equals, Not Equals, Greater Than, Greater Than Or Equals, Less Than, and Less Than Or Equals. 1
Join Join two tables by defining the keys and specifying the join type. If relationships exist between the two tables the link is automatically created but this can be changed if necessary. If the data connectors of the transforms being joined are the same and the corresponding data provider supports joining then an optimized query will be generated and sent to the server. 2
Lookup Join data from input columns with columns in a lookup table. 2
Math Perform simple math functions such as Absolute, Ceiling, and Square Root on numeric input columns. 1
String Manipulate string columns in data tables by applying functions such as To Upper, To Lower, Substring, Trim, Left, and Right. 1
Union Combine data from multiple input structures by mapping columns onto one another. If the structures come from the same data connector and that data provider supports union statements then an optimized query will be generated and sent to the server. 2+

 

4. Other transforms

TransformDescriptionInputs
Copy Element Create new columns by copying selected input columns and adding the new columns to the output. This can be used in cases where you need to manipulate a column but want to keep the original column as well. 1
Flatten JSON The Flatten JSON transform turns rows of data containing JSON text into separate columns for each of its values. 1
Flatten XML The Flatten XML transform turns rows of data containing XML text into separate columns for each of its values. 1
Fusing The Fusing transform combines the rows and columns of two tables together by appending them. 2
Fuzzy Grouping Allows grouping of records by looking at the similarity between the values of various columns. Two records in which a possible misspelling occurs can be grouped together for further analysis, or duplicates can removed by setting Output Top Level Records Only. The sensitivity can be set adjusted by setting the Probability Threshold. 1
Fuzzy Lookup Search for a matching record from a secondary table when no relationship key fields are defined between two tables. 2
Merge Merge values from multiple columns into one. This might be useful after a full outer join where one column may be null. 2+
Null Replacement Replace input data with user-specified values when null values are found. Use this as an alternative to a Calculated Element transform which requires you to write a script. 1
Percentage Sampling By specifying a rate, this transform reads in all of the data from the previous transform and generates a set of random indexes according to the rate input multiplied by the total record count, and then outputs the records according to those indexes. 1
Pivot Allows for creation of new columns and transposing data into a new layout. Can be used to make results more compact by pivoting the input data on a column value. 1
Python Analysis Write scripts using the Python programming language to perform statistical and predictive analysis on data. 1+
R Language Analysis Write scripts using the R programming language to perform statistical and predictive analysis on data. 1+
Rank Allow a rank number to be assigned to a new element created by the transform. 1
Record Sampling Reads in all of the data from the previous transform and generates a set of random indexes according to the number input. Then output the records according to those indexes. 1
Remove Duplicates Remove duplicated records by grouping all of the selected input columns and copying the results to the output. 1
Sort Apply sorting conditions to numerous columns. The data will be read in from the previous transform and sorted according to the options. It will then output the data to the next transform in the data cube. If the data provider of the previous transform supports sorting then an optimized query will be generated and sent to the server. 1
Top/Bottom Set up rules on how data should be sorted and how many records are to be returned (combination of Sort transform and Record Sampling transform without the random generation). 1
Transpose Turn columns into rows and rows into columns. 1
Unpivot Combine multiple columns into a single column (opposite of Pivot). It expands a result into a more normalized form by expanding values from multiple columns in a single record into multiple records with the same values in a single column. 1

 

5. Output transforms

TransformDescriptionInputs
Process Result Represents the final output or result of the data cube (ETL process). This transform doesn't do any data processing but it allows you to configure the measures and hierarchies that will be made available to downstream items such as metric sets. 1

 

6. See also

Dundas Data Visualization, Inc.
400-15 Gervais Drive
Toronto, ON, Canada
M3C 1Y8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours:
Phone: 9am-6pm, ET, Mon-Fri
Email: 7am-6pm, ET, Mon-Fri