OBIEE - Web Services API (SOAP, WSDL)

Web services are open standard ( XML, SOAP, HTTP etc.) based Web applications that interact with other web applications for the purpose of exchanging data.Web Services can convert your existing applications into Web-applications.

Web services are XML-based information exchange systems that use the Internet for direct application-to-application interaction. These systems can include programs, objects, messages, or documents.

The Web Services Description Language (WSDL, pronounced 'wiz-dəl' or spelled out, 'W-S-D-L') is an XML-based language that provides a model for describing the functions of Web services.

Need to make SOAP calls to OBIEE WSDL endpoint:

Obiee provides several webservices that can be reach with their own Url.

The URL has this form:

§  http://obiee_host:port/analytics/saw.dll?SoapImpl=name_of_service

SOAP

SOAP was originally part of the specification that included the Web Services Description Language (WSDL) and Universal Description, Discovery, and Integration (UDDI). It is used now without WSDL and UDDI. Instead of the discovery process described in the History of the Web Services Specification section below, SOAP messages are hard-coded or genereated without the use of a repository. The interaction is illustrated in the figure below. More on SOAP.

Web Services Description Language

The Web Services Description Language (WSDL) forms the basis for the original Web Services specification. The following figure illustrates the use of WSDL. At the left is a service provider. At the right is a service consumer. The steps involved in providing and consuming a service are:

1.       A service provider describes its service using WSDL. This definition is published to a repository of services. The repository could use Universal Description, Discovery, and Integration (UDDI). Other forms of directories could also be used.

2.       A service consumer issues one or more queries to the repository to locate a service and determine how to communicate with that service. 

3.       Part of the WSDL provided by the service provider is passed to the service consumer. This tells the service consumer what the requests and responses are for the service provider.

4.       The service consumer uses the WSDL to send a request to the service provider.

5.       The service provider provides the expected response to the service consumer.

The Web Service Definition Language (WSDL) is located here:

http://obiee_host:port/analytics/saw.dll/wsdl/v7

Security Service: -            This service helps in identifying the user privileges. One can assign,  

                                          revoke privileges using Security Services.      

Ibot Service: -                   As the name suggests, this is a service for invoking Ibots. 

Web Catalog Service:-       This service is for managing the web catalog.

Replication Service:-          This service is used for replication. Export/Import of catalogs can be

                                           done using this. 

Metadata Service: -           This is for managing the BI Server metadata. 

Report Editing Service: -    This service is used to merge arguments and Oracle BI Web Services

                                            data to create and return the results. 

HTMLView Service:-           This service is used to embed Oracle BI HTML results in third-party        dynamic Web pages, such as Active Server Pages (ASP) or JavaServer Pages (JSP), and portal frameworks. The embed process merges Oracle BI Web Services content with the content of third-party Web pages. 

XMLView Service:                  This is used for retrieving the data from Oracle BI EE Server in the form of XML. 

SAWSession Service:  This is the service that would enable users to login, logout and maintain sessions. Now lets see how we can go about using these services from within Jdeveloper to create a sample custom report. 

USE of SoapUI :-

We can use web services of OBIEE to accomplish any task that we do in OBIEE like folder creation, report creation, setting permissions etc.

I used the web services to generate a session, executeSqlQuery, executeXmlQuery and so on. Below are the steps for the same:

Step1:-

To access and understand the structure of webservices I used a tool called SoapUI. It can be downloaded from the following link: http://www.soapui.org/. Download the tool and install it.

Step 2:-

Open the SoapUI and create a project in it using OBIEE’s webservices URL  

http://URL:port/analytics/saw.dll/wsdl/v7

Click Ok.

Following are the URLs that you would have to use for each service:-

XMLViewService –

http://localhost:9704/analytics/saw.dll?SoapImpl=xmlViewService

SecurityService - http://localhost:9704/analytics/saw.dll?SoapImpl=securityService

WebCatalogService –

http://localhost:9704/analytics/saw.dll?SoapImpl=webCatalogService

SAWSessionService - http://localhost:9704/analytics/saw.dll?SoapImpl=nQSessionService

JobManagementService -

http://localhost:9704/analytics/saw.dll?SoapImpl=jobManagementService

