OBIEE Sequence Data by Dimensions

1
votes

First question here, so please be gentle. I've been a lurker for a long time and figured now's a great time to get involved.

Using Oracle OBIEE 12c, I'm looking to create a running counter in my result dataset for instances of a group of dimensions, which reset if the group starts again. Like this - in the below example the counter would consider Cust ID and Status:

Cust ID, Order ID, Status, Counter
111, 123456, APPROVED, 1
111, 123457, APPROVED, 2
111, 123458, APPROVED, 3
111, 123459, DECLINED, 1
111, 123460, APPROVED, 1
111, 123461, APPROVED, 2
222, 123462, APPROVED, 1
222, 123463, APPROVED, 2

Any ideas? I've tried a combination of case statements and RSUM(), but I can't quite get to what I'm after and I'm looking for a fresh approach.

Thanks

3
obiee

3 Answers

1
votes

I'm not sure how you want your data ordered, so that will matter. It looks like you are ordering by ORDER ID.

You can use a count with Partitioning. Though this may not work depending on how your data is ordered.

SELECT CUSTID, ORDERID, STATUS, count(*)
over (PARTITION BY CUSTID||STATUS ORDER BY ORDERID ROWS UNBOUNDED PRECEDING) COUNTER
FROM MYTEST;

You can also use LAG to check for row changes, for example:

SELECT CUSTID, ORDERID, STATUS, curr_row, prev_row
  ,CASE WHEN curr_row != prev_row THEN 'Change' ELSE 'Same Group' END as TRACKER
  FROM (
      SELECT CUSTID, ORDERID, STATUS, CUSTID||STATUS AS curr_row
      ,LAG(CUSTID||STATUS) OVER (ORDER BY CUSTID, ORDERID, STATUS) AS prev_row
      FROM MYTEST
      ORDER BY ORDERID
     )
     ;

The above examples are using this set of test data: Test Data

0
votes

You should probably calculate the counter outside of OBIEE as part of the extract/load/transform process. Here is an approximation using the ROW_NUMBER analytical function in Oracle.

with dataset as (
SELECT 111   AS cust_id,
  123456     AS order_id,
  'APPROVED' AS status,
  1          AS expected_counter
FROM dual
UNION ALL
SELECT 111,123457,'APPROVED',2 FROM dual
UNION ALL
SELECT 111,123458,'APPROVED',3 FROM dual
UNION ALL
SELECT 111,123459,'DECLINED',1 FROM dual
UNION ALL
SELECT 111,123460,'APPROVED',1 FROM dual
UNION ALL
SELECT 111,123461,'APPROVED',2 FROM dual
UNION ALL
SELECT 222,1234562,'APPROVED',1 FROM dual
UNION ALL
SELECT 222,1234563,'APPROVED',2 FROM dual 
)
select cust_id,order_id,status,expected_counter,row_number() over (partition by cust_id,status order by order_id asc) as counter from dataset ;

The result is:

cust_id /order_id /status / your_counter / my_counter
111 123456  APPROVED    1   1
111 123457  APPROVED    2   2
111 123458  APPROVED    3   3
111 123460  APPROVED    1   4
111 123461  APPROVED    2   5
111 123459  DECLINED    1   1
222 1234562 APPROVED    1   1
222 1234563 APPROVED    2   2

This example helps you find out that customer 111 has 5 approved orders and 1 that was declined; customer 222 has 2 approved orders. I could not match your exact counter. Hope this helps

0
votes

In OBIEE analysis column formula syntax that'd be sth like

RCOUNT("Orders"."Status" by "Customers"."Cust ID")