EVALUATE in OBIEE with parameter

Last week I had a challange that I solved with Oracle BI EVALUATE function. Because I never used this “advanced” feature before I wrote this quick How-To.

Business example:

Think of a fact table like this one

AMOUNT | CUSTOMER_ID | DATE
200,00 | 1           | 01.09.2013
200,00 | 1           | 01.10.2013
200,00 | 1           | 01.11.2013
117,00 | 2           | 01.09.2013
119,00 | 2           | 01.10.2013

The business question is “how much pays a customer each month in average”. The result should be CUSTOMER_ID = 1 pays 200,00 each month ((200+200+200) / 3). CUSTOMER_ID = 2 pays an average of 118,00 each month ((117+119) / 2).

Of course you can solve this with Oracle BI standard features but my challange was a little bit more complex. But I broke it down to an easier example here!
I wrote an Oracle function that returns the average payment for the customer given as parameter. The function spec is

F_GET_AVG_AMOUNT(P_CUSTOMER_ID NUMBER) RETURN NUMBER;

-- Test
SELECT F_GET_AVG_AMOUNT(1) FROM DUAL; => 200,00
SELECT F_GET_AVG_AMOUNT(2) FROM DUAL; => 118,00

The function body is some SELECT with AVG(),  WHERE clause and GROUP BY and so on. I would not focus on it.

Now I would include this function call in Oracle BI. Therefore I created a new logical column within a logical fact table.

The column is derived from physical mapping with the following formula:

EVALUATE('F_GET_AVG_AMOUNT(%1)' as Double,  "ORCL".""."MY_SCHEMA"."FACT"."CUSTOMER_ID" )

The EVALUATE function passes the call down to the database. So the function call is executed within the database. %1 is a placeholder for a parameter that is added to the string. My parameter is the logical column CUSTOMER_ID. Of course you can add further parameter with %2, %3 and so on if your functions expect these. Add the values to the end of the function call seperated by commas. As Double indicates the returned data type.

The aggregation was set to NONE because the function does it already. Then I dropped this logical column to presentation layer and created a new analyse. The result is shown in the following picture. The column AVERAGE AMOUNT is filled by function call. The analyse is filtered to CUSTOMER_ID=1.

 You will find further information here:

http://docs.oracle.com/cd/E14571_01/bi.1111/e10540/sqlref.htm#BIEMG675
http://know2obiee.blogspot.de/2013/03/evaluate-function-in-obiee-to-embedded.html
http://obieetutorialguide.blogspot.de/2012/03/database-evaluate-function.html
http://gerardnico.com/wiki/dat/obiee/evaluate

 

 

Posted in General, OBIEE | Leave a comment

Article about Oracle Business Intelligence migration

I wrote an nice article about the approach and snares to migrate OBIEE 10g to 11g. It is written in german and was published in DOAG News 03/2013.

Link to my article

Posted in OBIEE | Leave a comment

How to create application roles automatically

Sometimes it is necessary to create application roles in Enterprise Manager via script. This is not very difficult.

First create a file with your application roles you want to create. Use the following syntax:

createAppRole("obi","APPROLE1")
createAppRole("obi","APPROLE2")

Then create a second file which assign groups to your application roles:

grantAppRole("obi","APPROLE1","weblogic.security.principal.WLSGroupImpl","GROUP1")
grantAppRole("obi","APPROLE2","weblogic.security.principal.WLSGroupImpl","GROUP2")

Now log in to Weblogic Scripting Tool (WLST), connect to your local domain and exeute boot files:

/wlst.sh
# connect to your local domain with weblogic administrator
connect() 
# create application roles
execfile(createapproles.py)
# after this finished assign groups
execfile(grantapproles.py)
Posted in OBIEE | Leave a comment

supported upgrade paths for OBIEE to version 11g

When you migrate OBIEE from 10g or 11.1.1.3 or 11.1.1.5 to a newer version it is important to know the supported upgrade paths.

If your objective is version 11.1.1.6.x (for example the latest 11.1.1.6.10) then first you has to go to 11.1.1.6.0. Afterward you can patch the version with opatch to 11.1.1.6.x.  This way is described here by Oracle. Bear in mind that all patches are cumulative so you only has to take the latest one.

If your objective is version 11.1.1.7.x then you can directly go to this version. There is no need to take a stopover. This way is described here by Oracle.

 

Posted in OBIEE | Leave a comment

Log and Configuration Files

At the beginning the directory structure within OBI is obscure. Looking for log information take minutes to find the relevant files. Here is an overview of the most important log and configures files:

FMW_HOME is your BI installation directory for example /usr/local/obiee/mwhome

