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