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.
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 column-action-panel.png
You can enhance or transform a dataset with:
- 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, extract the day of the week from a column that uses month, day, year as its format.
- 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, extract 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 change the column type (set the Treat As option to attribute or measure), or change the default Aggregation type.
Tip: For recommendations on setting column types, on the toolbar click Review auto Treat-as recommendations (
). For example, if the semantic profiler initially identifies a column with numeric IDs such as 1078220 as a measure, you can change the column to an attribute.
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.
Refreshing Profile Results
If the System Knowledge or Custom Knowledge has been updated and you have author privileges on the dataset, you can reprofile and refresh the results for the currently open dataset by clicking Refresh Profile Results on the toolbar (
). Oracle Analytics usually manages this refresh for you, so in most cases you won't need to use this option. Use Refresh Profile Results to reprofile the data if the System Knowledge has been updated, or the Custom Knowledge has been updated and you want to incorporate this in the currently open dataset. When new knowledge is available, the Refresh Profile Results icon displays an alert showing the number of updates available.