Using Transforms
This applies to: Managed Dashboards, Managed Reports
A data cube is a multidimensional model of your data that is built of transforms that define your ETL process in each cube.
When you create or edit a data cube, you can add most transforms the toolbar depending on your application settings and current canvas selection. For example, to insert a Join transform, select the connection link between two already connected transforms, then select Join from the toolbar. Alternatively, select an existing transform and drag it to another transform to connect the two, or select a connection between transforms and right-click to add other transforms or joins.
Input Transforms: Use input transforms to bring data into the cube directly, or by reference. See Input Transforms and Output Transforms.
Common Transforms: Use these common transforms, such as Join, Filter, and Aggregate, to bring your data together or change the data, and more. See Common Transforms.
Other Transforms: For more extensive data cleansing, you can bring remove duplicate records, apply sort conditions to your data, or flatten your data, and more. See Other Transforms.
Output Transforms: This brings together the final output or result of your data cube ETL process. Configure the measures and hierarchies that are available to downstream consumers through metric sets and more. See Input Transforms and Output Transforms.
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.
To configure a transform, select the transform, then select Configure from the right-click menu to open a dialog that offers you information about the transform you can reference and in some cases, edit, such as the transform Name, Color, Description, and more.
Note: You need to be a user with a Developer seat to create or edit a data cube.
Input Transforms
Use input transforms to bring data into the cube directly, or by reference.
| Transform | Description | Inputs |
|---|---|---|
| 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 Symphony. |
N/A |
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 Symphony 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 |
Common Transforms
| Transform | Description | Inputs |
|---|---|---|
| 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+ |
Other Transforms
| Transform | Description | Inputs |
|---|---|---|
| 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 |
Output Transforms
Output transforms bring together the final output or result of your data cube ETL process.
| Transform | Description | Inputs |
|---|---|---|
| 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 |
Comments
0 comments
Please sign in to leave a comment.