PL/SQL calls BIEE webservices clean up BI Server cache

The BIEE itself provides several ways to manage the cache, either by setting the cache's expiration time at the physical level or by invoking the Sapurgeallcache () process.

The first two methods are passive management policies (the event table is timed polling, the expiration time is specified as valid duration), is inefficient, and the individual is more active in the cache management policy. This is to proactively clean up the cache once the data has changed.

The best way to do this is to call the API to complete the cache cleanup at the end of the ETL, and to do this you need to invoke the cache management stored procedure provided by BI server, which provides the following cache management process:

Sapurgecachebyquery
Sapurgecachebytable
Sapurgecachebydatabase
Sapurgeallcache

Refer to the Oracle BIEE BI Server ODBC stored procedures Guide for an introduction to the ODBC process

The use of each process can be seen from the name, and now the problem is, how to invoke these procedures in the program?

There are two ways to use JDBC to connect to BI server and then call, and the other is the Web services approach described in this article.

Here are the bi Server Metadata Web services described earlier, for an introduction to BIEE Web Services, see the BIEE 11g WebService Guide

Before using bi Server Metadata Web Services, we need to configure some of the following steps:

Note: This article is based on BIEE 11g 11.1.1.7

1. Configure the data source

In order for Web services to connect to BI server, we need to first configure the data source in the console to log in to the WebLogic console:

Http://xxxxx:port/console Click on the services on the left, then click on Data Sources on the new page and click on the "New" button (if the button is not available, first click on Lock & Edit in the upper left corner and select " Generic Data Source "as shown in:

Open the Create a New JDBC Data source page and fill in the following information separately:

Name:biserver (this name can be customized)

JNDI Name:jdbc/bi/server

Database Type:other then click Next, Database driver also choose for other, continue next; In the next Transaction Options page, select

Supports Global transactions and T one-phase Commit. Then next

Fill in the relevant information on the next Connection Properties page:

Database Name: Fill it out, like ABC

Host NAME:BI server hostname (Here you can fill in the localhost placeholder, we also change it later)

Port:bi Server Port

Database User Name:weblogic

Password:weblogic's password

Confirm Password: Enter the WebLogic password at the second repeat

After filling out next, fill in the following information on the Test Database Connection page

Driver Class Name:oracle.bi.jdbc.AnaJdbcDriver

Url:jdbc:oraclebi://host name:9703/

Properties:user=weblogic

Test Table Name: SQL {call Nqsgetsqlcatalogs ()}

Then click on the Test Configuration button to prompt connection test succeeded. And then continue next.

On the Select Targets page choose which target we need to deploy the data source to, choose Bi_cluster here, because the application of Web Services is deployed on Managerd server.

then click Finish. On the next Data Sources page, click on the Biserver data you just configured, go to the Biserver Data source Settings page, switch to the Connection Pool tab,

Set the statement Cache size to 0, then click the Advanced button below to expand the premium settings, set Connection Creation Retry Frequency to 10, then click Save to save the settings,

Finally, don't forget to click Activate Changes in the upper-left corner to activate your changes.

2. Configure Web Services Security Policy

By default, metadata services does not have a security policy and anyone can call, of course, this does not meet our requirements, the following to add oracle/wss_http_token_service_policy This most basic security policy

For example, explain the configuration process. This is a way to pass the user name and password through HTTP to complete authentication.

Sign in to EM, and then follow the actions shown

Select oracle/wss_http_token_service_policy from available policies and click the Attach button.

After the configuration is complete, we need to test whether the Web Serives is working properly, and here we use SOAPUI, the graphical software, to test the Web services.

3. Testing Web Services with SOAPUI

