Blog

Creating an All Other Values Row in Oracle BI

In a recent training session for Oracle 10g, requests were used to present information for the “Top N” most expensive projects. During the session a delegate asked whether it was possible to present a row in the table that shows the value of all the other projects. The change would result in information being presented as shown in the table below, with the row highlighted in yellow being a summary of all the other records not in the Top N:

Fiscal Year	Project Number	Project Name	Cost
2007	ATZ Services	ATZ Services	£55,018
2007	PC201	Percent Complete – Operations	£26,359
2007	FP201	Fixed Price – Operations	£19,541
2007	CS419	Redwood City 419 Construction	£18,000
2007	Bldg Renovation	Bldg Renovation	£13,314
2007	AD201	Administrative	£11,340
2007	100123	New Store 100123	£8,153
2007	B200	Building 200 Construction	£4,858
2007	Corp Srvcs	Corporate Services	£4,793
2007	SS2349	Redwood City Selection	£1,200
2007	All Other Projects	All Other Projects	######
Total Cost			162,574

Two methods to achieve the result will be described in this article, these methods can also be used with Oracle BI 11g. The first method is slightly easier to implement as all the work is done in a single request. However this method is not ideal for a large volume of rows that is to be ranked.

Single Request Method

The example for this method builds a request based on the Vision Sample data with the subject area called Project – Cost. The concepts can be applied to any subject area.

The request will present the top 7 most expensive projects then a row that presents the total cost of all other projects. The first step is to build a request that presents all the projects in descending order of Cost.

In this example the request will present information grouped by Project Number and Project Name this is often referred to as the row header. This is important information to note, later on columns will be added to simulate the row header.

Then for good measure add some filters to the request. In the example here filters are used to provide a sweet spot from the sample data Oracle provide.

Columns are added to the request to use as the row header on the view objects. In this example two columns are used as row headers so two placeholders are added from the subject area. It doesn’t really matter which columns are added as the details will be edited.

Edit the first of the new column’s formula(fx) and provide a meaningful name. In this example the data is presented by Project Number. The new column has been updated with the Table Heading of Custom Columns and the Column Heading of Project Number. The formula has been changed to the expression:

case when rank("Fact - Project Cost".Cost) <= @{PvMostExp}{7} then Project."Project Number" else 'Other Projects' end

In this example there are two columns in the row heading. The second column’s formula (fx) has been updated to have the Table Heading of Custom Columns and the Column Heading of Project Name. The formula has been changed to the expression:

case when rank("Fact - Project Cost".Cost) <= @{PvMostExp}{7} then Project."Project Name" else 'Other Projects' end

The expression is important to achieve the end result. The expression is based on a CASE statement. The first WHEN statement contained in the CASE statement looks at the result of the function RANK(Cost). The result of the RANK function is compared to the presentation variable PvMostExp. If the result of the RANK function is less than or equal to the value contained in the presentation variable then either the Project Name or the Project Number is returned. If the result of the RANK function is greater than the value in the presentation variable then the value Other Projects will be returned as the value.

The two new columns result in the following selection of columns in the Criteria page.

Lets display the results in a table view. It can be seen from the screenshot that the first seven projects are presented with the Project Number and Project Name in the last two columns. Then the remaining rows are all presented with the value Other Projects.

The table view is excellent for viewing how the data is being received but not the best method of presention. The ideal view to use to present the data would be either a Pivot Table View or a Chart View.

The Chart View should be configured to present the Cost by the column Project Name found under the heading Custom Columns. The screenshot included presents the configuration options used with the example.

Using this technique the last bar in the chart presents the Other Projects.

To present the information in a tabular format a Pivot Table would be the perfect container. Simply exclude the original columns for Project Number and Project Name. Then use the Custom Columns versions as the definition for the rows as shown in the image below.

The Pivot Table will group the information as shown in the image included. This is much more presentable than the table view.

Combined Request Method

