09 May 2011 OBI Tables tips & tricks: Hard Coding Zero Values and Combined Request Reports

One of the most frequent requirements from reports in tabular form (Pivot Tables or normal Tables) is to show all possible values of a specific dimension and its related metrics.

But how can you show all values if for some of them there is no data?

Based on a SQL query, it is obvious that an OBI report shows dimension values that have information in fact tables, skipping values without any data.

For example, a Marketing report showing the number of Campaigns launched per Quarter will only display quarters having at least one campaign executed. If a quarter doesn’t have information about the report metric, the quarter will be skipped and will not appear in the end report. In this example (Figure 1), there are no campaigns launched in quarter 2 and 4.

Figure 1 – Normal Report

Although this is standard reporting behaviour and in many circumstances well accepted, as the number of rows retrieved and displayed are minimalised, avoiding the risk of showing sparse tables, sometimes it is required to show all dimension values in a report, even if there is no information in the query fact table (Figure 2).

Figure 2 – Customized Report

There are two possible ways to handle this requirement without having to make physical changes to the source database (see link at the end of this article): in the back-end, by tweaking the SQL query of the report to retrieve all quarters – for example, forcing an outer join or adding a UNION query with hardcoded values – or in the front-end, taking advantage of Answers’ capability to combine different requests in a report.

This blog article describes the latter technique.

To create a customised report and obtain the results shown in Figure 2, we need to add a new report for each dimension value that we wish to show in the report to the existing one.

In our example, 4 reports have to be created – one for each quarter (Figure 3).

Figure 3 – Combined Request in Answers

Figure 4 – Main Report

The trick to show all data is to hardcode a value (e.g. ‘Q1’) in the specific dimension Edit Column Formula box in each additional report.

Other columns present in the Criteria are hardcoded with ‘0’ or the value we want to show in the report, substituting the missing data for those dimensions. In the example, 4 new single reports are created, hardcoded by year, month and ‘0’ value for metrics (Figure 5).

Figure 5 – Individual Reports (hardcoded reports)

When the report is displayed, OBI will merge the requests as if it was a UNION, retrieving all dimension values as per Figure 2.

Easy? Definitely. But it’s not over: we can use the Combined Request functionality to show metrics from different fact tables matched with different dimensions that contain the same values, in the same report.

For example, a customer requires a report with two metrics coming from different fact tables: ‘# of Campaigns’ (coming from Fact 1) and ‘# Responses’ (coming from fact 2) matched with a time dimension, let’s say ‘Quarter’ (Figure 6).

Figure 6 – Logical Schema

When both reports are built independently (Figure 7), there are some quarter values without information.

Figure 7 – Individual Reports

The objective is to merge both of the reports, joining dimension values, and including information from both stars (as Figure 8 ) in the same row.

Figure 8 – Required Report

In order to obtain this report layout, we can apply the functionality described earlier: we will therefore reuse the two reports (one for Campaigns, one for Responses) and add another set of reports to hardcode the necessary values.

For example, 4 additional reports are required to show all quarters in a specific year (Figure 9).

Figure 10 – Structure of the Report in the Answers Criteria tab

NOTE: this functionality is applicable if the report is shown in a Pivot table, and aggregation rules have to be defined.

As specified earlier in the article, there are alternative ways to get the same result, which is to show all possible values for a specific dimension independently if there are facts associated or not.

For example, the ETL process can be forced to retrieve full data results by creating a new physical fact table with a dummy column that contains all data (see Retrieving non-existent data for a global view of reality).

The choice of approach to follow will depend on the reusability of the solution, the effort involved (and resource availability) and design constraints. Choose wisely!