How to Transform and Enrich Data in Oracle Analytics Cloud? – Part 1

November 27, 2020 Digital Transformation

Overview of Oracle Analytics Cloud


Oracle Analytics Cloud (OAC) is an oracle product which deals with Data. We can play around with data with the help of features provided by OAC.

It has four different pillars, or we can say OAC is nothing, but you can say it is all about connect, prepare, explore and share the Data.

Overview of Oracle Analytics Cloud

OAC empowers business analysts and consumers with AI-powered, self-service analytics capabilities for data preparation, visualization, enterprise reporting and augmented analysis.

OAC provides the us the most comprehensive cloud analytics in a single platform, including everything from self-service visualization and powerful inline data preparation to enterprise reporting, advanced analytics, and self-learning analytics.

In OAC, below mentioned options are available for/ to perform on Data:

1. Data Visualization
2. Data Flow
3. Advanced Analytics & ML
4. Data Replication
5. Data Modeling
6. Mobile Apps

In this section, we will be looking into some of the points on how to Transform and Enrich Data in Oracle Analytics Cloud.

We can have any type of data that may or may not be understandable/presentable. We can follow some of the steps and prepare our data to be reparative called Data Visualization. For example, we want to hide some of information, or we want amount in format and specific currency, We want to know profit and loss calculated on amount column, we can have all this here.

Features of Oracle Analytics Cloud :


Features of Oracle Analytics Cloud


Transform and Enrich Data in Oracle Analytics Cloud


Data set is combination of facts and information, before representing/visualizing the Data set we need to prepare and clean it so that one can understand easily, and access enough based on privacy. Preparing and cleansing your data is an important step before visualizing a data set. For example, the set might have sensitive data such as customers’ social security numbers that you don’t want to expose. You can hide or transform all characters of the social security number column, remove columns from a data set, or extract portions of a data column to create a new column that contains the extracted data.

We have recommendations and available data preparation options in Oracle Analytics to improve data quality.

We have used spreadsheet as the data source. We have added spreadsheet files that have the XLSX extension and that are no larger than 100 MB. We do have option to create data set from comma-separated value (CSV) and text (TXT) files.

Step1: Create a Data Set as given below:

Oracle Cloud Analytics step 1

Click on Data set and you will get options as mentioned in screenshots, select “Drop Data file or Click to Browse” and drop or upload any data set (here we have uploaded Customer and sales data).

Save it and It looks like below:


Create a Data Set -Oracle analytics cloud


Changes done by us will be recorded in a Preparation Script on the Prepare page. A blue dot Blue dot icon indicates that the script hasn’t been applied to the data source. The changes are implemented when you select Apply Script. The changes are complete when the Apply Script progress indicator disappears.

Step2: Create a Project and Data Source:


In the right side of the page you will be able to see recommendations for the data, by column, in the data set. If recommendations are relevant to your data, then you can select if not then we have to do changes on data accordingly.

1. Sign into Oracle Analytics.
2. On click Create you will get option to create project beside Data set. Click on that and create project.
3. In Add Data Set, you will have option to create data set or you can choose from existed create one as below:


Create a Project and Data Source


4. Select Data set one by one if you want multiple then click on Add to Project and save it by giving it a relevant name.
5. In Prepare tab, you can perform multiple things on data.


Step3: Cleanse Data Using Groups


Here, you create a group to apply consistency to gender data in the data set. In the gender column, the genders are represented by four values: male, M, female, and F. Your changes cleanse the data to use Male and Female as the standard for genders.

1. In Results, select the title column, click Options, and then select Group.

Cleanse Data Using Groups


2. In Group, enter Gender in the Name field.
3. Enter Female in the field that contains Group 1. In the list of values for the Gender column, click the rows with female and f, and then click Add Group.
4. Enter Male in the field that contains Group 2. In the list of values for the Gender column, click Add all, and then click Add Step.
5. Don’t forget to Apply Script after doing every change otherwise it will not reflect.


Step4: Conceal Sensitive Customer Data


To comply with security policies for sensitive data, we can obfuscate all or a portion of the data in a column. If some users need to see the sensitive data, you can create a duplicate data set containing the sensitive data.

1. In Results, select the ccnumber column.
2. In the Recommendations list, click Obfuscate First 12 Digits of ccnumber.
3. In Results, select the ccn column. In the Recommendations list, click Obfuscate First 5 digits of ccn.


Conceal Sensitive Customer Data


Step5: Data Options


We have Options for data column based on if it is Attribute or measure as given below:
ccnumber: Measureccard:Attribute

Data Options

Step6: Apply Script: Apply the Preparation Script


Preparation script contains the pending changes that are applied when the script is run.


1. Click Apply Script.
2. You will be able to see blue dot next to Results, then clicking Apply Script progress indicator disappears and the script completes the transformations.
Oracle Analytics automatically saves the changes in the data set.
3. ClickSave to update/refresh the data set.
4. In left side menu you will be able to see data set, click and search for data set created by you and see the changes.



How to Transform and Enrich Data in Oracle Analytics Cloud? – Part 2