An alternative method for presenting the information is a little more complicated. This should perform better when used against a larger volume of data.

In this example the same data will be presented with a different technique. This technique uses a request that will be referenced in a filter to determine what Projects are in the top seven. This technique has the same filter criteria referenced in many places. It is therefore recommended to create a saved filter to capture the logic.

In this example the saved filter is defined as shown in the image below and saved with the name Year and Operating Unit Filter:

The reference request is created using the following column selection:

The reference request has a selection of filters applied. The saved filter which was created earlier and a filter to limit the rows to the top 7 most expensive projects.

The request is saved with a name indicating it’s functional existence, the name used in the example is Control List. When the results are viewed a simple table of the most expensive projects is displayed.

The default view objects are adequate for the purpose of the Control List request. This request will never be viewed. It will be used as the criteria for a filter on the main request.

The next step is to create a request that is used to present the information to the user. In the column selection add in the following columns:

Then add an extra column to the end of the row and edit the column formula (fx). Rename the Table and Column Heading to Order. Then update the formula to RANK the Cost.

As the column name implies this will be used for ordering the data when we finish building the request.

Next add in the filters for the request. This filter is the same as the one used on the Control List.

Now click the Combine with Similar Request button. This allows the combination of two separate requests. The information from the different requests is appended together into a single table.

When the button is clicked an interface as shown in the following image appears. This provides a template of what columns need to be added in the new request. Oracle BI needs the requests that will be appended to follow the exact same structure.

In the template add the exact same columns so you end up with the selection shown in the image.

The Project Number column should be edited to have the properties shown in the image. The column has been provided with Custom Headings of Project and Project Number. The column formula has been updated to show the value ‘Other Projects’.

The same set of changes are made to the Project Name column. This time the custom heading is Project and Project Name. The column formula is provided with the same value of ‘Other Projects’.

The final change to the column selection is to the Order column. This column is provided with the custom headings of Order and Order. Then the formula is changed to the value 99999.

This part of the request requires a filter to be created. This filter uses the saved filter created earlier. The filter has an additional set of criteria based on the column Project Number. In the Create/Edit Filter window for the Project Number column select the Advanced button. Then select the menu item “Filter based on results of another request”.

Then provide the details shown in the image below. The Relationship should be set to “is not equal to any”. The Saved Request should be pointed to the Control List created earlier. The Use Values in Column property should be set to Project Number.

The actions produce a filter selection as shown below.

When the results are viewed the results will likely appear to be out of the required order. This is why the Order column was created.

Return to the Criteria page and then use the Order column to sort by ascending value. To interact with the column properties that are used ensure that the Result Columns group is selected. To keep the results presentable it’s recommended that the Order column is set to hidden.

This technique results in the ability to present the information in a standard table view. There is no need to use a pivot table to group the result set as the work as has been completed on the underlying data source.

Conclusion

Either technique can be used to achieve similar results. It’s important in all development activity to consider the implications of the design of a solution and the associated implications.

The Single Request Method is the simpler approach to implement and the statement generated to the underlying data source will be a simple select statement. However the approach will result in the BI server performing additional work to aggregate the information for the creation of the pivot table. This could potentially be a huge overhead in both BI server processing and network traffic if the underlying result set that is retrieved from the data source contains many rows of information.

The Combined Request Method is more difficult to setup. This technique will result in more processing being performed on the data source. This is usually the preferred outcome as the data sources will often perform the processing more efficiently than the BI server. The statements that are sent to the data source will be more complex then the first approach and could result in some overhead in completing the request. The great thing with this technique is the data sent to the BI server requires only a small amount of processing to be displayed in a report or on a dashboard.

It’s recommended that all techniques are tested for performance as many factors will impact the approach to use. Some factors to consider are:

• Data Volumes
• BI Server Resources
• Data Source Resources
• Network Capacity
• Scalability

8 May, 2012 by James Walsh

Business Intelligence