Written by 11:50 Business intelligence, Oracle BI

Financial Dashboard With Oracle Visualization Desktop

CodingSight - Financial Dashboard With Oracle Visualization Desktop

Oracle Analytics Cloud (OAC) is a single cloud-based platform for data preparation, in-depth analytics, and comprehensive detailed reporting with customizable visualization in one solution. Oracle Business Intelligence Analytics helps to improve planning and make better-grounded decisions to achieve objectives faster and with less effort. Users can easily load the data from both internal and external sources into the system and report the results.

Financial and HR departments are the most evident beneficiaries of Oracle Analytics. Key moments in employees’ work, turnovers, financial management, forecast vs. actual results, and many related issues are in the focus. The current article deals with building datasets for financial and human resources needs. You will get a step-by-step guide on setting up the Analytics and data visualization for the results.

Data Sources

Here you can find the CSV and XLSX files with sample datasets to use during our exercises. You can download them and then upload them into your Oracle OAC. 

Oracle Financial Analytics Data Model

Our first step is to build the data model.

Log in to Oracle Analytics and click the Create button in the top-right corner. Select Data Set

Oracle Financial Analytics Data Model

In the new Create Data Set popup window, choose Drop data file here or click to browse

In the new Create Data Set popup window, choose Drop data file here or click to browse

Browse to the locally saved dataset. In this example, we are going to work with the Financial dataset.

work with the Financial dataset.

Enter the name of the dataset: Financial Dataset. Then click the Add button.

Enter the name of the dataset: Financial Dataset

Click the Metadata icon to review the dataset metadata.

Click the Metadata icon to review the dataset metadata.

Update the Data Type, Properties, and Aggregation functions accordingly.

Column NameData TypeTreat AsAggregation
YearNumberAttributeNone
MonthDateAttributeNone
QuarterTextAttributeNone
RegionTextAttributeNone
Account GroupTextAttributeNone
AccountTextAttributeNone
Cost CenterTextAttributeNone
CashNumberMeasureSum
RevenueNumberMeasureSum
Operating ExpensesNumberMeasureSum
BudgetNumberMeasureSum
Net IncomeNumberMeasureSum
PayablesNumberMeasureSum
ReceivablesNumberMeasureSum
Previous Year – RevenueNumberMeasureSum
Previous Year – Operating ExpensesNumberMeasureSum
Previous Year – Net IncomeNumberMeasureSum
Previous Year – PayablesNumberMeasureSum
Previous Year – ReceivablesNumberMeasureSum

Click Apply Script. You need to run this after making any changes to the dataset. Otherwise, the changes won’t be saved.

Create a new column named MonthKey. It is extracted from the Month column by using Expression. The column is of the Integer data type and YYYYMM format.

Click Plus to Add Preparation Step.

Click Plus to Add Preparation Step.
  • Enter the name of the new column as MonthKey
  • Build expression: Year(Month)*100 + Month(Month)
  • Click Validate to make sure there is no invalid syntax
  • Click Add Step
  • Click Apply Script
Click Apply Script

Click on the Data icon to view data:

Click on the Data icon to view data:

After uploading a dataset, the AI/ML engine of OAC will run and provide recommendations for data extracting from an existing column, such as date, geography, email, phone, currency, etc. Based on the Financial dataset, OAC will know that the Month column includes Date. So, it is recommended to extract the time data from the Month column.

  • Day Of Week
  • Day Of Month
  • Day Of Year
  • Month Of Year
  • Quarter of Year
  • Quarter

Click on a hamburger icon in the top-left corner (Navigator) > Data

Click on a hamburger icon in the top-left corner (Navigator) > Data

Choose Financial Dataset > Duplicate

Choose Financial Dataset > Duplicate

A new entry is created. However, it does not create a new data source. The new entry in OAC points to the same data source.

Click to open a new dataset:

Click to open a new dataset

Change the name of a new dataset to Financial Dataset-Previous Month. We’ll use it to build some Time analysis, such as Month Over Month.

Time analysis, such as Month Over Month

Click on the MonthKey column > Edit…  Update the expression accordingly:

CASE WHEN MONTH(Month)=12 THEN (YEAR(Month)+1)*100 + 1
ELSE YEAR(Month)*100 + MONTH(Month) +1 END
Click Add Step, then Apply Script.

Click Add Step, then Apply Script.

HR Dataset Preparation

Click Create in the top-right corner > Data Set

HR Dataset Preparation

In the Create Data Set popup window, choose Drop data file here or click to browse:

 Drop data file here or click to browse

Browse to the HR-Employee-Attrition.csv dataset:

Browse to the HR-Employee-Attrition.csv dataset

Rename that dataset to HR Employee Attrition and click Add. Then click on the Metadata icon:

Rename that dataset to HR Employee Attrition and click Add. Then click on the Metadata icon

Rename and update the Data Type, Properties, and Aggregation functions accordingly:

Column NameNew Column NameData TypeTreat AsAggregation
Age NumberAttributeNone
Attrition TextAttributeNone
BusinessTravelBusiness TravelTextAttributeNone
DailyRateDaily RateNumberAttributeNone
DepartmentDepartmentTextAttributeNone
DistanceFromHomeDistance From HomeNumberMeasureSum
EducationEducationNumberAttributeNone
EducationFieldEducation FieldTextAttribute 
EmployeeCountEmployee CountNumberMeasureSum
EmployeeNumberEmployee NumberNumberAttribute 
EnvironmentSatisfactionEnvironment SatisfactionNumberAttribute 
GenderGenderTextAttribute 
HourlyRateHourly RateNumberAttribute 
JobInvolvementJob InvolvementNumberAttribute 
JobLevelJob LevelNumberAttribute 
JobRoleJob RoleTextAttribute 
JobSatisfactionJob SatisfactionNumberAttribute 
MaritalStatusMarital StatusTextAttribute 
MonthlyIncomeMonthly IncomeNumberMeasureSum
MonthlyRateMonthly RateNumberAttribute 
NumCompaniesWorkedNum Companies WorkedNumberAttribute 
Over18Over 18TextAttribute 
OverTimeOver TimeTextAttribute 
PercentSalaryHikePercent Salary HikeNumberAttribute 
PerformanceRatingPerformance RatingNumberAttribute 
RelationshipSatisfactionRelationship SatisfactionNumberAttribute 
StandardHoursStandard HoursNumberAttribute 
StockOptionLevelStock Option LevelNumberAttribute 
TotalWorkingYearsTotal Working YearsNumberMeasureSum
TrainingTimesLastYearTraining Times Last YearNumberMeasureSum
WorkLifeBalanceWork-Life BalanceNumberAttribute 
YearsAtCompanyYears At CompanyNumberAttribute 
YearsInCurrentRoleYears In Current RoleNumberAttribute 
YearsSinceLastPromotionYears Since Last PromotionNumberAttribute 
YearsWithCurrManagerYears With Curr ManagerNumberAttribute 
Rename and update the Data Type, Properties, and Aggregation functions accordingly

Click Apply Script.

Add Preparation Step > Add a new column

  • Upload HR_Turnover_Data-Updated.xlsx for the Turnover analysis
  • Choose the New Joiners sheet
  • Enter the name HR Employee New Joiners, click Add
  • Add Preparation Step > Add new column DateKey by using the following expression:
(YEAR(Date of Join)*100+MONTH(Date of Join))*100+DAYOFMONTH(Date of Join)
  • Again, upload HR_Turnover_Data-Updated.xlsx for the Turnover analysis
  • Choose the Leavers sheet
  • Enter the name HR Employee Leavers, click Add
  • Add Preparation Step > Add new column DateKey by using the below expression:
(YEAR(Date of Leaving)*100+MONTH(Date of Leaving))*100+DAYOFMONTH(Date of Leaving)

Data Modelling: Financial Data Model

In the top-right corner, click Create > Project

In the top-right corner, click Create > Project

Choose the Financial datasets: Financial Dataset and Financial Dataset – Previous Month

Choose the Financial datasets

Enter the project name: Financial Workshop

Click on the Prepare tab

Enter the project name: Financial Workshop
Click on the Prepare tab

