ORACLE-Transpose Column Value to Row in Oracle

You have a table with various columns. One of your column is a text column where the values are seperated with semicolon (";"). You want to transpose this column values into rows.

Generally we use instr function for this kind of purposes. But there is a another way to do this.

Let's suppose that your select statement is like this:

SELECT 'test;tester;siteconfidence;' TXT FROM DUAL
union all
SELECT 'AA;BB;TT;' TXT FROM DUAL

the output:

and our new statement:

WITH T AS(
SELECT 'test;tester;siteconfidence;' TXT FROM DUAL
union all
SELECT 'AA;BB;TT;' TXT FROM DUAL)
SELECT TXT FROM T
MODEL
RETURN UPDATED ROWS
PARTITION BY(ROWNUM RN)
DIMENSION BY (0 POSITION)
MEASURES (TXT,NVL(LENGTH(REGEXP_REPLACE(TXT,'[^;]+','')),0) NB_MOT)
RULES
(TXT[FOR POSITION FROM 1 TO NB_MOT[0] INCREMENT 1] = REGEXP_SUBSTR(TXT[0],'[^;]+',1,CV(POSITION)))

and the output:
 

Tweet

BusinessObjects-Microsoft Analysis Services and BI4

With BI4 you have to configure your MS Analysis Service server. It is not so easy. Clariba Consulting did it explainvery well. You should check their web sites. Here is the corresponding link:

Configure HTTP Access to Analysis Services on IIS:
https://msdn.microsoft.com/en-us/library/gg492140.aspx

MSAS OLAP Connection setup:
http://wiki.scn.sap.com/wiki/display/BOBJ/Microsoft+SQL+Server+Analysis+Services+(SSAS)+OLAP+Connection

Configuring BI4 Analysis Edition for OLAP for End-to-End SSO to MS SQL Server Analysis Services:
http://service.sap.com/sap/support/notes/1688079

There is only one thing you have to notice. By creating your Site in IIS, Application Pool should be 32 bit.

Tweet

BusinessObjects-Webi Ignores Prompt

Today i faced a new property(!) of BI4. I create a query. Put my dimensions and my prompts. After running the query i saw that my query returns result which schould filter after my prompt.

I checked the SQL script and there it is. The query stays without the prompt. See the picture below.

As you seen on the screenshot, i have two prompts but the script shows me only one of them. After a little search i found that the properties are different of the dimensions.

Aftermath: Webi prompt doesn't support "Hierarchical Display" dimensions.

Tweet

BusinessObjects-CS: COM Provider CLSID not specified or erroneous

If you face with this problem be cool. It is very easy to resolve it.

First you have to do is install SQL Server 2008 to your server where your BusinessObjects is installed. After that go to your BusinessObjects installation folder. Under "SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\oledb" you will see the sqlsrv.sbo file. Open this file and change the two lines as shown in the picture below:

If you use client tools you have to do this at your client too.

If you install SQL Server 2005 drivers you have to write only "SQLNCLI" instead of "SQLNCLI10"

Tweet

BusinessObjects-BI4 Configuration Tips

After installation of your BI4 system one of the important thing you should notice is the "Platform Search Application". This Application is indexing all of your data for searching. Not only your documents, it is also indexing your universe dimensions. By indexing your universes it runs the List Of Value query. Therefore it can be harmful to your database systems.

To configure your Platform Search Application go to the Central Management Console. Under "Application" you can see "Platform Search Application". I suggest to index only Web Intelligence content. I also recommend to set the "Crawling Frequency" to "Scheduled Crawling".

If you change the "Crawling Frequency" don't forget to schedule it under the "Platform Search Scheduling"

Tweet

BusinessObjects-BI4 Main URL List

Query Panel:
http://cmsserver:port/AdminTools

BI Launchpad:
http://cmsserver:port/BOE/BI

CMC (Central Management Console) Panel:
http://cmsserver:port/BOE/CMC

LCM (Life Cycle Management) Panel:
http://cmsserver:port/BOE/LCM

Tweet

BusinessObjects-Main Query Tables for BusinessObjects R2

Query all this tables from the SI_KIND column

CI_INFOOBJECTS
-Webi

CI_APPOBJECTS
-Universe
-Universe Connection

CI_SYSTEMOBJECTS
-User
-UserGroup

Tweet