[OBIEE 10g] Perform Pattern Matching

                                      

 Perform Pattern Matching

In this post I would like to share “how to perform pattern matching” in OBIEE. One of my clients wants to perform an aggregation on a dimension field and the source is fact table.

This dimensional field is a mixture of characters, numbers and special symbols and from this data I need to extract the number and perform the aggregation. My client wants to have a trial in OBIEE.

As I said earlier, the symbol may come at any part of data. Let me list out the constraint I faced in this field.

1.  Extract the number from mixture of characters and special symbols.

2.  I need to consider a single + or – symbol before the number.

3.  Symbol appears in the last or middle of the number should be neglected.

4.  A single >,>=, <, <= symbol before the number should be considered.

5.  Decimal should be considered up to 2 precision.

Due to these constraints, my client accepted minimal performance delay in data retrieval.

At first I don’t used the regular expression and tried using the case function. Since there is a length constrain in BMM logical column I decided to blend the case function and regular expression and used for my requirement.

Consider I need to perform the pattern matching on this column Calc 1 from table F (Fact Table).

Step 1:

Logic to handle the special symbols:

Case when F.CALC1 like ‘%/%’ or F.CALC1 like ‘%:%’ then ’0′

I also used the replace function.

Step 2:

Logic to handle the Characters (both upper and lower case)

When REGEXP_SUBSTR (F.CALC1,’[a-z]‘) = REGEXP_SUBSTR (F.CALC1,’[a-z]‘) or

REGEXP_SUBSTR (F.CALC1,’[A-Z]‘) = REGEXP_SUBSTR (F.CALC1,’[A-Z]‘) then ’0′

Here [a-z] stands for Ranges of characters, such as ‘[a-z]‘, for any ASCII lowercase letter, which is equivalent to the character class: “[[: lower:]]”" while [A-Z] stands for Ranges of characters, such as ‘[A-Z]‘, for any ASCII uppercase letter, which is equivalent to the character class: “[[: upper:]]”"

Step 3:

Logic to extract the number

REGEXP_SUBSTR (F.CALC1,’[0-9-0-9]‘) = REGEXP_SUBSTR (F.CALC1,’[0-9-0-9]‘) then F.CALC1 else ’0′

[0-9-0-9] will match strings consisting entirely of digits

Step 4:

Logic to handle the special symbols

Replace (case query,’>’,”)

Replace (case query,’<’,”)

…

 

Step 5:

Now cast the query using the double.

Note:

Regular expression cannot be used in BMM. In order to use this we need to apply the EVALUATE function.

Syntax for Evaluate Function

EVALUATE (‘DB_Function(%1)’, <Comma separated Expression>)

%1 is a parameter (in this example 1 parameters will be passed to the database function)

Screenshots:

Step 1:

The required column calc 1 is dragged from physical layer to BMM fact table.

 

Now open the properties and click the mapping of the column

Now apply the logic in to the logical column.

Now click the OK button and duplicate the same logical column in the BMM layer and name it as Calc2. Apply summation on the Calc2.

Now save the repository.

Let we check the report

I dragged the two columns Original Calc1 as well as Calc1 Derived Column (newly created)

 

Above figure shows the derived column can able to handle the negative symbols as well as special symbols.

Let we check the rest

It can also able to handle varchar >, <, = operators. I am able to achieve the result.