Thursday, July 9, 2015

Automatic schedule of OBIEE Agents after load/ETL process.

If you want to run the agents immediately after a particular task like ETL/Load, you can schedule them through a batch script.

Below is the syntax, you can put this on a notepage and save it with .bat extension.

pushd D:\OBIEE_MW\Oracle_BI1\bifoundation\server\bin

echo pwd| saschinvoke.exe -u weblogic -j 38 -m server_name:9705
echo pwd| saschinvoke.exe -u weblogic -j 39 -m server_name:9705
echo pwd| saschinvoke.exe -u weblogic -j 40 -m server_name:9705
echo pwd| saschinvoke.exe -u weblogic -j 41 -m server_name:9705

D:\OBIEE_MW\Oracle_BI1\bifoundation\server\bin
This is where saschinvoke.exe file resides in OBIEE server.

38,39,40,41 are the job IDs for the Agents we want to run. You can get these IDs from S_NQ_JOB table.

echo pwd
Without this, each command will ask weblogic password before proceeding.

Thanks.




Saving an OBIEE report on a Folder via Agent.

Here I have given steps to place OBIEE 11g report on a shared folder.
First step is to create an Agent.


We can directly go to the Delivery Content tab. Subject is not really necessary as we are not sending it in email. Select the report and format for the report to be placed.

No need to set the recipients or destinations. We need to create an post action in Actions tab. We will come back to that later. Now save this Agent.
We need to create a VB Script file as below and need to call it as a post action in the above agent.
_________________________________________________________________________________

'##=====================================================================
'## Title: Export Report
'## Rev: 1.0
'## Author: Swaminathan Vadivelu
'## Purpose:
'## 1. This script takes a file from OBIEE and saves to the file system
'## 2. Creates a reporting subdirectory if not already present
'##
'## Inputs (specified in Actions tab of OBIEE Delivers Agent):
'## 1. Parameter(0) - This actual file to be exported
'## 2. Parameter(1) - The file name specified within OBIEE
'##
'##=====================================================================

'##Create a variable and assign the base folder path where to store the file:
Dim sPath
sPath = "D:\Report Exports"
'##Remember the above path is either a shared folder or folder on OBIEE server.

Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")

'##check whether directory exists, if not create
Dim objDir
If Not objFSO.FolderExists(sPath) Then
Set objDir = objFSO.CreateFolder(sPath)
End If
Set objDir = Nothing

'##build string to get date in yyyy-mm-dd format
Dim sDate, sDateFull
sDate = Now
sDateFull = DatePart("yyyy", sDate) & "-"
If Len(DatePart("m", sDate))=1 Then sDateFull = sDateFull & "0" End If
sDateFull = sDateFull & DatePart("m", sDate) & "-"
If Len(DatePart("d", sDate))=1 Then sDateFull = sDateFull & "0" End If
sDateFull = sDateFull & DatePart("d", sDate)

'##Create a complete path with file name and add the date on the file name:
Dim sFileName
sFileName = sPath & "\" & Parameter(1) & "-" & sDateFull & ".xls"

'##Place the file on the folder:
Dim objFile
objFSO.CopyFile Parameter(0), sFileName, True
Set objFile = Nothing
Set objFSO = Nothing
_________________________________________________________________________________

Place this file on a path and call it from Agent. Create a Invoke Server script from Actions tab like below.

And set the VB script path and parameters as per the VB script.

Save the agent and run it. It will send the report in excel format to the folder specified in VB script.



If you want to schedule the agent after a ETL process or some other task, you can create a batch script and link it to the ETL process to run after the load. Refer this LINK.

Tuesday, June 23, 2015

OBIEE 11g custom Login Screen Background


Change Login Background OBIEE 11g

Login Background customization:

I have given the below URL of the Oracle logo on the login screen, to figure out the analtics path on which the background .jpg file present.


in the above URL analytics represents path of the folder: 
D:\OBIEE_MW\user_projects\domains\bifoundation_domain\servers\bi_server1\tmp\_WL_user\analytics_11.1.1\7dezjl\war

Replace background_blue_whitegradient_.png with your image. On the following path and recycle presentation services.

