Building nice waterfall charts with Oracle BI EE

A waterfall chart is a special type of floating-column chart, also known as Flying Bricks Chart or Bridge due to the apparent suspension of the column (brick) in mid-air.
A typical waterfall chart shows how an initial value is increased and decreased by a series of intermediate values, leading to a final value. An invisible column keeps the increases and decreases linked to the heights of the previous columns.

For instance, we’d like to graphically represent the weight of a brand in the global evolution of sales between two FY. I recently was asked by a customer to build the following chart :

One can simply create a waterfall chart in Answers using vertical stacked bars but a fancy looking chart would mean no gap between the bars. As there is no option to act on margin in the bar’s properties, the only way to achieve this is to build your own custom chart type e.g a new chart template has to be created.

The apparent suspension of the brick in mid-air needs a calculated « filler » measure in the query that will be stacked the other measures and invisible (actually white)

This workaround has some limits:
o No way to display values inside the columns (use drill down to detail)
o On mouse roll-over filler columns values will appear
o Use of rectangle style for bars is mandatory otherwise filler columns will not remain invisible.
o Closely linked to the used style : chart template has to be duplicated in every style’s folder
o Closely linked to the language : customization to be replicated for each language

Let us assume that the query is already built (just ask how if needed). Now, just have a look at the basic 2D stacked bar chart below. What we want to do is to visually "glue" the bars.

Let's create a new template :

Step 1

Under [OracleBI_HOME]\web\app\res\s_oracle10\popbin
Copy column.pcxml file to columnforbridge.pcxml
Open the renamed file and adjust the graph properties (add PercentBarWidth attribute)

<Properties BarStyle='Cylinder' PercentBarWidth='100' Effect='2D'/>

Step2

Under [OracleBI_HOME]\web\app\res\s_oracle10\charts
Copy column cxml file paste it and rename it to columnforbridge.cxml

Step3

Copy the two brand new files under
[OracleBI_HOME]\oc4j_bi\j2ee\home\applications\analytics\analytics\res\s_oracle10

Step4

Create a new customMessages folder under [OracleBI_HOME]\web\msgdb\l_en (adjust folder according to your language)
In this new folder, create a new custommessages.xml file with the following content :

Vertical Bars for Waterfall

Note : depending your language adapt the graph label

Step5

Edit [OracleBI_HOME]\web\app\res\s_oracle10\charts\columnforbridge.cxml
then set
displayNameRef="kmsgChartBridgeVerticalBar"
and

Close and save the file.

Step6

Restart Oracle BI Java Host and Oracle BI Presentation Service
Get back to your chart and select your brand new chart type «Vertical bars for Waterfall»

Adjust formatting, colors, labels….et voilà :

Difference between metrics and KPIs

Metrics are the very essence of dashboards. The design of relevant dashboards undoubtedly implies a proper identification and definition of metrics (also called measures) and Key Performance Indicators (KPI). Although there is a difference between a metric and a KPI, I recently noticed that the use of the KPI term has become widespread. So, let’s make clear the difference between the two.

Basically a KPI is a metric, but a metric is not necessarily a KPI.

A metric is a measure of “anything”. It helps to quantitatively measure a situation or a trend according to various analysis axes. It is meant to assess and control events, transactions or processes and to interpret them in the light of different contexts. A metric is a number, an amount, a standard unit of measure.

KPIs are typically tied to an organization’s strategy. They help to evaluate the progress of an organization towards its vision, short-term and long-term goals. A KPI is a key part of a measurable objective, it is meant to be a metric “that matters” and that ideally can be acted on. KPIs will often be defined as ratios or averages. They will ideally be displayed as, or illustrated by, a graphical symbol (sauch as gauge, traffic lights, arrows or threshold) so as to instantly alert users whether they are on target or above or below the target.

Thus, an organization should have many metrics, but just a few KPIs.