String aggregation in OBIEE

In the BI world, we talk about measures and aggregation but we don't talk a lot about string aggregation. String aggregation is about concatenating strings for a certain dimensional value. Let me give an example. Let us say that we have a purchase order (Lets call it PO1) raised for a set of items (Lets say I1,I2 and I3). Lets say that the supplier does not have I1 in stock and wants a larger order quantity for I2 to make the shipping viable and the supplier feels that I3 can be shipped. The store manager might want to see "I1 out of stock, Order quantity of I2 is too less" against PO1. Question is, how do we do it? This blog talks about the process.


The job is done by wm_concat function.

Create a new logical column with a formula similar to the following

CAST ( EVALUATE_AGGR('WM_CONCAT(distinct %1)', "Project ABC"."Purchase Orders"."Problem reason") AS VARCHAR(1000 ))

wm_concat is used just as any other aggregate function such as sum,max and min.

The query generated by OBIEE will have the structure similar to the following

select wm_concat(distinct prob_reason),sum(po_qty),po_number
from po_table
group by po_number;

Please note that the final query generated by OBIEE will depend on how you have configured your rpd but you will have a query similar to the one above at the heart of the final OBIEE query.