D:\OBIEE_MW\user_projects\domains\bifoundation_domain\servers\bi_server1\tmp\_WL_user\analytics_11.1.1\7dezjl\war\res\sk_blafp\login


Most of the times only the above step needed for changing the background. But in my case the file with name background_blue_whitegradient_.png doesn’t work because it had a ‘_’ at the end. So I have renamed the file to background_blue_whitegradient.png and change the login.css file like below.



Before restarting you can run the URL similar to below to make sure you the proper background and recycle presentation services.


Have fun!!!
Thanks

Friday, June 12, 2015

Installing OBIEE 11.1.1.7.0 on Windows 7 64 Bit

Here I laid out screenshots of Step-by-step installation of OBIEE 11.1.1.7.0 on Windows 64 bit machine. The disgrams are self explainable.
Download all the four Disks onto the same folder. Open Disk1 and run Setup.












 On the below step, give the Connection String and Credentials for the Schemas created by RCU. Refer another blog here for how to install RCU.









Click Save on the below window to save all the middleware paths and OBIEE URLs for different  functions on your local machine. And then click finish.




















Once click Finish. Your local machine obiee login page will automatically launch.


Monday, April 13, 2015

GOURL for BI Publisher report - Passing multiple values on to a parameter

Most of you might know already on how to place a BI publisher report on a Dashboard and link a Dashboard prompt to that BI Publisher report.


By creating a Presentation variable on the Dashboard prompts with the same name as the parameter name on BI Publisher, we can link the Dashboard prompts and BI Publisher.

For instance, if you have a parameter created ASOFDATE on your BI Publisher report, you can create a Dashboard prompt with a Presentation variable ASOFDATE and embed the Prompt and BI Publisher report on the same Dashboard page.

To make if more interested my client gave me a requirement to call this Dashboard page from an External application. Here comes the use of GOURL.

Here I called directly the BI Publisher report  through GOURL as the Client anyhow don’t want to see the Prompts and want to pass the values from the application through GOURL.

My GO URL was like this:

https://SERVERNAME/analytics/saw.dll?bipublisherEntry&Action=open&itemType=.xdo&bipPath=%2FReports%2FBIPUBLISHER%2FTest%20BIP%20Report.xdo&bipParams={"_xmode":"1","_xpf":"","_xpt":"0","_xdo":"%2FReports%2FBIPUBLISHER%2FTest%20BIP%20Report.xdo","Date1":"08/26/2014","Date2":"08/26/2014","TestID":["399,456,876"],"_xt":"Test%20Template","_xf":"html","_xautorun":"false"}&NQUser=weblogic&NQPassword=password1234

I have explained the above URL here:

SERVERNAME: name of the OBIEE server or IP address.

Action=Open: Action of this GOURL is just to open the report.

%2FReports%2FBIPUBLISHER%2FTest%20BIP%20Report.xdo: Name of the BIP Report.

xmode":"1": This will hide the parameter labels on top of the BIP report.

Date1":"08/26/2014","Date2":"08/26/2014","TestID":["399"] : Parameters used on the BIP Report

Test%20Template: Template name on the BIP report

Weblogic: User ID passed as part of the GO URL from application, user no need to enter it again.

Password1234: Password given on GOURL, hence user no need to enter it again.


More interesting task would be to pass multiple values on the same parameter. Consider the client want to pass multiple TestIDs from the application via GOURL. The URL below has multiple TestIDs ("399,456,876") , but this one doesn’t work because parameter on BIP side will take this as a single string.

https://SERVERNAME/analytics/saw.dll?bipublisherEntry&Action=open&itemType=.xdo&bipPath=%2FReports%2FBIPUBLISHER%2FTest%20BIP%20Report.xdo&bipParams={"_xmode":"1","_xpf":"","_xpt":"0","_xdo":"%2FReports%2FBIPUBLISHER%2FTest%20BIP%20Report.xdo","Date1":"08/26/2014","Date2":"08/26/2014","TestID":["399,456,876"],"_xt":"Test%20Template","_xf":"html","_xautorun":"false"}&NQUser=weblogic&NQPassword=password1234