ReplicationService - http://localhost:9704/analytics/saw.dll?SoapImpl=replicationService

ReportEditingService - http://localhost:9704/analytics/saw.dll?SoapImpl=reportService

IBotService - http://localhost:9704/analytics/saw.dll?SoapImpl=ibotService

HTMLViewService - http://localhost:9704/analytics/saw.dll?SoapImpl=htmlViewService

MetadataService - http://localhost:9704/analytics/saw.dll?SoapImpl=metadataService

Generate a session ID :-

    1) In SoapUI, navigate to SAWSessionService>getSessionVariable>Request1 under the newly created     Project.

     2) Right click to open the request editor to alter the xml.

     3) Add the Login Id and password in the xml as shown below.

<?xml version="1.0" encoding="UTF-8"?>

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:v7="urn://oracle.bi.webservices/v7">

   <soapenv:Header/>

   <soapenv:Body>

      <v7:logon>

         <v7:name>test</v7:name>

         <v7:password>*******</v7:password>

      </v7:logon>

   </soapenv:Body>

</soapenv:Envelope>

4) Submit the request after adding the parameters (user, pwd) as shown in above script. This will generate the resulting xml with a session_ID as shown below:

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:sawsoap="urn://oracle.bi.webservices/v7">

   <soap:Body>

      <sawsoap:logonResult>

         <sawsoap:sessionID xsi:type="xsd:string">pfec2ujndlfvchtthr2shpfoisi2kei85tbi2ei</sawsoap:sessionID>

      </sawsoap:logonResult>

   </soap:Body>

</soap:Envelope>

Use of XMLQuery:- (Read a obiee report) create a new folder test  at the following path  /sharad/Users/test

     1) In SoapUI, navigate to XMLViewServices  >executeXMLQuery>Request1

     2) Right click to open the request editor to alter the xml.

     3) Add the reportPath  and sessionID  in the xml as shown below

Use of IbotServices:-  create a new ibot   at the following  any path of OBIEE

    In my case created a IBot TEST12 /users/weblogic/test12

     1) In SoapUI, navigate to IBotServices>executeIBotNow>Request1

     2) Right click to open the request editor to alter the xml.
     3) Add the reportPath and sessionID  in the xml as shown below.

    

Now Check IBot Destination mail.

Actionable Intelligence

Now Click On New->Actionable Intelligence ->Action

How to Count of Individual Characters in a String Lets see..:)

select ind, count(ind) from (select substr(given_string,rownum,1) ind from(select 'TESTMAN' given_string from all_objects) where rownum<=length(given_string))group by ind

OBIEE USERS

OBIEE 11g R1 Architecture

BI Domain:

Overall system is called “Oracle BI Domain” . This comprises of Java components deployed into J2EE containers , non java system components and required configuration files, metadata, repositories, and infrastructure. Oracle Enterprise Manager(OEM) acts as “Fusion Middleware Control” which together with Weblogic Admin Console are the basic backbone,the powerhouse and the Admin Activity controller for the entire domain .

Weblogic Server Domain: The logical domain consists of Admin server and Managed server .It comprises mainly with all the Java modules to trigger the java services .A peer handshaking has been done between WLSD and Oracle BI Instance.

Administration Server:

A JEE container that runs in a dedicated Java virtual machine that contains Java components for administering the system .It typically trigger the start,stop kind of admin activity for his peer Manager server processes.

Managed Server:

A JEE container that runs in a dedicated Java virtual machine that provides the run-time environment for the Java-based services and applications within the system.The services comprises of BI plugin , Security , publisher ,SOA ,BI Office services etc .

Node Manager:

Node Manager provides process management services for the Administration Server and Managed Server processes.Its a separate java utility runs to trigger the auto start , stop , restart activities for distributed Managed server.

Oracle Process Manager and Notification Server(OPMN):

It is monitored , managed and controlled by Fusion Middleware Controller(OFMW). It is also used for distributed process start/stop/restart i.e maintains the Oracle Business Intelligence system component processes.Also used for performance collection using Dynamic Monitoring System (DMS) .

Oracle Weblogic Server (Console):

It is the replacement of Oracle 10g R3 middle tier cluster topology based on Oracle Container for Java (OC4J) . It is a Java EE application server that supports the deployment of Oracle Business Intelligence Java components and primarily host the java component services inside Managed server .