Click on the Data Diagram to build the relationship between two datasets. By default, there is no link, so we are creating the link based on MonthKey, Region, Account Group, Account, and Cost Center columns.

  • Click on the dotted line, and the Blend Data popup appears
  • Click Add Another Match to add connection, then OK.
Click Add Another Match to add connection, then OK
  • Click Add Data Set, then choose the Calendar Time dataset. If this dataset is not present, add a new dataset by uploading the Calendar Time.csv file
  • Click on the dotted line between Financial Dataset and Calendar Time, creating the link
Click on the dotted line between Financial Dataset and Calendar Time, creating the link

Click Save.

Data Stories: Financial Workshop

Open the Financial Workshop project and click on the Visualize tab.

Open the Financial Workshop project

Right-click on My Calculations > Add Calculation and provide the following info:
% Operating Expense to Revenue = Operating / Revenue

% Operating Expense to Revenue = Operating / Revenue

You should choose columns from the Financial Dataset.

Add new calculation: % MoM Operating Expense
Expression: (Operating Expense of Month (N) – Operating Expense of Month(N-1)) / Operating Expense of Month(N-1)

choose columns from the Financial Dataset

Add new calculation: % MoM Receivables
Expression: (Receivables of Month (N) – Receivables of Month(N-1)) / Receivables of Month(N-1)

Add new calculation: % MoM Payables
Expression: (Payables of Month (N) – Payables of Month(N-1)) / Payables of Month(N-1)

Add new calculation: % MoM Gross Revenue
Expression: (Revenue of Month (N) – Revenue of Month(N-1)) / Revenue of Month(N-1)

Add new calculation: Accumulated Gross Revenue
Expression: Running SUM of all revenue base on Time

Running SUM of all revenue base on Time

Operating Expense Canvas

Rename Canvas 1 to Operating Expense. Click on the triangle icon of canvas > Canvas Properties

Canvas Properties

Select Freeform in the Layout dropdown list and click OK.

Select Freeform in the Layout dropdown list and click OK

Operating Expense vs Revenue by Quarter

  • Drag and drop Operating Expense, Revenue, and Quarter from the dataset into the Visualization area
  • Choose the chart type: Stacked Area chart
  • Values (Y-Axis) should include Operating Expense and Revenue measures
  • Category (X-Axis) should include Quarter
  • Change the title of the chart to Operating Expense vs Revenue By Quarter
Operating Expense vs Revenue By Quarter

Operating Expense by Cost Center

  • Drag and drop Operating Expense and Cost Center from the dataset into the Visualization area
  • Choose the chart type: Treemap chart
  • Values (box size) – Operating Expense
  • Color – Cost Center
  • Change the title of the chart to Operating Expenses By Cost Center
  • Legend: Bottom
  • Data Labels should display Label and Value
Operating Expense By Cost Center
  • Number Format: Number
  • Abbreviate = On
Operating Expense By Cost Center

Operating Expense by Cost Center vs Quarter

  • Drag and drop Operating Expense, Cost Center Quarter from the dataset into the Visualization area
  • Choose the chart type: Horizontal 100% Stacked Bar chart
  • Values (Y-Axis) – Operating Expense
  • Category (X-Axis) – Quarter
  • Color – Cost Center
  • Change the title of the chart to Operating Expenses By Cost Center vs Quarter
  • Legend: Top
Operating Expense By Cost Center vs Quarter

Operating Expense Change by Month

  • Drag and drop Operating Expense and Month from dataset into the Visualization area
  • Choose the chart type: Waterfall chart
  • Values (Y-Axis) – Operating Expense
  • Category (X-Axis) – Quarter
  • Color – Cost Center
  • Change title of the chart to Operating Expenses By Cost Center vs Quarter
  • Legend: Top

Conclusion

This way, the article demonstrated how to configure the Oracle Data Visualization Desktop to build the datasets for financial and HR needs. Also, we touched on the visualization basics to obtain the oracle business intelligence analytics results in a visual form. The further dashboard tutorials will cover other specific tasks we can perform with the Oracle Sales Cloud Analytics.

(Visited 53 times, 1 visits today)

Subscribe to our digest
to get SQL Server industry insides!

Close