Oracle Analytics (Cloud and Desktop) 6.1 introduced conditional formatting in the Data Visualisation interface. The ability to conditionally highlight data elements is clearly very useful when used appropriately and allows us to provide ‘RAG’ statuses to draw the consumers’ attention.
To demonstrate some simple formatting, we’ll use a table of monthly ETL runtimes, split by Development and Production.
Let’s load this up as a data set, create a project and a simple line graph visualisation of run times over months for the Development and Production dimensions just to show the data. Hmmmm……we can see that both Development and Production ETL times are trending up over the year, probably as we are loading an increasing number of records.
Oracle Analytics provides a really convenient facility to allow us to duplicate a visualisation and change its type.
It is duplicated as another line graph (obviously), so toggle the duplicate to a Pivot table.
If we tweak the pivot to get the ETL as the break group we have a nice report on the ETL figures. What would be great is if we could have some ‘RAG’ statuses against these values to highlight things to be aware of, such as when the run times exceed a certain value.
Click on the new ‘traffic light’ icon to add conditional formatting – Note this is available at the top of the visualisation and also at the canvas level.
This pops up the template which allows us to define the formatting rules.
Let’s look at the fields
1 – View of the formatting rules currently created and to which visualisations they are applied. We can see those rules active or not for a specific visualisation or choose ‘All Visualisations’.
2 – This is set as ‘Uncategorized’ until we have 1 or more rules, then this shows the rules by measure.
3 – The user defined name of the formatting rule.
4 – The measure to evaluate.
5 – Ability to select a preset e.g. the first one is for just Red and Green, for a quick start.
6 – Create the rule based on specific value ranges or results from calculations.
7 – The Scope determines over which attribute values the rule operates. By default it works on all of them, but we can restrict it to a subset of the data.
So we’ll select the 3 colour RAG option and format based on the values in the Time In Mins measure and just do it for this visualisation. We set the rules so that any ETL time < 100 min is Green, 100 to 119 is Amber and 120+ is Red.
This has the following effect on our pivot table
We can ‘tune’ the colours to be in line with corporate colours or just make them stand out. Let’s tweak the Green to a much more vibrant hue to emphasize it.
This change really highlights the single record which is under 100 mins
Now our Pivot is showing Development and Production run times here, if we just wanted to limit the colour-coding to Production then we can easily do that too by using the Scope facility. Click on the traffic light to go back into the formatting and just select the Production value from the ETL attribute.
Then we are really focused on just the dimension we need. Of course we could have used a filter to exclude all the data we don’t want from the Pivot, but here we have the Development times for context and reference, but the visual cues are only for the Production data.
Talking of visual cues though – say I just wanted to see the dates when the ETL exceeded a certain time, then we can certainly do that too. If we go back into the formatting, then we can see the option to ‘remove’ appears when we hover over the options and we can delete them.
Delete the first two so we just have a single condition that tests for anything over 120m and just against ‘Production’.
The Pivot now just highlights those records which exhibit that characteristic and our attention is drawn to them.
It is now immediately clear that our Production ETL is exceeding the target we have set for it and we need to do something with it.
If we tweak the Pivot table to get a different view, then this makes it easier for us to visually compare the Development and Production times whilst retaining our highlighting for the production records.
Conditional formatting like this is certainly a really useful benefit when wisely used and a great addition to the Data Visualisation tool.