One workaround would be pass the entire TestID value as a string with a separate character and use the below on the filter to split the string of numbers into separate numbers.

TESTID in (with str as (select :PoolId as val from dual)

select cast(regexp_substr(val,'[^.]+', 1, level) as int)

from str

connect by cast(regexp_substr(val, '[^.]+', 1, level) as int) is not null)


Here the multiple TestIDs will be passed as ("399.456.876"), dot separated one string and the above function on the filter will convert it into a comma separated list of values (399,456,876). The GOURL is below.

https://SERVERNAME/analytics/saw.dll?bipublisherEntry&Action=open&itemType=.xdo&bipPath=%2FReports%2FBIPUBLISHER%2FTest%20BIP%20Report.xdo&bipParams={"_xmode":"1","_xpf":"","_xpt":"0","_xdo":"%2FReports%2FBIPUBLISHER%2FTest%20BIP%20Report.xdo","Date1":"08/26/2014","Date2":"08/26/2014","TestID":["399.456.876"],"_xt":"Test%20Template","_xf":"html","_xautorun":"false"}&NQUser=weblogic&NQPassword=password1234








Thursday, April 2, 2015

OBIEE 11g Customization - Location of Files to Change


Remove/Edit Copyright Message:

Change Utilmessages.xml on the path:

\Oracle_BI1\bifoundation\web\msgdb\l_en\messages

Change Oracle Logo:

Replace Oracle Logo with your custom logo on these paths

\Oracle_BI1\bifoundation\web\appv2\res\sk_blafp\b_mozilla_4

\Oracle_BI1\bifoundation\web\appv2\res\sk_blafp\login

\user_projects\domains\bifoundation_domain\servers\bi_server1\tmp\_WL_user\analytics_11.1.1\7dezjl\war\res\sk_blafp\b_mozilla_4

\user_projects\domains\bifoundation_domain\servers\bi_server1\tmp\_WL_user\analytics_11.1.1\7dezjl\war\res\sk_blafp\login

Login Messages:

\Oracle_BI1\bifoundation\web\msgdb\l_en\messages\uitlmessage.xml

Product Messages:

\Oracle_BI1\bifoundation\web\msgdb\l_en\messages\productmessages.xml

Oracle BI Catalog:

\Oracle_BI1\bifoundation\web\msgdb\l_en\messages\uicmsgs\saw.catalog.xml

Oracle Biee Home

\Oracle_BI1\bifoundation\web\msgdb\l_en\messages\bieehomemessages.xml

Logout Messages

\Oracle_BI1\bifoundation\web\msgdb\l_en\messages\logonmessages.xml

To include Signin Banner/Notification:

\Oracle_BI1\bifoundation\web\msgdb\pages\common\signin.html

Restart only presentation service to apply these changes.

[nQSError: 59044] Unable to create table TT* error on OBIEE 11g


[nQSError: 59044] Unable to create table TT* error on OBIEE 11g 

Some time when you configure a new connection pool and through that connection poo, if trying to run OBIEE queries, especially when using one report on the filter of the other report  , you may get the error similar to below.

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 59044] Unable to create table TT2OCI7GIAP2N676894873010H3 [nQSError: 17001] Oracle Error code: 1031, message: ORA-01031: insufficient privileges at OCI call OCIStmtExecute: CREATE TABLE TT2OCI7GIAP2N676894873010H3 ( column1 VARCHAR2(80 CHAR) ). [nQSError: 17011] SQL statement execution failed. (HY000)

Depending on the Database, insufficient privilege error will change. In the above error I used Oracle Database.

What is happening over here is, when using one report on the filter of another report, OBIEE creates subqueries and instead of storing the results of the subqueries on BI Server side, it creates TEMP tables on Database. This happens when you set Persist connection pool while creating your connection pool on the physical layer of the RPD. See below.

The User ID on the connection pool used here does not have CREATE TABLE privilege on the Database to create TEMP tables. Hence, you are getting the error.

You can simply clear persist Connection Pool settings on the connection pool and save the RPD and reload metadata, which will solve the issue. Or if you still want to keep Persist Connection pool for improving performance of your Reports, you can give CREATE TABLE privilege to the USER ID given on the connection pool.