Mixing Context Text Searching and OBIEE

Using EVALUATE and Context Searching

In OBIEE we are limited to using a LIKE operator in filters when we do text searches. There are more options available in the database like CONTEXT searching. This allows us to use a fuzzy matches operator where best matches are scored with the least likely matches receiving a zero score.

What we need is something where when we prompt for one or more keywords, results are returned even if the words are reversed, or misspelled.

This is not a tutorial in the intricacies of Oracle Text searching, which is a speciality in its own right.

As this functionality is not available nativly we need to get creative, and this solution, I'm sure there are more, relies on using the context searching capabilities of the database and the ability to call functions from OBIEE using EVALUATE. (more on EVALUATE here)

Setting up the example:
We will create a table that holds our text

CREATE TABLE text_test
(
   id     NUMBER,
   text   VARCHAR2 (1000)
);


SET DEFINE OFF;
Insert into CRA.TEXT_TEST
   (ID, TEXT)
 Values
   (1, 'cat');
Insert into CRA.TEXT_TEST
   (ID, TEXT)
 Values
   (2, 'dog');
Insert into CRA.TEXT_TEST
   (ID, TEXT)
 Values
   (3, 'kat');
Insert into CRA.TEXT_TEST
   (ID, TEXT)
 Values
   (4, 'dug');
Insert into CRA.TEXT_TEST
   (ID, TEXT)
 Values
   (5, 'dawg');
COMMIT;

Create a context index.

CREATE INDEX mytextindex ON text_test(text) INDEXTYPE IS CTXSYS.CONTEXT;

Create a function that scores our text against a search term.

CREATE OR REPLACE FUNCTION tsearch (id IN NUMBER, wrd IN VARCHAR2)
   RETURN NUMBER
IS
   retval   NUMBER := 0;
   lsql     VARCHAR2 (1000);
BEGIN
   BEGIN
      lsql   := 'select score(1) from text_test t where contains(t.text,''fuzzy(' || wrd || ',,,weight)'',1)>0 and t.id =' || id;
      EXECUTE IMMEDIATE lsql INTO retval;
   EXCEPTION
      WHEN OTHERS
      THEN
         retval   := 0;
   END;
   RETURN retval;
END;

More details on the FUZZY operator here.

Model the table through as a simple single table dimension and fact.

Lastly call this functionality from answers.

Create a new analysis and drag across the TEXT column twice, create a prompt that sets a presentation variable called p_text and edit the formula in the second TEXT column to be

evaluate('tsearch(%1,%2)',"TEXT_TEST"."ID" , '@{p_text}{''cat''}')

saving and running the report will give you something like

entering the search term cat will give you 

Happy Hacking