Oracle WebLogic Server Administration Console access has been provided by Fusion Middleware Control.Oracle WebLogic Server Administration Console enables to monitor and manage a WebLogic Server domain. Its capabilities include the following:

• Monitoring health and performance of JEE servers
• Configuring WebLogic domains
• Stopping and starting JEE servers
• Viewing JEE server logs

Fusion Middleware Control:

Fusion Middleware Control is a browser-based tool and the recommended method for monitoring, managing, and configuring Oracle Business Intelligence components.

Fusion Middleware Control is used principally for managing the system components of a BI domain and provides support for the following:

• Starting, stopping, and restarting all system components (BI Server,BI Presentation Server) and Managed Servers
• Configuring preferences and defaults
• Scaling out of system components
• Managing performance and monitoring system metrics(DMS-Dynamic Monitoring System)
• Performing diagnostics and logging (ODL-Oracle Diagnostic Logging)

Fusion Middleware Control also provides access to Oracle WebLogic Server Administration Console, where you monitor and manage Oracle Business Intelligence Java components.

To be very precise OFMW contains to components ..i.e. Java Components managed by Weblogic Server and System components managed by Oracle Process Manager and Notification (OPMN) and this includes below as well including BI components :

• Oracle HTTP Server
• Oracle Web Cache
• Oracle Internet Directory
• Oracle Virtual Directory
• Oracle Forms Services
• Oracle Reports
• Oracle Business Intelligence Discoverer

OFMW Environment: Oracle Fusion Middleware environment contains Oracle WebLogic Server domain (including Administration Server, two Managed Servers) , an Oracle instance and Metadata repository .

Java components: Deployed as one or more Java EE applications:

• Administrative Components — Enterprise Management applications and JMX MBeans for managing all configuration and run-time settings for Oracle Business Intelligence.
• Oracle BI Publisher — This component provides an enterprise reporting solution for authoring, managing, and delivering all types of highly formatted documents to employees, customers, and suppliers.
• Oracle BI Office — This component provides the integration between Oracle Business Intelligence and Microsoft Office products.
• Oracle BI Action Services — This component provides the dedicated Web services that are required by the Action Framework and that enable an administrator to manually configure which Web service directories can be browsed by users when they create actions.
• Oracle Real-Time Decisions (Oracle RTD) — This component provides enterprise analytics software solutions that enable companies to make better decisions in real-time at key, high-value points in operational business processes.
• Oracle BI Security Services — This component provides dedicated Web services that enable the integration of the Oracle BI Server with the Oracle Fusion Middleware security platform i.e JPS (Java Platform Security) , CSF (Credential Store Framework) and users and groups managed by  BI LDAP security.
• Oracle BI SOA Services — This component provides dedicated Web services for objects in the Oracle BI Presentation Catalog, to invoke analyses, agents, and conditions. They make it easy to invoke Oracle Business Intelligence functionality from Business Process Execution Language (BPEL) processes.
• Oracle BI Plugin — A JEE application that routes HTTP and SOAP requests to Oracle BI Presentation Services.

System components: Deployed as non-JEE components, such as processes and services written in C++ and J2SE:

• Oracle BI Server — This component provides the query and data access capabilities at the heart of Oracle Business Intelligence and provides services for accessing and managing the enterprise semantic model (stored in a file with a .RPD extension).
• Oracle BI Presentation Services — This component provides the framework and interface for the presentation of business intelligence data to Web clients. It maintains an Oracle BI Presentation Catalog service on the file system for the customization of this presentation framework.
• Oracle BI Scheduler — This component provides extensible scheduling for analyses to be delivered to users at specified times. (Oracle BI Publisher has its own scheduler)
• Oracle BI JavaHost — This component provides component services that enable Oracle BI Presentation Services to support various components such as Java tasks for Oracle BI Scheduler, Oracle BI Publisher, and graph generation.
• Oracle BI Cluster Controller — This components distributes requests to the BI Server, ensuring requests are evenly load-balanced across all BI Server process instances in the BI domain.

Automating cache purging through windows scheduler &Re-building Cache

windows scheduler.

Call SAPurgeAllCache();

