Regular Expressions in OBIEE
When reporting against an OLTP system, in many ways, OBIEE works like an ETL tool, transforming the source system data and presenting it as a star schema. After years of writing ETL code, if there's one thing I hate to be without, it's regular expressions. So, when working on a project to map an OLTP source system to a logical model in OBIEE, I came across the following issue, and knew immediately that I would need regular expressions to solve it.
The CONTACT table in the source system had two columns storing the name: FIRST_NAME and LAST_NAME. There were two different processes that wrote entries in the CONTACT table... and one of them was faulty, writing the entire name in the FIRST_NAME column, though it was never corrected. So the following is a decent representation of what the data looked like:
SQL> create table CONTACT (contact_id NUMBER, first_name varchar2(50), last_name varchar2(50));
Table created.
Elapsed: 00:00:00.02
SQL> insert into CONTACT values (1, 'Bryson, Stewart W.', NULL);
1 row created.
Elapsed: 00:00:00.02
SQL> insert into CONTACT values (2, 'Mead, Jon', NULL);
1 row created.
Elapsed: 00:00:00.00
SQL> insert into CONTACT values (3, 'Mark', 'Rittman');
1 row created.
Elapsed: 00:00:00.00
SQL> select * from CONTACT;
CONTACT_ID | FIRST_NAME | LAST_NAME
---------- | -------------------- | --------------------
1 | Bryson, Stewart W. |
2 | Mead, Jon |
3 | Mark | Rittman
3 rows selected.
Elapsed: 00:00:00.00
SQL>
I needed to map the BMM such that I could return FIRST_NAME and LAST_NAME regardless of whether the entire name was concatenated into the FIRST_NAME, or whether it was correctly distributed across both columns. Additionally, the fact that the middle initial needed to be included with FIRST_NAME also proved a little troubling. At the end of the day, this is what I came up with in SQL:
SQL> SELECT CASE 2 WHEN last_name IS null THEN trim(regexp_substr(first_name,'[^,]+$')) 3 ELSE first_name 4 END first_name, 5 CASE 6 WHEN last_name IS null THEN regexp_substr(first_name,'^([^,]+)') 7 ELSE last_name 8 END last_name 9 FROM contact 10 / FIRST_NAME | LAST_NAME -------------------- | -------------------- Stewart W. | Bryson Jon | Mead Mark | Rittman 3 rows selected. Elapsed: 00:00:00.01 SQL>
To explain a bit, I'll start with how I extracted the first name information from the FIRST_NAME column. I needed to start at the comma and then get the entire string until the end of the column. So I used the [^] structure in regular expressions, which basically says, return anything EXCEPT the character between the brackets and after the carrot (^). The plus (+) instructs the RegEx engine to return one or more instances of the previous structure. And at the end, the dollar sign ($) dictates that the entire string must run to the end of the column value. So taken all together, [^,]+$ instructs the RegEx engine to:
"Start at the first character that is not a comma, and return all non-comma characters all the way to the end of the column value."
The only kludge introduced here is that the first non-comma character was actually a space, and to remove it, I simply used a TRIM. If some one has a way to do this without a TRIM, then I'd be glad to hear it.
To extract the last name information from the FIRST_NAME column, I used a similar mechanism, except that, instead of using the dollar sign ($) at the end, I put the carrot (^) at the beginning. It's the same concept: it means that the expression returned has to begin at the start of the column value. So, the ^([^,]+ instructs the RegEx engine to:
"Start at the beginning of the column value, and return the whole string until a comma is encountered."
Easy enough.
Now I want OBIEE to accept this SQL in the BMM. The only issue here is that OBIEE does not support regular expressions in it's SQL language, so I have to use the EVALAUTE command to pass Oracle's regular expression syntax back through to the database. So I'll demonstrate how to do this, but first I'll need to create a fact table to join to the CONTACT table in OBIEE.
SQL> CREATE TABLE activity (contact_id number, activity_date date, num_calls NUMBER);
Table created.
Elapsed: 00:00:00.04
SQL> INSERT INTO activity VALUES (1, SYSDATE-2, 10);
1 row created.
Elapsed: 00:00:00.00
SQL> INSERT INTO activity VALUES (2, SYSDATE-1, 20);
1 row created.
Elapsed: 00:00:00.00
SQL> INSERT INTO activity VALUES (3, sysdate, 30);
1 row created.
Elapsed: 00:00:00.00
SQL>
SQL> SELECT * FROM activity;
CONTACT_ID | ACTIVITY_DATE | NUM_CALLS
---------- | ---------------------- | ----------
1 | 12/16/2009 10:21:34 AM | 10
2 | 12/17/2009 10:21:34 AM | 20
3 | 12/18/2009 10:21:34 AM | 30
3 rows selected.
Elapsed: 00:00:00.01
SQL>
To demonstrate the whole process in OBIEE, I first built the BMM to bring the data in how it is from the database:
To generate the correct data, using the regular expressions developed above, here is how I mapped the First Name attribute:
CASE
WHEN "bidw".""."STEWART"."CONTACT"."LAST_NAME" IS NULL
THEN Trim(BOTH ' ' FROM Evaluate('regexp_substr(%1,''[^,]+$'')', "bidw".""."STEWART"."CONTACT"."FIRST_NAME" ))
ELSE
"bidw".""."STEWART"."CONTACT"."FIRST_NAME"
END
And here is how I mapped the Last Name attribute:
CASE
WHEN "bidw".""."STEWART"."CONTACT"."LAST_NAME" IS NULL
THEN Evaluate('regexp_substr(%1,''^[^,]+'')', "bidw".""."STEWART"."CONTACT"."FIRST_NAME" )
ELSE
"bidw".""."STEWART"."CONTACT"."LAST_NAME"
END
And finally... the results:
