About Data Enrichment and Transformation in CIC Analytics
CIC Analytics makes it easy to enrich and transform your data before you make it available for analysis.
Navigate to the transform editor
Workbooks - If you open a workbook, click the Data page, and in the Data Diagram, select the dataset you want to prepare. If the dataset contains multiple tables, you'll see a Join Diagram, with a tab for each table. Select a table to open it in the transform editor.
Datasets - If you open a dataset, you'll see the transform editor. If the dataset contains multiple tables, you'll see a Join Diagram, with a tab for each table. Select a table to open it in the transform editor.
Applying Recommended Transformations
When you create a workbook and add a dataset to it, the data undergoes column level profiling that runs on a representative sample of the data. After profiling the data, you can implement transformation and enrichment recommendations provided for the recognizable columns in the dataset. The following types of recommendations are provided to perform single-click transforms and enrichments on the data:
- Column concatenations, for example, adding a column with the person’s first and last name.
- Date part extractions, for example, separating out the day of week from a date that uses a month, day, year format to make the data more useful in the visualizations.
- Delete columns containing sensitive fields.
- Duration enrichments for date columns, with Extract Age in Years, Months, or Days. For example, you might use the generated numeric column to assign data into bins, such as 0-3 months, 3-6 months, 6+ months, and so on.
- Global positioning system enrichments such as latitude and longitude for cities or zip codes.
- Obfuscation or masking of sensitive fields (full and partial).
- Part extractions, for example, separating out the house number from the street name in an address.
- Semantic extractions, for example, separating out information from a recognized semantic type such as domain from an email address.
Applying Your Own Transformations
In addition to the recommended transformations that you see in the Column Action Panel, you can create your own transformations in different ways:
- Use the Options menu at the top of each column to apply common transformations such as Rename, Uppercase, and Trim. See Transforming Data Using Column Menu Options.
- Click Add Preparation Step in the Data Panel to add a column based on a custom transformation. You can build your column using a wide range of functions, for example, operators, math, aggregates, and conversion. See Adding Columns to a Dataset.
- Use the Element options pane in the bottom left hand corner of the Data Panel to select Treat As - Attribute or Measure, or change the default Aggregation type.
How Dataset Transformations Affect Workbooks and Data Flows
The data transformation and enrichment changes that you apply to a dataset affect all workbooks and data flows that use that particular dataset. When you open a workbook that shares the dataset, you'll see a message indicating that the workbook uses updated data. When you refresh data in a dataset, the preparation script changes are automatically applied to the refreshed data.
Set Up Your Transform Editor
Before you start, it's best to configure your transform editor by displaying the Data Panel, Recommendations Panel, and Quality Tiles. Use these toggle options displayed bottom right: Toggle Data Panel , Toggle Column Action Panel , and Toggle Quality Tiles.
Last Published Friday, December 8, 2023