Path Descirption Details
FMW_HOME/instances/instance1/diagnostics/logs/OracleBIServerComponent/coreapplication_obis1/nqserver.log Logfile for BI Server Link
FMW_HOME/instances/instance1/diagnostics/logs/OracleBIPresentationServicesComponent/coreapplication_obips1/sawlog0.log Logfile for BI Presentation Server Link
FMW_HOME/instances/instance1/diagnostics/logs/OracleBISchedulerComponent/coreapplication_obisch1 Logfile for BI Scheduler Link
FMW_HOME/user_projects/domains/bifoundation_domain/servers/bi_server1/logs/bi_server1.log Logfile for Managed Server (bi_server1)
FMW_HOME/user_projects/domains/bifoundation_domain/servers/AdminServer/logs/AdminServer.log Logfile for Administration Server (Weblogic)
FMW_HOME/user_projects/domains/bifoundation_domain/servers/bi_server1/logs/bipublisher/bipublisher.log Logfile for BI Publisher Link
FMW_HOME/instances/instance1/config/OracleBIServerComponent/coreapplication_obis1/NQSConfig.INI Configuration file for BI Server Link
FMW_HOME/instances/instance1/config/OracleBIPresentationServicesComponent/coreapplication_obips1/instanceconfig.xml Configuration file for BI Presentation Server Link
FMW_HOME/instances/instance1/config/OracleBISchedulerComponent/coreapplication_obisch1/instanceconfig.xml Configuration file for BI Scheduler Link
FMW_HOME/user_projects/domains/bifoundation_domain/config/bipublisher/repository/Admin/Configuration/xmlp-server-config.xml Main configuration file for BI Publisher Link
FMW_HOME/user_projects/domains/bifoundation_domain/config/fmwconfig/biee-domain.xml Central configuration file Link
Posted in OBIEE | Leave a comment

OPMNCTL – No addresses matched the properties for the request

A customer duplicated (cloned) a whole system inclusing operating system, database and BI. That works fine after applying Venkatakrishnan´s article “How to change hostnames“. We have done this multiple times.

But one system does not want to start. OPMN reported the error “No addresses matched the properties for the request”. I was not able to start OPMNCTL with success. After doing some research I found the hidden file /instances/instance1/config/OPMN/opmn/states/.opmndat. This file containts process ids of the opmn subprocesses (BI Server, Presentation Server and so on). These process ids were created on the original system. I renamed this file and startet OPMNCTL again. With success!

Posted in OBIEE | Leave a comment

How to install newest OBI Client Tools 11.1.1.6.7

Today I got a nasty error. I edited a 11g repository in online mode and checked it in. I have done the consistency check also. I know there are many bugs relating this in version 11.1.1.6.0. For example Bug 12426993, 13605692, 13860089, 14038708 and so on. But I am running version 11.1.6.7. and felt save.

After that I got some error messages in nqserver.log that syntax of some logical columns are wrong. These columns are created on the basis of dynamic repository variables. They use VALUEOF(variablename). I checked these columns and found out that the logical expression was removed. The datatype of these columns are set to unknown. After doing some research I got patch 14538078 which is part 1 of 7 of 11.1.1.6.4 Oracle Business Intelligence Installer. The patch note noted

Having installed the Oracle BI 11.1.1.6.4 patches, you must also update the Oracle Business Intelligence Client Tools (Oracle Business Intelligence Administration Tool, Oracle Business Intelligence Job Manager, and Oracle Business Intelligence Catalog Manager) as follows:

Ok, and how I do this?

Reinstall the Oracle Business Intelligence Client Tools by following the instructions in Chapter 5 of the Oracle Fusion Middleware Installation Guide for Oracle Business Intelligence.

After finding this document at oracle.com and reading chapter 5, I got the idea to download client tools installer from BI Homepage. I logged in and selected Download BI Desktop Tools from dropdown list. But the download does not finished. He stopped at 190 megabytes. Next step was searching the biee_client_tools.exe file at filesystem and I found them in mwhome/Oracle_BI1/clients/biserver/biee_client_install.exe. I copied this with WinSCP to my local maschine and installed BI Admin Tool 11.1.1.6.7. Now the consistency check works fine.

Stupid! May I remind you to update your BI Client Tools to the newest version when upgrade to OBI 11.1.1.6.7?

Posted in OBIEE | Leave a comment

Load testing of OBIEE

I looked for a possibility to test an OBIEE system regarding performance. Therefore I want to simulate user behavior and query reports to exeute requests. I found Apache JMeter, an open-source application for load testing. Following I describe my easy setup scenario.

First I downloaded the current Apache JMeter release 2.8 for windows. After extracting I executed <JMETER_DIR>\bin\jmeterw.cmd and got an application window.

Now I created a new Thread Group (testplan > add > Thread (Users) > Thread Group). Within this Thread Group I added a HTTP Request Defaults (Thread Group > add > Config Element) and a Recording Controller (Thread Group > add > Logic Controller). I filled the OBIEE servername and analytics port (7001 or 9704) in HTTP Request Defaults web server area.

