Oracle BI EE 11g – Calling VB Scripts

 

 How to save the reports scheduled via Agents to a local directory using a simple VB Script

 

 

Posted by Satya Ranki Reddy.

 

 

VB Script Method:

 

'#####=========================================================================
'## Title: Export Report
'## Rev: 1.0
'## Author: satya
'## Company: Total Business Intelligence / http://satyaobieesolutions.blogspot.com
'##      
'## Purpose:
'##        1. This script takes a file from OBIEE and saves to the file system
'##        2. Creates a reporting subdirectory if not already present
'##        3. Creates a further subdirectory with name based on current date
'##                   
'## Inputs (specified in Actions tab of OBIEE Delivers Agent):
'##        1. Parameter(0) - This actual file to be exported
'##        2. Parameter(1) - The filename specified within OBIEE
'##        3. Parameter(2) - Report sub directory name specified within OBIEE
'##
'#####=========================================================================

Dim sBasePath
sBasePath = "D:\satya\"

Dim sMasterPath
sMasterPath = sBasePath & "\" & Parameter(2)

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

'check whether master directory exists, if not create
Dim objMasterDir
If Not objFSO.FolderExists(sMasterPath) Then
 Set objMasterDir = objFSO.CreateFolder(sMasterPath)
End If
Set objMasterDir = 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)

Dim sDir
sDir = sMasterPath & "\" & sDateFull

Dim objDir
If Not objFSO.FolderExists(sDir) Then
 Set objDir = objFSO.CreateFolder(sDir)
End If
Set objDir = Nothing

Dim sFileName
sFileName = sDir & "\" & Parameter(1)

Dim objFile
objFSO.CopyFile Parameter(0), sFileName, True

Set objFile = Nothing

Set objFSO = Nothing

 

Please copy the above script in notepad then save  as satyaexport_report.vbs then move to this file as mention below path.

 

C:\OBIEE11G\instances\instance1\bifoundation\OracleBISchedulerComponent\coreapplication_obisch1\scripts\common

 

 

Follow the below steps:

 

Next Create ibot and execute it.

 

 

 

 

 

 



 

Please read my next post for   JS script implementation ..........!!!!!!

 

 

 

Hope this Help's

 

Thanks

Satya Ranki Reddy

Oracle BI Scheduler Custom Java Program Package:

Oracle BI Scheduler Custom Java Program Package

The public interfaces and class for Oracle BI Scheduler Custom Java Program are packaged as com.siebel.analytics.scheduler.javahostrpccalls. There are two interfaces and one class, which are described in following topics:

 SchedulerJavaExtension Interface

Your custom code must implement the following interface:

package com.siebel.analytics.scheduler.javahostrpccalls;
public interface SchedulerJavaExtension {
public void run(SchedulerJobInfo jobInfo) throws SchedulerJobException;
public void cancel();
}

 Example: Creating a Java Program for Agents.

This example creates a Java program that copies the results of an agent to 
another directory. The example creates a Java class that contains filecopy 
logic.


To create a Java program to be used with agents:


Create a Java program using a Java editor.


Create a new Java class called 'sched'.

Paste the following code into the Java editor:

package sched;
    import java.io.*;
    import java.lang.Thread;
 
    import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJavaExtension;
    import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJobException;
    import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJobInfo;
 
    public class sched implements SchedulerJavaExtension{
    public void run(SchedulerJobInfo jobInfo) throws SchedulerJobException
    {
      System.out.println("JobID is:" + jobInfo.jobID());
      System.out.println("Instance ID is:" + jobInfo.instanceID());
      System.out.println("JobInfo to string is:" + jobInfo.toString());
      try
      {
        // File outputFile = new File("D:\\JavaJob.txt");
        File attachFile = jobInfo.getResultSetFile();
        
        InputStream in = new FileInputStream(attachFile.getAbsolutePath());
        OutputStream out = new FileOutputStream(jobInfo.parameter(0));          
        byte[] buf = new byte[1024];
        int len;
        while ((len = in.read(buf)) > 0) 
        {
          out.write(buf, 0, len);
        }
        in.close();
        out.close();        
        
      }
    catch(Exception ex)
    {
      throw new SchedulerJobException(1, 1, ex.getMessage());
    }
    }
    public void cancel()
    {
    }
    }



Add the schedulerrpccalls.jar file from the \MW_HOME\ORACLE_HOME\bifoundation\javahost\lib\scheduler directory 
into your classpath.

Compile the Java Class without errors.

Jar the compiled output to a file. For example, filecopy.jar.

Note the location of the file and ensure that there are no 
errors.





To configure a Java program to be used with 

agents:



Copy the filecopy.jar file that you created in  to the following 
directory:

ORACLE_HOME\bifoundation\javahost\lib

Make the following changes to the JavaHost configuration file, which is 
called config.xml:

<Scheduler>
  <Enabled>True</Enabled>        <DefaultUserJarFilePath>D:\<ORACLE_HOME>\bifoundation\javahost\lib</DefaultUserJarFilePath>
</Scheduler>

If the JavaHost file is not configured correctly, then the agent log file can stop getting written to, although the agent and the Scheduler are still running. In this situation, you stop the Scheduler using the Windows Task Manager.


Restart the JavaHost service.





Adding Java Jobs for Oracle BI Scheduler

Use the following procedure to add a Java job for the Oracle BI 
Scheduler.





Note:The compiled Java class file has to exist on the 
JavaHost computer before you can configure the properties.

To add a Java Job for Oracle BI Scheduler:



Access the Job Manager and from the Jobs menu, select Add 
New Job.

The Add New job window appears.

In the Script Type field, select Java.

Specify the custom properties. For information about setting these values.

Example values and settings for a Java job with the class name "sample.Test", 
file path "Sample", and no additional paths and parameters are included 
below.









Field
Value or Setting



Script Type
Java

Class Name
sample.Test

Class File (Jar File)
Sample



Click OK.

Oracle BI Scheduler Java Extension Example

The following example illustrates how to use the previously described interfaces and class to create a custom Java action. For more information,This example does not contain any long running code, so it is acceptable to do nothing in the cancel method.When the compiled class runs, it collects the ID of the user who ran the agent, the job ID of the agent, the instance ID of the agent, and all possible parameters into an output file.

package sample;
import java.io.*;
import java.lang.Thread;
import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJavaExtension;
import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJobException;
import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJobInfo;
/**
 *
 * @author
 */public class SimpleTest implements SchedulerJavaExtension
{
public void run(SchedulerJobInfo jobInfo) throws SchedulerJobException
{
System.out.println("JobID is:" + jobInfo.jobID());
System.out.println("Instance ID is:" + jobInfo.instanceID());
System.out.println("JobInfo to string is:" + jobInfo.toString());
try
{
File outputFile = new File("D:\\temp\\JavaJob.txt");
FileWriter out = new FileWriter(outputFile);
out.write("User ID:\t\t" + jobInfo.userID() + "\r\n");
out.write("Job ID:\t\t" + jobInfo.jobID() + "\r\n");
out.write("Instance ID:\t\t" + jobInfo.instanceID() + "\r\n");
out.write("Parameter Count:\t\t" + jobInfo.parameterCount() + "\r\n");
for(int i = 0; i < jobInfo.parameterCount(); ++i)
{
out.write("\tParameter ");
out.write(new Integer(i).toString());
out.write(":\t" + jobInfo.parameter(i) + "\r\n");
}
out.close();
}
catch(Exception ex)
{
throw new SchedulerJobException(1, 1, ex.getMessage());
}
}
public void cancel()
{
}
}

Just for information.

Thanks,

Satya Ranki Reddy

Third-Party Triggering of Oracle BI Scheduler Jobs

---

Third-party applications and scripts can launch an Oracle BI Scheduler job from the command line. They can also change the Job Parameters for a single instance. This change simulates third-party-triggered iBots. The interface for the command line is:

saschinvoke.exe -u <Admin Name>/<Admin Password> (-j <job id> |
-i <iBot path>) [-m <machine name>[:<port>]] ([-r <replace parameter filename>] | [-a <append parameter filename>])

The required parameters <Admin Name> and <Admin Password> are the same as those configured for Oracle BI Scheduler where you invoke the job. You can invoke the job either by the job ID or by the iBot path. Optionally, you can specify a machine and port for Oracle BI Scheduler server. If this is omitted, the invoker uses localhost and 9705 respectively.

The invoker also takes an optional job parameter file. Depending on the mode you choose, the parameters configured in Oracle BI Scheduler are changed in one of the following ways by the options in the job parameter file:

• Replace the existing parameters, using the following syntax:
  [-r <replace parameter filename>]
• Append to the existing parameters, using the following syntax:
  [-a <append parameter filename>]
• When using a parameter file, employ the following rules:
  • Use only one parameter per line.
  • Do not ignore white space because it may be custom script-dependent.

Using Replace Mode

In replace mode, the file can specify to leave some parameters as they are in Oracle BI Scheduler. To specify this for a specific line, enter $SCH_DEFAULT$ on the line. This text string acts as a variable and replaces the $SCH_DEFAULT$ text with the text from the original Oracle BI Scheduler parameter.

For example, if the original parameter is hello, the line

$SCH_DEFAULT$ world, $SCH_DEFAULT$ again

is changed to

