About Data Enrichment and Transformation in Oracle Analytics
Oracle Analytics makes it easy to enrich and transform your data before you make it available for analysis.
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
Navigate to the transform editor
From the workbooks editor - 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 see a Join Diagram, with a tab for each table. Select a table to open it in the transform editor.
From the datasets editor - If you open a dataset, you see the transform editor. If the dataset contains multiple tables, you see a Join Diagram, with a tab for each table. Select a table to open it in the transform editor.
The transform editor enables you to evaluate data quality, edit your metadata, and cleanse and transform your data.
Description of the illustration transform-editor.png
To transform your data, click Options (the ellipsis top-right of the data column ), and select a transformation option (for example, Bin, Rename or Convert to Text).
Description of the illustration prepare-data-using-column-options.png
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. Click a recommendation in the right-hand Recommendations panel to implement it.
Description of the illustration transform-editor.png
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.
- Custom Knowledge enrichments that your administrator has added to Oracle Analytics.
- 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 Transform Data.
- 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 Add 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 the same dataset. When you open a workbook that shares the dataset, you 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.