Next I added a HTTP Proxy Server to Workbench section (Workbench > add > Non-Test Elements). I attended the Port and configured my favorite web browser to use a proxy connection with “localhost:8080″. Now I started the Proxy by clicking the Start button.

I switched to my web browser which used the started proxy for network connection and played around with OBIEE (login, call dashboard pages, open reports and so on). These actions have been recorded in JMeter.

When I finshed recording I stopped it by clicking the Stop button. As result I got a testplan that can be repeated at any time. Now I wanted to execute this testplan as more then one user. I selected Thread Group and configured “Number of Threads” and “Loop Count”. Number of Threads is the number of simulated users. Loop Count is the number that each user repeats the testplan.

After saving my testplan I executed it by clicking the Play button. Taking a look to Enterprise Manager Performance Monitor I could see some action:

Playing around with number of threads or different testplans and monitoring with different tools (Enterprise Manager, top …) helps me to appraise the system.

A good tutorial can be found here: http://gerardnico.com/wiki/jmeter/recorder
JMeter user manual: http://jmeter.apache.org/usermanual/index.html

Posted in OBIEE | Leave a comment

Don´t forget to drop a process flow before deleting

I wanted to execute a process flow with PL/SQL. Therefore I use the following script:

declare
v_result number;
begin
 v_result :=
         owbsys.wb_rt_script_util.run_task (
            p_location_name   => 'LOCATIONNAME',
            p_task_type       => 'PROCESSFLOW',
            p_task_name       => 'PF_ONE',
            p_custom_params   => '');
      dbms_output.put_line (v_result);
end;

I wondered because I got the error

[Error] Execution (25: 1): ORA-20001: Task not found - 
Please check the Task Type, Name and Location are correct.
ORA-06512: at "OWBSYS.WB_RT_API_EXEC", line 759
ORA-06512: at "OWBSYS.WB_RT_SCRIPT_UTIL", line 910
ORA-06512: at line 4

I tried some research and queried the table OWBSYS.OWB$WB_RT_WAREHOUSE_OBJECTS. I got two entries for my process flow. One entry was an old one that belongs to a deleted process flow package. The other one was the correct one. I forgot to drop the process flow package from DB in control center before removing it in OWB design client. Ohmpf … bad trap! My assumption was, that the correct meaning of the error message is not “could not find the process” but rather “I found 2 process flows and did not know which should I take”.

So I created a new process flow package with the same name as the deleted one and deployed it. The old entries become overwritten in DB. Then I droped it in Control Center and the complete entries were deleted. Now I could executed my processflow with PL/SQL.

Posted in OWB | Leave a comment

Comparison of the 10g and 11g security model

With this post I want to explain the differences between the Oracle Business Intelligence 10g and 11g security model. 11g is based on Fusion Middleware which implements Oracle Platform Security (OPS). Let´s start with 10g. Usually you manage your users and groups within the repository (RPD). In answers (the report frontend) exists catalog groups were users and groups are assigned. The privileges controls who can open or edit a report, create a new dashboard or call the administration interface are assigned to these catalog groups. This is 10g security in simple. Furthermore if you use LDAP and want to take users and groups from there then you take repository initialization blocks. That was a quick look at 10g. Now let´s change to 11g.

In 11g you did not manage your users and groups within the repository anymore. Rather there is an embedded LDAP within the shipped Weblogic Server which holds this account information. If you use LDAP then you connect the Weblogic to it for retrieving the account information. Furthermore there is a new object type called application role. These application roles are a kind of catalog groups. The users and groups are assigned to these application roles and the privileges are given to the same. But application roles are more complex. They can hold users and groups and other application roles. So they can used to build hierarchic privilege schemas. In addition the application roles are available at Fusion Middleware stack and so they can be used in different Oracle applications which use Fusion Middleware (BI Suite, SOA Suite, WebCenter …). The idea is to use a central managed security platform.

And what is done by migration from 10g to 11g with the upgrade assistant? The 10g repository users and groups are created within embedded Weblogic LDAP. Each 10g repository group becomes an application role and the embedded LDAP group is assigned. The result is an one to one relation between group and application role. At this point you can use the new application roles like groups in the old known way. Let´s take a look to the frontend (called analytics in 11g). The best approach is to manage all privileges by application roles. But by migration the 10g catalog groups are transferred to 11g one to one. So catalog groups are available in 11g for backward compatibility. The migrated 11g report privileges are based on catalog groups.

The way straight forward is to change report privileges to application roles and forget catalog groups. But Oracle does not. Now it is possible to mix everything. Report privileges can base on catalog groups and application roles. And catalog groups can contain application roles also.

Overall the security model is not complete different and not massively more complex. I hope I could give an understandable summary. If you have question or notes feel free to add a comment.

Posted in OBIEE | Leave a comment