Grouping data is an important function of data analysis. Attributes – such as “price class” – often facilitate group formation in a BI application. Sometimes, however, a BI user has to compile their data according to their own, new groups for which no attributes are available in the database.
Oracle Business Intelligence Suite (OBI) provides the bin-case function for this purpose. We can use it to create groupings, so-called bins, in just a few clicks without in-depth SQL knowledge.
There are certainly nicer and more sensible ways of creating groups, e.g. creating them as attributes in the database. But the bins are a quick and easy alternative for BI users.
The bin-case function of the OBI is briefly explained here for BI users.
Basis CASE statement
The bin case function represents nothing other than an SQL CASE statement. There is a link at the end of the article for further information on the case statement.
Attributes whose values can lead to self-defined groups or “bins” are selected in the case statement. Each bin represents a WHEN clause in which an attribute value is checked for a predefined condition. E.g. “WHEN price >= 100 …”.
If a value matches a condition, “THEN” is used to assign this line to the defined group.
E.g. “WHEN price >= 100 THEN ‘PREISGRUPPE_1′”.
As in the CASE statement, the sequence must be observed. Each selected line in a BI evaluation is added to exactly one bin. The following applies: if the checked attribute value matches a group, it is assigned. Other possible bins are then no longer taken into account.
Example
In this simple example, vehicles are to be evaluated according to engine capacity ranges. We have created a BI analysis for this purpose.
The analysis shows in detail how many vehicles have a certain cubic capacity value. There are many displacement values close to each other with only one vehicle. To summarize these individual rows, we have to group the cubic capacity. A cubic capacity class attribute or similar is not available in this BI model, i.e. we now create the ranges based on the existing attribute.
In the Criteria section of the BI analysis, we can right-click on the “Edit formula” column. In the “Bins” tab, we can create the groups.
Under “Add bin” we can select the operator and the value to be checked.
We then have to select a name for the new bin.
Once we have created all the bins or groups, we can define the name for “all other values”. “All other values” corresponds to the ELSE branch in the case statement.
Notes on bins
Important: as already mentioned above, the bins are processed in order in the CASE statement. If a data record matches a condition, it is sorted into this group. Even if the data record can still apply to other bins.
The bin groups should therefore be selected selectively by the user so that a value can only be added to one group. Users should also know the approximate value range of the attributes.
The “All other values” option does not need to be created. However, all remaining individual values are then displayed as a separate line.
If we do not create a name for the remaining group, the BI system generates a group without a name.
If the “Treat result as numerical value” checkbox is selected, you can sort numerically according to the group column. We can also perform calculations with the result.
If we select the “Column formula” tab again, we see the newly generated CASE statement.
Result
If we go back to the results view of the analysis, we can see how the vehicles are divided according to the cubic capacity groups we have defined.
As a result, quick and easy groupings with bins are possible. For more complex groupings, however, it is advisable to solve this using upstream attributes in the database or in the Oracle BI repository.
Links
www.oracletutorial.com/oracle-basics/oracle-case/
https://docs.oracle.com/en/cloud/paas/bi-cloud/bilug/conditional-expressions.html
