How to avoid “Distinct” in OBI analysis

As you know, by default, OBI reports doesn’t display duplicated records in the analysis. The reason for such behavior it’s that OBI it’s the analytical tool, not a query tool. The main “Use Case” scenario for OBI, as well as any other Business Intelligence tools, it’s when a business user starts their data analysis from higher levels of data and drill down to the details. If for one fact, several fact values are related to the same dimension values (i.e. several amounts for one client), it will aggregate the values and show only one row.

But from time to time I meet with a customer’s requirement when it needs to display all rows from a table without any aggregation, for instance, a list of transactions.

Let consider an example. We need to provide the ability to view data about Revenue on transaction level without aggregation, and there are three possible solutions.

Solution #1:

The first one solution it’s to remove aggregation from the measure and disable “DISTINCT_SUPPORTED” option for the Database Feature, the instruction bellow:

  1. Duplicate “Revenue” measure with new name “Revenue record level”; 
  2. Select “Properties…” from the context menu and open “Column Source” for the new “Revenue record level” field; 
  3. Remove any aggregation for the field; 
  4. Within the Physical Layer ->Database Features unchecking “DISTINCT_SUPPORTED” feature. 

Solution #2:

The second one solution it’s adding unique record ID into the analysis, for example, “Transaction ID” field (If you want to hide the newly added column in the report, just right click on the new column, select Column Properties–>Column Format–> Hide).

Solution #3:

The third one solution it’s using ROWNUM expression to “suppress” DISTINCT expression, that the solution which I prefer, the instruction bellow:

  1. Duplicate “Revenue” measure with new name “Revenue record level”; 
  2. Select “Properties…” from the context menu and open “Column Source” for the new “Revenue record level” field; 
  3. Select mapping record and click “Edit…”; 
  4. Click “Edit expression …”; 
  5. Add the following expression.
  6. Remove any aggregation for the field 

In the last solution, BI Server will generate the following SQL expression:

 

And you will get the required result …

Kind regards

Aleksey Movchanyuk

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s