Some basic string manipulation in OBIEE.

Manipulating strings using SUBSTR

To extract parts of a string of characters like prefixes or file extensions (as an example) we involve a few of the base string functions available in OBIEE.

So what basic features do we need to know about strings, or what basic functionality do we need to manipulate strings.

SUBSTRING

To extract a substring from a string we need to be able to determine several things about the string.
1. Its overall length
2. If a character or sequence of characters is in the string.
3. the start position of a sequence of one or more characters within the string.

So how long is a string?

We can use the LENGTH function to return the number of characters in the string.

We have the LOCATE function to determine the position of a particular string within the string.

Used together they will allow us to extract the second word from the string 'one two three'.

Let's do a worked example

Start a new Analysis and select any column. We will change the contents as we go. Select Edit formula

and edit the column formula to contain 'one two three' (the single quotes are important as they tell OBIEE this is a string).

Add another column and this time we will edit the formula to show the length of the first column. Select the dropdown on the new column and along the bottom of the Column formula panel you will find a column button with a down chevron, click this and select 'one two three'. Highlight 'one two three' then using the f(...) button at the bottom left we will expand the string functions and select LENGTH.

LENGTH('one two three')

Similarly we will now add columns for finding the first occurence of space ' '.

LOCATE(' ', 'one two three')

and another for the next space

LOCATE(' ', 'one two three', LOCATE(' ', 'one two three')+1)
and one to extract the second word, (remember the word starts one character after the first Locate).

SUBSTRING('one two three' FROM LOCATE(' ', 'one two three')+1 FOR (LOCATE(' ', 'one two three', LOCATE(' ', 'one two three')+1)-LOCATE(' ', 'one two three')))

The SUBSTR has the following syntax SUBSTR(string FROM start FOR number). So we can fill in the blanks

• string is obviously 'one two three'
• start is LOCATE(' ', 'one two three')+1
• and number is the length of the word which we can calculate as (the second occurence of space) minus (the first occurence of space + 1)

and finally a test of the length of the second word, just make sure.

LENGTH(SUBSTRING('one two three' FROM LOCATE(' ', 'one two three')+1 FOR (LOCATE(' ', 'one two three', LOCATE(' ', 'one two three')+1)-LOCATE(' ', 'one two three'))))

What Hour is it?

You can extract time elements from dates in OBIEE at both the repository and answers level.

Just use something along the lines of :

CAST ( EXTRACT( HOUR FROM "CentralCal"."date") AS INTEGER )

OBIEE - Using LocateN

So I guess your pulling your hair out about now, LocateN doesn't seem to work.

Quick tip:

Replace the LocateN with Locate, that's right remove the N (but keep the syntax). The Locate function appears to be overloaded to allow this functionality and the LocateN (which doesn't work) seems to be a leftover hanging around.

Locate(' ','one two three')

finds the first space ' ' in the string 'one two three', and here's the hidden part, starting from position 0 (zero).

LocateN(' ','one two three',5)

finds the first space ' ' in the string 'one two three' from position 5.

OBIEE SampleApp 11.1.1.5 available on OTN

SampleApp V107 includes a multiple enhancements since previous 11.1.1.3 public release. Many examples showcase OBIEE 11.1.1.5 new features (Mobile, New types of data sources), and numerous additional OBIEE examples were added (Geospatial, Datamining, Functional examples and more).

Get it here.

More on Dates

Do you have timestamp values????

And do you want to......, well join them to a date????

Cast(timestamp_value as DATE)

Does the same as TRUNC(timestamp_value) in Oracle. It removes the time section and you are left with just the date part.

Works in Prompts as well as prompted columns (fortunately).

OBIEE 11G and Flex using crossdomain.xml in Weblogic

For Flex viewer to communicate with a remote data source you need to deploy a small XML file in the root directory of the domain containing the data source.

The crossdomain.xml file has the structure


<?xml version="1.0"?>
<!DOCTYPE cross-domain-policy SYSTEM "http://www.adobe.com/xml/dtds/cross-domain-policy.dtd">
<cross-domain-policy>
<site-control policies="all"></site-control>
<allow-access-from domain="*"></allow-access-from>
</cross-domain-policy>



With OBIEE 10G we had a physical directory.

Now with 11G we are presented with weblogic and no root directory, .......bother.

The solution is to deploy an application for the root directory.

All is explained below

in the %BI Home%\Oracle_BI1\bifoundation\jee directory you create a directory called (in this case) apps
in the apps directory you place your crossdomain.xml file and create two further directories.

META-INF and WEB-INF

in the WEB-INF directory you place two files with the contents detailed below

web.xml



<web-app xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" schemalocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
<welcome-file-list>
<welcome-file>crossdomain.xml</welcome-file>
</welcome-file-list>
<mime-mapping>
<extension>xml</extension>
<mime-type>application/xml</mime-type>
</mime-mapping>
</web-app>





weblogic.xml



<?xml version='1.0' encoding='UTF-8'?>
<wls:weblogic-web-app xmlns:wls="http://xmlns.oracle.com/weblogic/weblogic-web-app" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd http://xmlns.oracle.com/weblogic/weblogic-web-app http://xmlns.oracle.com/weblogic/weblogic-web-app/1.1/weblogic-web-app.xsd">
<wls:weblogic-version>10.3.3</wls:weblogic-version>
<wls:context-root>/</wls:context-root>
<wls:container-descriptor>
<wls:default-mime-type>application/xml</wls:default-mime-type>
</wls:container-descriptor>
</wls:weblogic-web-app>

Then you deploy the open directory (apps) as an application on the BI server and accept all defaults.

Char to Date

How to convert a CHAR to a Date datatype.

Assuming that the format of the char date is consistent the easiest is to use the EVALUATE function

EVALUATE('TO_DATE(%1,%2)' AS DATE,,'dd/mm/yyyy')

Here's a good article on more with changing a CHAR using the CAST function in OBIEE.

Happy Hacking