Download Open source free version soapui opensource (https://www.soapui.org/downloads/soapui.html) from SOAPUI official website and install it, then open the program File-new SOAP Project

Fill in wsdl:http://xxxxxx:port/adminservice/adminservice?wsdl Note: Port is the same as the ports in the Analytics app

Click OK, the following screen appears, followed by the steps shown

After clicking Execute, an unauthenticated prompt appears.

This is because we have a security policy configured for Web services, so we must provide the user name and password as shown in:

Click OK, at the time of execution, success!

Now that our Web services have been configured successfully, the next step is to start writing the client calling code. Clients can use your preferred language for development, such as Java. This example takes PL/SQL as an example mainly because our ETL is mostly written in PL/SQL.
3. Write PL/SQL code call Web Services

PL/SQL calls Web services in a number of ways, such as Utl_dbws and the packages provided in apex, but these methods need to be configured with additional packages, rather than simply sending SOAP messages directly using utl_http and then parsing them with XPath. So this example takes the Utl_http package as an example to explain how to invoke a Web services.

Because this example uses an Oracle 11g R2 database, when you use the Utl_http package to access external network resources, you need to configure the network ACL, or you will not be able to access networking resources.

The specific configuration method, you can see: HTTPS://ORACLE-BASE.COM/ARTICLES/11G/FINE-GRAINED-ACCESS-TO-NETWORK-SERVICES-11GR1

Once you have configured the ACL, you can create the package, giving the code directly below

Baotou:

Note: You need to replace the G_admin_service_url variable value in Baotou according to your actual situation.

Create or Replace package Pf_biee_utl AUTHID current_user  is--Author  : Wind behind  --created:2015-10-30 04:12 :-  -Purpose: For ETL task after cleanup BIEE cache  /***  ***/  g_admin_service_url varchar2 ($): = ' http://xxxxx:8803 /adminservice/adminservice ';  Procedure Set_admin_service_url (P_url varchar2);  Procedure Purge_cache_by_tab (P_db_name varchar2, p_cat_name varchar2 default ', P_schema_name varchar2,p_tab_name VARCHAR2);  Procedure purge_cache_by_db (P_db_name varchar2); end Pf_biee_utl;

Inclusion

Create or Replace package body pf_biee_utl is function send_soap_msg (p_url varchar2,v_soap_msg varchar2) return VARCHAR2 I   S v_resp_content varchar2 (2048);   Req Utl_http.req;   Resp Utl_http.resp; V_value VARCHAR2 (32767); Begin--utl_http.　　Set_wallet (Path = ' file:/home/bitest/wallets/test_wallet ');   --Here you can use wallet to manage usernames and passwords req: = Utl_http.begin_request (P_url, ' POST ', utl_http.http_version_1_1);      Utl_http.set_authentication (req, ' username ', ' passwd '); Utl_http.     Set_authentication_from_wallet (req, ' biee_adm_ws ');   Utl_http.set_header (req, ' content-type ', ' text/xml; Charset=utf-8 ');   Utl_http.set_header (req, ' Content-length ', Length (v_soap_msg));   Utl_http.write_text (req, v_soap_msg);   ---get response resp: = Utl_http.get_response (r = req);     IF (Resp.status_code <> utl_http.http_ok) then Utl_http.end_response (RESP); Raise_application_error ( -20021, ' BIEE Admin Service Connection failed for the following reason: ' | |   Resp.reason_phrase);   End If; BEGIN LOOP UTL_HTTP.REad_line (resp, V_value, TRUE); V_resp_content: = V_resp_content | |     V_value;     END LOOP;   Utl_http.end_response (r = resp); EXCEPTION when Utl_http.     End_of_body then Utl_http.end_response (r = resp);         When OTHERS and then raise;   END; return v_resp_content;  End Procedure Set_admin_service_url (P_url varchar2) as begin if G_admin_service_url is not NULL then G_admin_service_ur    L:=p_url;    else Raise_application_error ( -20020, ' BIEE Admin Service URL cannot be null ');    End If;  End Procedure Purge_cache_by_tab (P_db_name varchar2, p_cat_name varchar2 default ', P_schema_name varchar2,p_tab_name VARCHAR2) As--here is the requested content V_content VARCHAR2 (32767): = ' <soapenv:envelope xmlns:soapenv= ' http://schemas.xmlsoap.org/ soap/envelope/"xmlns:ws=" http://ws.admin.obiee.oracle/"> <soapenv:Header/> <soapenv:Body> <ws: Callprocedurewithresults> <procedurename>sapurgecachebytable (' #dbname # ', ' #cat_name# ', ' #sche_name # ', ' #tab_name # ') </procedureName> </ws:callProcedureWithResults> </soapenv:   Body></soapenv:envelope> ';   V_xmltable XmlType;   V_resp_content VARCHAR2 (2048); V_result varchar2 (100);   BEGIN--utl_http.set_response_error_check (enable = true);   --Utl_http.set_detailed_excp_support (enable = true); --dbms_output.put_line (' STATUS CODE: ' | |   ' Resp.status_code ');   ---substitution variable v_content:=replace (v_content, ' #dbname # ', p_db_name);   V_content:=replace (V_content, ' #cat_name # ', p_cat_name);   V_content:=replace (V_content, ' #sche_name # ', p_schema_name);    V_content:=replace (V_content, ' #tab_name # ', p_tab_name);   Dbms_output.put_line (v_content);   ---Initiate the request and obtain the response result V_resp_content:=send_soap_msg (g_admin_service_url,v_content);   ---analytic result v_xmltable: = XmlType (v_resp_content);   --this resolves the returned results if there are namespace that need to be declared in the extract parameter. V_result:=v_xmltable.extract ('/env:envelope/env:body/ns0:callprocedurewithresultsresponse/return/rows/columns[ 2]/valuE/text () ', ' xmlns:env= ' http://schemas.xmlsoap.org/soap/envelope/' xmlns:ns0= ' http://ws.admin.obiee.oracle/' xmlns   : xsd= "Http://www.w3.org/2001/XMLSchema"). Getclobval (); If V_result <> ' [59118] operation Sapurgecachebytable succeeded! ' then raise_application_error (-20022, ' cache cleanup failed, original Due to the following: ' | |   V_result); End If;  END; Procedure purge_cache_by_db (P_db_name varchar2) as--here is the requested content V_content VARCHAR2 (32767): = ' <soapenv:envelope xmlns: soapenv= "http://schemas.xmlsoap.org/soap/envelope/" xmlns:ws= "http://ws.admin.obiee.oracle/" > <soapenv: header/> <soapenv:Body> <ws:callProcedureWithResults> <procedurename>sapurgecachebydat Abase (' #dbname # ') </procedureName> </ws:callProcedureWithResults> </soapenv:body></soapenv   :envelope> ';   V_xmltable XmlType;   V_resp_content VARCHAR2 (2048); V_result VARCHAR2 (50);   BEGIN--utl_http.set_response_error_check (enable = true); --Utl_htTp.set_detailed_excp_support (enable = true); --dbms_output.put_line (' STATUS CODE: ' | |   ' Resp.status_code ');   ---substitution variable v_content:=replace (v_content, ' #dbname # ', p_db_name);   ---Initiate the request and obtain the response result V_resp_content:=send_soap_msg (g_admin_service_url,v_content);   ---analytic result v_xmltable: = XmlType (v_resp_content);   --this resolves the returned results if there are namespace that need to be declared in the extract parameter. V_result:=v_xmltable.extract ('/env:envelope/env:body/ns0:callprocedurewithresultsresponse/return/rows/columns[ 2]/value/text () ', ' xmlns:env= ' http://schemas.xmlsoap.org/soap/envelope/' xmlns:ns0= ' http://ws.admin.obiee.oracle   /"xmlns:xsd=" Http://www.w3.org/2001/XMLSchema "'). Getclobval (); If V_result <> ' [59118] operation Sapurgecachebydatabase succeeded! ' then raise_application_error (-20022, ' cache cleanup lost For the following reasons: ' | |   V_result); End If; End;end Pf_biee_utl;

Code in Utl_http. Set_wallet can specify wallet to access BIEE user name and password, without having to write the password in the code, refer to the wallet: Oracle databasae Wallet Usage Guide

In addition, the XML-formatted SOAP message returned by Web services needs to be handled using Oracle database's XML processing function, so please let us have your own Baidu for this part of the content.

4. Call method

Begin
Pf.pf_biee_utl.purge_cache_by_tab (' birpod ', ' ', ' APPS ', ' A ');
End

Note: Parameter 1 is the physical layer database name in RPD

Parameter 2 is the physical layer catalog name in RPD, which can be ignored for the Oracle database and is empty.

Parameter 3 is the physical layer scheme name in RPD

Parameter 4 is the physical-layer physical table name in RPD (the alias table only needs to be called on the source table, unless the cache attribute of the alias table overrides the source table)

If the cache cleanup succeeds, no exception is thrown and an exception is thrown if it fails.

So everyone in the process called when the need for exception handling!

5, conclusion the above process is only a basic framework, there are many security-related settings need to consider, such as what to use the BIEE account to invoke this Web services? It is not recommended to use the WebLogic account directly, because this is too risky, you should create a separate account to clean up the cache, this account can not use any access to the BIEE report permissions. In addition, on this basis we can further encapsulate external services, such as the BIE environment is shared by multiple teams, we certainly hope that each team can only clean up their development of the part of the cache, this time need to put the RPD in the meta-data in the database, And in the program logic to determine whether they want to clean up the cache belongs to their development.

PL/SQL calls BIEE webservices clean up BI Server cache