BusinessObjects, Oracle & Sql Tips: 2012

Tuesday, November 27, 2012

BusinessObjects-How to find which report is running from database session

Sometimes you see very large SQL expressions which are killing your database systems. With audit reports you can find which reports have run after they finished. But with this little code you can directly find the user, the report and the universe from your database directly on demand.

http://www.dallasmarks.org/blog/2011/10/tips-and-tricks-identifying-business-objects-queries-using-end_sql/

Just put this lines to your universe parameters "END_SQL" line:
/* @Variable('UNVNAME') - @Variable('BOUSER') - @Variable('DOCNAME') */

Thats it. Your query will look like this:

Your dba will appreciate you.

BusinessObjects-How to use Oracle hints in Universe

If you use an Oracle connection in your universe and put the tables from the table browser, you can't use hints with your table. You can use derived tables for use of Oracle hints.

But there is another way put hints to your queries. Just go to your universe. Create an object with your rule. For example:

There is a "1" at the end. Because BO puts a comma sign after each object by generating the sql. Therefore we put a dummy value.

Ok. Lets get a look after we export our universe and use the hint object. (Don't forget to use this object as the FIRST object in Webi)

Friday, June 15, 2012

BusinessObjects-XI 3.1 and Internet Explorer 9

Business Objects XI 3.1 does not support Internet Explorer 9. But there is a very simple workaround.

Go to the folder:
XXXX\Business Objects\Tomcat55\webapps\InfoViewAppActions

Create a page named httperror_400.htm and put the following code in it:

"
<html>

<head>
<meta http-equiv=Content-Type content="text/html; charset=utf-8">
<title>Missing Page</title>
</head>

<script type="text/javascript">
function fixUrl(strToReplace){
urlStr = window.document.location.toString();
return urlStr.replace(strToReplace,"");
}
// Comment out the following line with the double slash (//) to debug
window.location=fixUrl("/InfoViewAppActions");
</script>

<body>
<script type="text/javascript">

urlStr = window.document.location.toString();
urlStrNew = fixUrl("/InfoViewAppActions");
document.writeln("Before:<br/>");
document.writeln(urlStr);
document.writeln("<p>");
document.writeln("After:<br/>");
document.write("<a href='" + urlStrNew + "'>");
document.write(urlStrNew);
document.writeln("</a>");

</script>
</body>
</html>
"

Save and close your document. Now go to the folder:
XXXX\Business Objects\Tomcat55\webapps\InfoViewAppActions\WEB-INF\

Open the web.xml file. Search for where error-code 404 is catched and add the following code:

<error-page>
<error-code>400</error-code>
<location>/httperror_400.htm</location>
</error-page>

more information can be found at:
http://www.forumtopics.com/busobj/viewtopic.php?t=170250&postdays=0&postorder=asc&start=43

Tuesday, May 8, 2012

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:

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.

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.

Sunday, April 29, 2012

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"

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"

Saturday, April 28, 2012

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

BusinessObjects, Oracle & Sql Tips