Start command prompt and run following command. Note, there are four parameters in the command. You must replace this with values relavent to your implementation.

-d = ODBC datasource name
-u = Admin user name
-p = Admin Password
-s = sql file

NQCMD -d AnalyticsWeb -u Administrator -p Administrator -s C:\PurgeCache.txt

1. Go to Control Panel> System and Security> Administrative tools> Schedule tasks

2. Click on Schedule tasks. It will open Task Scheduler window.






3. On the right hand side, click on Create task. It will open a new window to create task.

4. Depending on your choice, you can change the trigger setting. Here I have done repeat task every 1 hour.

6. Go to Actions tab. Click on New button to get New Action window. Select the .exe file which you want to schedule. Here we will select nQcmd.exe which is present in c:\oraclebi\server\Bin folder. Add the arguments which is required to open nQcmd.exe and purge the cache. Here the arguments are :

-d AnalyticsWeb -u Administrator -p Administrator -s c:\oraclebi\server\Bin\SQL.txt

-d is for Data Source Name i.e AnalyticsWeb.

-u is user name i.e Administrator

-p is Password i.e Administrator

-s is File path where the purge cache command is writtent.

Create a file SQL.txt and write the purge command:

{call SAPurgeAllCache()};

Place the file in Bin folder

Re-building CacheOnce the cache is purged, you may want to re-build cache for some of the frequently used reports to avoid delay in response to the users. Re-Building cache requires executing the "logical" SQL query against the data source. We can use NQCMD same way we used for purging cache. Instead of purge function, we have to include logical SQL statements in the text file.

Create a text file using notepad with name "BuildCache.txt" and add logical SQL commands to it. You can include more than one command separated by semicolon(;). Save the file to local disk (Let's assume to drive C:\). Below is example of the text file content.

SELECT Product."Department" saw_0, Product."Style" saw_1, Sales."SalesAmt" saw_2 FROM MYCATALOG ORDER BY saw_0, saw_1;

SELECT Product."Department" saw_0, Product."Style" saw_1, Sales."CostAmt" saw_2 FROM MYCATALOG ORDER BY saw_0, saw_1;
Start command prompt and execute the command same way we did for purging the cache.

NQCMD -d AnalyticsWeb -u Administrator -p Administrator -s C:\BuildCache.txt

CALL SASeedQuery('Query').


THANKS...:)

Horizontal Clustering in OBIEE11g

Horizontal Clustering: - If we are going to implement horizontal clustering then we need multiple                                                boxes for multiple instances of BI Server,

Presentation Services etc.

In this type of clustering generally 3 components are clustered

1. BI Server

2. BI Presentation Services

3. Java Host

That is, all the components that can act in active-active configuration can be clustered in Vertical Clustering. Other components like Scheduler

Cluster Controller which work in active-passive fashion are generally not clustered vertically.

We start with logging into the enterprise manager FMW control and navigating to the deployment section. Since we are doing vertical clustering,

Administration and Management:-

• WLS Admin Server used for controlling WLS platform

‣ Startup, shutdown, security, non-OBIEE specific tasks

• Fusion Middleware Control (EM) used for managing OBIEE

• OPMN used for starting, stopping system components

• Node Manager used for

 Starting, stopping, managed servers

Horizontal Scaleout of System Components

• Used for adding additional managed servers (Java components) and system components to an existing cluster

• Used for scalability and failover

• Set up via the Universal Installer > Scale Out BI System option.

Importance of Horizontal Clustering:-

Scalability

Increasing or decreasing the capacity of a system by making effective use of resources is known as scalability. A scalable system can handle increasing numbers of requests without adversely affecting response time and throughput.

High Availability

High availability refers to the ability of users to access a system without loss of service. Deploying a high availability system minimizes the time when the system is down, or unavailable and maximizes the time when it is running, or available. To provide a highly available system, Oracle BI EE requires the following external services:

·         A fault tolerant HTTP load balancer

·         A highly available shared file system

·         A highly available database for Oracle BI Scheduler.

NOTE:-Once the drive is identified (one for RPD and the other for Web Catalog), copy the web catalog to the shared drive. In the deployment section of the Enterprise Manager, enter the shared directory details of both the RPD and the Web Catalog. Upload the repository (RPD) into the shared

