List of transforms


1. Overview

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

In the Data Cube Designer, 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. Select transforms

manual-select Manual Select Enter a TSQL statement to make a selection from a data connector instead of dragging native structures onto the canvas. N/A
mdx-select MDX Select Basic OLAP cube data querying. N/A
sql-select 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 TSQL statement and sent to the data source. N/A
stored-procedure-select 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 Designer canvas. N/A
tabular-select 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

aggregate 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 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 RateOfChange. 1
data-conversion 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 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 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 Lookup Join data from input columns with columns in a lookup table. 2
math Math Perform simple math functions such as Absolute, Ceiling, and Square Root on numeric input columns. 1
string String Manipulate string columns in data tables by applying functions such as To Upper, To Lower, Substring, Trim, Left, and Right. 1
union 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

copy-column 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
fuzzy-grouping 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 Fuzzy Lookup Search for a matching record from a secondary table when no relationship key fields are defined between two tables. 2
null-replacement 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 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 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
r-data-generator R Data Generator Generate data by writing scripts using the R statistical programming language. 0
r-language-analysis R Language Analysis Write scripts using the R programming language to perform statistical and predictive analysis on data. 1+
rank Rank Allow a rank number to be assigned to a new element created by the transform. 1
record-sampling 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 Duplicates Remove duplicated records by grouping all of the selected input columns and copying the results to the output. 1
sort 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 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 Transpose Turn columns into rows and rows into columns. 1
unpivot 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

process-result 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.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

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