More than 4000 Character in OBIEE

Recently I have come across throught the business requirement where the backe end databae SQL server is having some dimensional columns in a table which is having around 15000 character. As per the business need I need to show the same value in BI Dashboard.

But as we know OBIEE repository is having 3 types of Character types and max size of the datatype is 4000

1. VARCHAR
2. BINARY VARCHAR
3. LONGVARCHAR

If values exceed more than 4000 character in a columns It  does not show the value in the OBIEE answers/Dashboards but the interesting fact is OBIEE VARCHAR column support to the max size of 8000

So here is the workaround:

Step1:
Right click on the particular column in the physical layer of the repository and select 'Properties' and set LONGVARCHAR as data type and set '32768' as size

Step2:
Use the substring function the split the value in the column. So here my columns is having around 15000 character so I am splitting first 8000 character into one column and another 7000 character to another column.

Here keep two things in Minds.

First we need to cast the column to change LONGVARCHAR to VARCHAR and then use SUBSTRING to split the values

For e.g:

Original Column:
BAW."Dim - Product"."Sales Comment"
Sales Comment1 : Which will be holding the value of first 8000 character 

CAST ( SUBSTRING(BAW."Dim - Product"."Sales Comment" FROM 0 FOR 8000) AS VARCHAR ( 8000 ))

Sales Comment2: which will be holding the value of another 8000 character
 CAST ( SUBSTRING(BAW."Dim - Product"."Sales Comment" FROM 8000 FOR 16000) AS VARCHAR ( 8000 )) 

Step3: 
Drag the newly created columns into the corresponding presentation tables in presentation layer.

Step4: 
Reload the metadata repositoy in OBIEE answers side and check with the newly imported columns in results.

Note:
If you need both values in a single column result, try to use the narrative view methods.

Cool... isn't it???!!!

Cheers!
Bose