Drive using the enterprise manager.

Horizontal Clustering Step 1: Create Shared Areas

• Create folders on network share for RPD, Web Catalog and Global Cache

• Share needs to be accessible to all hosts

• Copy the web catalog across manually, the others will be populated automatically

Access the Fusion Middle ware Control 11g. Select core application under Business Intelligence and select Capacity Management. Go to

Scalability tab and click Lock and Edit Configuration.

Now the Enterprise Manager will allow editing the values.

Horizontal Clustering Step 2: Prepare Installation

• Start the Oracle Universal Installer on the new host, select

  Enterprise Install > Scale Out BI System option

• Enter connection details to the WLS Admin Server

Horizontal Clustering On the new server:-

On the new server copy the setup files for OBIEE and then run the Installer. Select Enterprise Install and then select Scale Out BI System option.

Here fill out the host name where the primary setup is installed and the port number for the web logic is listening.

The installer gives and option to place the Domain Home, Applications Home and Instance Home.

Select the option to auto allocate the ports or to give a preference for the port.

Review the summary and confirm the Installation.

Horizontal Clustering Step 3: Install and Configure

• Allow installation to complete, and then post-install configuration steps

• This should then complete the scale-out of the managed server and java components

  After the confirmation the installer copies the relevant files to the directories.

Now the scale out process is completed successfully.

Horizontal Clustering Step 4: Add New System Components

Add new BI Servers, Presentation Servers and Java Hosts on new host

Summary of the servers:-

Instance 2 is added and you can see it under the fusion middle-ware console.

1.      In the left pane of the Console, expand Environment and select Servers.

2.      Select the name of the Configuration tab where you want to view the Summary of the servers.

Summary of Machines:

This page displays key information about each machine that has been configured in the current WebLogic Server domain.

1.      In the left pane of the Console, expand Environment and select Machines.

.

Monitor Node Manager Status

To monitor Node Manager Status:

1.      In the left pane of the Console, expand Environment and select Machines.

2.      Select the name of the machine where you want to view the Node Manager status.

3.      Select Monitoring > Node Manager Status.

If Node Manager is currently running on the machine, the Node Manager Status displays the following information about the Node Manager process:

o    Status - current status of this Node Manager

o    Version - version string returned from the Node Manager

This page allows you to view current status information for the Node Manager instance configured for this machine.

System Components in Enterprise Manager

At this point, you’ve got an additional managed server, but you’ve not yet brought any additional system components into the cluster. You do this from Enterprise Manager, where you can now add additional BI Servers, Presentation Servers, Java Hosts, Cluster Controllers or Schedulers to the cluster, by selecting them from the Capacity Management > Scalability screen, where you’ll now see a new entry for your managed server.

1.      In the left pane of the EM, expand Business Intelligence and select core application.

2.      Select the name of the Capacity Management tab and click on Scalability tab.

 Where you want to view the System Components

BI Server Repository  

You’ll also need to specify a shared location for your RPD, and for the web catalog. You do this again from Enterprise Manager, this time selecting the Deployment > Repository screen and typing in the details of the shared locations.

1.      In the left pane of the EM, expand Business Intelligence and select core application.

2.      Select the name of the Deployment tab and click on Repository tab.

3.      Check the Share Repository check box, Share the Shared Repository path also Share the catalog path from the Shared catalog folder.

4.      Upload the Repository file give password and apply.

Performance Options (Global Cache)

1.      In the left pane of the EM, expand Business Intelligence and select core application.

2.      Select the name of the Capacity Management tab and click on Performance tab.

3.      Give the Global cache path and Global cache size

Using Fusion Middleware Control to Configure Primary and Secondary Instances

You can use Fusion Middleware Control to configure primary and secondary instances of the Cluster Controller and Oracle BI Scheduler.

1.      In the left pane of the EM, expand Business Intelligence and select core application.

2.      Select the name of the Capacity Management tab and click on Availability tab.

3.      Click Lock and Edit Configuration to allow changes to be made.

4.      In the Primary/Secondary Configuration section, select the host and Oracle instance on which you want to run the primary or secondary Cluster Controller or Oracle BI Scheduler.

5.      Click Apply, then click Activate Changes.

6.      Return to the Business Intelligence Overview page and click Restart.