hello world, hello again

If you use the saschinvoke command, make sure the job parameters are correct. The saschinvoke command does not test the parameters for correctness. A job may not properly execute if invalid parameters are passed to it.

Thanks,

Satya Ranki Reddy

Saschinvoke in obiee 10g/11g on Windows/Linux/Unix.

Saschinvoke is the command line utility in obiee to invoke Jobs in obiee scheduler. Typical use of this 

command involves invoking cache seeding ibots at the end of nightly etl loads.

OBIEE 10g :

Windows location path

OBIEE 10g:     OracleBI_HOME\server\Bin


Unix Location Path:  /u01/OBIEE11g/OracleBI_HOME\server\Bin


This command was invoked by passing the username of administrator and password as parameters.

$saschinvoke -u Administrator/password -j 1

OBIEE 11G:-

Windows Path :

 

ORACLE_HOME\bifoundation\server\bin

 

 

The command  was invoked by passing the username of Weblogic and password as parameters. 

.

$echo password|saschinvoke -u weblogic -j 1

Note: note the use of pipe character |. No space between | and the password.

 

You can implement is your requirement chain ibot also using conditional request. 

The alternative for command line execution , uses the 'saschinvoke' which is Siebel Analytics Scheduler Invoke. :

Usage: SASchInvoke.exe -u <Admin Name>/<Admin Password>
(-j <job id>
-i <iBot path>)

([-m <machine name>[:<port>]]

-p <primaryCCS>[:<port>] -s <secondaryCCS>[:<port>])

([(-r <replace parameter filename>
|-a <append parameter filename>)]

| [-x <re-run instance id>])
[-l

[-c <SSL certificate filename>
-k <SSL certificate private key filename>]

[-w <SSL passphrase>|-q <passphrase_file>|-y]
[-h <SSL cipher list>]

[-v
[-e <SSL verification depth>]

-d <CA certificate directory>
-f <CA certificate file>
[-t <SSL trusted peer CNs>]

]

]  

Thanks,

Satya Ranki Reddy

OBIEE 11G Scheduler Configuration & Mail Configuration on Windows 7/XP/2003 servers.

I have seen several posts in OTN forum for Scheduler configuration for obiee 11g on windows. In 11g petty much easy for scheduler configuration compare to 10g. Please follow the below steps you can achieve easily.



What is the Difference Between 10g & 11g.


OBIEE 10g we can create a saved request / Analysis in OBIEE 11g and we can schedule the reports. But in OBIEE 11g all the  tables related to scheduler services are preconfigured. When we are installing OBIEE 11g , we are running repository creation Utility (RCU). Rcu is creation two schemas called Metadata schema (MDS) and BIPLATFORM. All the scheduler related tables were created in BIPLATFORM schema. So Enterprise Manger (EM) is taking these schemas automatically and running scheduler servers in BI 11g. Connect with BIPLATFORM schema in the data source. There we can view the scheduler related tables NQ_JOB,S_NQ_INSTANCE,S_NQ_JOB_PARAM,S_NQ_ERR_MSG which are created already when we were running RCU. Configure Email settings:

 

Configure Email settings:

1.        Login to Fusion Middleware Control Enterprise manager (http://yourservername:7001:/em) using Admin user credentials
2. Navigate to Mail tab (Business Intelligence > core application > Deployment>Mail 

Here no need modify anything it is default.

Next

Add caption

Note: SMTP server Details reuired.

How to Create GMX account please watch below video.

Please login to below website create user id and password then use GMS smtp server details for delivery for obiee report to mail.

WWW.GMX.com
Note: SMTP server details for GMX ( It is free)

SMTP server name: mail.gmx.com

SMTP Port no:  25

• SMTP Server – SMTP server of your email (e.g. mail.gmx.com)

• Port – Port of the SMTP server (e.g. 25)

• Display name of sender – Any name(e.g.Satya Ranki Reddy)

• Email address of sender – Sender’s email address (e.g. satyarankireddy@gmx.com

• Username – Same as the sender’s email (e.g. satyarankireddy@gmx.com)

• Password – password of your email

• Confirm password – confirm the same password as above

• Number of retries upon failure – any number

• Maximum recipients

• Addressing method To, Blind Copy Recipient (Bcc) – if you want to receive a BCC, select it.
Agent Creation on OBIEE 11g. 

1.

 2.      

 

 

 

Hope this help’s

Good Luck..

Thanks,

Satya Ranki Reddy

How To use INDEX Hints In OBIEE

I’ve found quite a few blogs describing how to add an oracle database hint to a physical table in OBI; but they all stop short of showing how they can be added to an Alias Object.  There is a section at the end of the blog addressing this.
Database Hints allow us to change suboptimal SQL Execution Plans; they simply allow us to provide more information to the optimizer and influence the plan executed.  A database hint will take the form as below.

SELECT
   /*+ index(T222,PK) */
   ‘ROW_WID’ as c1
FROM
   W_CUSTOMER_D T222

OBIEE Query Hints
In OBIEE we can add a database hint to a table object in the physical layer; whenever this table is referenced in a query the hint will be applied.  Importantly, a hint should use the table alias whenever a query specifies an alias for a table; the table itself should not be used.  If you look at the underlying SQL of an OIBEE query, via the Obiee view log, you will notice OBIEE will always use an alias for a table in the generated SQL; an alias taking the form such as T222 above.
The first step to adding our hint to a table is to determine the alias that OBIEE will use in the generated SQL.  Select to Query Repository from the Tools Menu of the Administration Tool.  The Query Repository Tool will open, as in the image below.  Enter the Name of the table in the Name textbox and select Physical Table from the Type menu; click query to run the tool.  Notice the use of the wildcard character, *.

In this example you can see we have a physical table in the database, W_CUSTOMER_D, and that I have created an Alias ojbect based on that table of Dim_W_CUSTOMER_D.  In the ID column the two objects have IDs of 3001:111 and 3001:222 respectively.  If we were using the table in our query, we can deduce we will use an alias of T111.  Actually we used the Alias object and the database alias generated would be T222.  You can see it is the latter segment of the ID that is used.
We can now add the hint; a hint can not be added to an OBIEE Alias object, only to an underlying Table object.  We open the properties for the table object and add the hint to the text box as below.

You can see that the hint is applied to the OBIEE Alias object, T222; but we are creating that definition on the underlying Table object.  If there were no Alias object and the table itself were used in the query then we would need to use the SQL generated alias of T111.
Hinting an Alias Object
But what do we do if we have multiple Alias’ objects sharing the same underlying Table object; this happens all the time. 
We can’t apply the hint to the Alias object itself; instead we apply it to physical joins to and from the Alias object.  The screenshot below shows where we apply the hint for the Alias table above.

You can see that we’ve populated the hint textbox referencing the SQL alias T222.  Whenever this join is used the hint will be added to the query.

Thanks,
Satya Ranki Reddy

Dynamic Variables for Previous Date Period

One of my client was looking at a report which would provide data from first day of last month to current date, so that he can compare sales trend for the last month and plan for the current month.
Best way to quickly work on it is to create variable and use it on dashboard prompt as default to provide first day of last month to current date.
To demonstrate the steps, I have created an excel database to work on the example.
Firstly, create an initialization block and dynamic variables called previous months
Click Manage > Variables to open the Variable Manager.
Click Repository > Initialization Blocks.
Right-click in the white space on the right and select New Initialization Block.

Name the initialization block as Previous_MonthsClick Edit Data Source to open the Repository Variable Init Block Data Source dialog box. Click the Browse button to open the Select Connection Pool dialog box. Select any connection pool associated with database but not excel sheet. (on the above screen shot, emp is excel database. Double-click the HR > Connection Pool object to add it to the Connection Pool field in the Repository In the Default Initialization String field, type the following SQL

SELECT
ADD_MONTHS(TRUNC(SYSDATE,’MM’),-1),
SYSDATE AS CURRENT_DATE
FROM DUAL

Click OK to close the Repository Variable Init Block Data Source dialog box. The connection pool and initialization string are added to the Repository Variable Init Block dialog box
Click Edit Data Target to open the Repository Variable Init Block Variable Target dialog box.

Use the New button to create TWO variables as FIRST_DAY_LAST_MONTH and create another as CURRENT_DATE
Click OK to close the Repository Variable Init Block Variable Target dialog box. The variables appear in the Variable Target field in the Repository Variable Init Block dialog box

Click Test and verify you get the results in the picture.
Click OK to close the Repository Variable Init Block Data Source dialog box.
Click Action > Close to close the Variable Manager
Check in changes.
Check Global Consistency. If the Consistency Check Manager displays any errors, edit the repository to correct the errors before continuing. If there are no error messages, close the Consistency Check Manager.
Save the repository.
Logon to Answers
You can use these variable anywhere in the reports like on the filters as well as on prompts.
Create any report which provides data by date range
Build the following query
1. Pull the date field with other fields
2. Add filters on the date field

Click on the results, it will pull only data within the date range. This date range will dynamically change every month to provide data from first day of previous month to current date.

You use the variables in the Date Prompt also as below
Similarly, you can you these variables on the prompt as well.
Go to prompt and pull the columns including date column

On the default to section, select server variable and type the variables as

Save the report. The report will filter date with the above date range by default.


Thanks,
Satya