IT-eye

In most SOA architectures where BPEL is involved as the service orcherstrator to model business process, you have to have access to business data stored in a back-end database. Most often you will do this by using webservices that will give your bpel process access to the data. On the otherhand the SOA architecture will also need some basic java applications for data administration. These java applications will also need to have access to the same data. There are many ways to implement both the webservices (e.g. pl/sql) and the model-layer for your java applications. (You also can reuse the webservices for your model-layer, but this will raise a performance penalty due to a lot of XML overhead). I think the best way to do this is to have one implementation of the data-access functionality and make the functionality available both as webservice and as a normal DAO. EJB3 is perfectly suitable for this issue. EJB3 offers you the ability to implement functionality once and expose it in different ways:

1. as a remote bean
2. as a local bean
3. as a webservice

In an earlier blog-posting, I already explained in detail how to implement and EJB3 webservice using the JAX-WS 2.0 annotations and the Oracle EJB3 implementation. Below, I show a basic example of how you can expose easily an EJB3 session bean both as a local bean and as a webservice:

Bean implementation

package nl.iteye.ejb;

import javax.ejb.Stateless;

@Stateless(name="SessionWSEJB")
public class SessionWSEJBBean implements SessionWSEJB, SessionWSEJBWebService {
    public SessionWSEJBBean() {
    }

    /**
     * Local bean method
     * @return hello world greeting
     */
    public String helloWorld(){
        return "Hello, non-WS world!";
    }

    /**
     * WebMethod
     * @return hello world greeting
     */
    public String helloWsWorld(){
        return "Hello, WS world!";
    }

}

Webservice interface

package nl.iteye.ejb;

import java.rmi.Remote;

import javax.jws.WebMethod;
import javax.jws.WebService;

import oracle.webservices.annotations.Deployment;

@WebService(name="SessionWS",
            targetNamespace="http://ejb.oracle.com/targetNamespace",
            serviceName="EjbSessionService")
@Deployment(contextPath="ejb-ws",
            uriPath="sessionWS",
            portName="sessionService")
public interface SessionWSEJBWebService extends Remote {

    @WebMethod(operationName="helloWSWorld")
    public String helloWsWorld();
}

Local interface

package nl.iteye.ejb;

import javax.ejb.Local;

@Local
public interface SessionWSEJB {

    public String helloWorld();

}

I can now use the bean both in my java-front-end application and in a bpel proces with no duplicate implementation code. Another advantage is that I don’t have to change the webservice interface when I need new functionality in my Java front-end application.

I’m convinced that EJB3 is a suitable technology to implement both your model and your webservices in an efficient way.

Encouraged by the SOA virus, I decided to spend some spare time on installing the new Oracle SOA suite on my laptop. I start with downloading the appropriate distribution to my Win XP laptop. After downloading, I start to scan the installation guide for any specific requirements and discover that my laptop is ready to rock.

The installation wizard pops up and I choose for the regular basic installation (because I only want to play with the suite ). After the customization steps the wizard starts to install the suite on my laptop….Everything seems to go ok and the product is installed successfully. Now it is time for the wizard to configure all the components of the suite. The first three configuration steps (EM, ESB and BPEL) are going well…but…hey the configuration assistent for the OWSM is now running for more than 10 minutes and seems to hang at the step where the coreman app is initialized…strange thing. After a look in the SOA suite forum on OTN I discover that the problem is probably due to a bug in the installation script… Assuming this bug is also the cause of my problem (In this thread they are only mentioning a hang in the next step of the configuration phase….), I started to try the work-around.

Here is the short-cut for the hang-problem:

- abort installation (kill opmnctl process first, then kill opmn processes)
- reload opmn manually using opmnctl (if still hangs - reboot and try again)
- rename ORACLE_HOME\cfgtoollogs\cfgtoolcommands to cfgtoolcommands.cmd
- delete rows with OPMN CA in cfgtoolcommands.cmd
- replace at the last row of that file *protected value not to be logged* with -DADMIN_PW=password for oc4jadmin
- run cfgtoolcommands.cmd
- ENJOY 

After completing step 1 till 5 of the workaround, I start the cfgtoolcommands.cmd to finish the installation..and quess the procedure still hangs during the configuration of the OWSM (no wonder it does!). I decide to comment this step out and try to install the suite with out the configuration of the OWSM completely. This time everything went ok..YEAH !

I’m in a good mood now after finishing the installation of the SOA suite. I decide to test all the components if they are indeed working and start with the Enterprise Manager (EM). Using the appropriate url and my administrator username/password, I log in to the EM..
Slammmm… I immediately get an error right in my face :

This OC4J is configured to make JMX connections via RMIS and fall back to RMI if the RMIS port is not configured. The RMIS connection on the OC4J instance home on Application Server assoa.locahost is configured but a connection could not be established. The JMX protocol is specified by the oracle.oc4j.jmx.internal.connection.protocol property in opmn.xml

So I go back to the forum on OTN , but no clues there…so I start to search the net for a clue on my problem…and after a while I discover somewhere on a forum (Sorry, the url to the posting has vanished) a possible solution for my problem. A guy on the forum had the same problem as me and solved it by changing his computername in a name without any ‘_’ characters in it. Problably Oracle cannot deal with such computernames..!? I change my computername too to fulfill this assumption and try again. Now everything seems to work fine and I can start every component console in my browser.

Ok happily ever after ..everything is installed and even the OWSM is working properly at first sight! I can start playing around with the SOA suite.

At the end of this blog posting a strange feeling is still with me because to day I spoke to a collegue of my and he told me that he has installed the suite without any problems…Arghhhhhhhh
Because I’m not a Application Manager or DBA I do not have a lot of experience with installing products, therfore I do not have a good reason to explain why I have problems with installing the suite and my colleque doesn’t. I also questioning myself how well an installation procedure is tested and documented if I have to do a lot of custom work-around steps to fulfill an installation procedure. And according to the SOA suite forum on OTN, I’m not the only person having these problems..

Oh there is also some positive news…after installing the Oracle SOA suite, you’re left with a nice set of tools to practice SOA concepts with

The type 2 Slowly Changing Dimension (SCD) feature from OWB 10.2 has been explained shortly in last week’s entry. Today I will zoom further into the usage of this new feature and in particular some of the shortcomings. First off, I’m very enthousiastic on the subject of DWH development with OWB but in some cases a workaround is necessary. The case today is one of these examples. In short: type 2 on dimension attributes works perfectly fine when the changes occur on the lowest level of the dimension. If you want to catch changes on higher levels as well then every level will need an effective and expiration date in the OWB implementation, but changes on ‘higher level’ attributes do not always deliver the results you might expect.

This may sound a bit vague at this stage but hopefully more becomes clear from the example in this article in which a product dimension with the levels product_class, product_group and the detail level product.

To recreate the full type 2 on multiple levels for this example use the following scripts.

The source table:


CREATE TABLE PRODUCTS_LIST
(
PRODUCTCLASS_CODE VARCHAR2(10 BYTE),
PRODUCTCLASS_DESC VARCHAR2(100 BYTE),
PRODUCTGROUP_CODE VARCHAR2(10 BYTE),
PRODUCTGROUP_DESC VARCHAR2(100 BYTE),
PRODUCT_CODE VARCHAR2(10 BYTE),
PRODUCT_NAME VARCHAR2(25 BYTE),
PRODUCT_DESC VARCHAR2(100 BYTE)
);


GRANT SELECT ON PRODUCTS_LIST TO OWB_R2_TARGET;

The source table data (minimal to isolate the issue):


INSERT INTO PRODUCTS_LIST ( PRODUCTCLASS_CODE, PRODUCTCLASS_DESC, PRODUCTGROUP_CODE,
PRODUCTGROUP_DESC, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_DESC ) VALUES (
'Toys', 'Toys for small children', 'Boardgames', 'Non-electronic games', '79897AB'
, 'Chess', 'The original chess game');
COMMIT;


The only things we need to develop in OWB are:
- The table metadata definition ‘ PRODUCTS_LIST’ for the 0_Source module.
- The DIM_PRODUCTS_LIST dimension from the 2_Datawarehouse module.
- The DIM_PRODUCTS_LIST table from the 2_Datawarehouse module.
- The DIM_PRODUCTS_LIST sequence from the 2_Datawarehouse module.
- And of course the M_Products_List mapping, made as simple as possible to demonstrate the issue.

Issue:

Run 1:
After everything is installed and the mapping has been run for the first time, you will see three rows in total. one for each level. This is all expected and well.

Run 2:
Now, if we change the product description from ‘ original chess game ‘ to ‘ new chess game’ the output will be like step 2 in the Excel file. This is also correct; there has been a type 2 change on the lowest level (product name has a ‘ trigger history ‘ which leads to another detail record and closure of the old one. Four records in total.

Run 3:
The change of the product name, also with trigger history, correctly inserts another new record and closes the old one. Five records in total.

Run 4:
But now the highest level group (product class) description changes from ‘ Toys for small children‘ into ‘ Toys for everyone ‘. This description attribute also has a trigger history.

First off OWB creates a new high level (product class) record and closes the old one, this is to be expected. But you would expect that the detail level also would be duplicated; since the product ‘Chessnew’ has got a change in a higher level description and thus has changed also. To state it in a more generic way: a high level change should also cause a type 2 transaction for every lower level! In the result you see that the product class of the ‘Chessnew’ product has been changed without a type 2 transaction. Functionally speaking: in history you can never tell that the Chessnew product once had the description ‘Toys for small children’.

This also ripples though the level in between, in this case the product group, so to make the example cleaner you can also recreate this without the product group.

Final thoughts

The SCD implementation is a very helpfull addition to the OWB featues but there is still some work to be done. In my current project we have solved this by mapping all of the attributes on to a single level (with a single effective and expiration date) and then the type 2 works fine. But this would be unfortunate if we might want to use Oracle OLAP in the future; all the levels would have to be re-created again.

Suppose you work with BPEL. After a period of development everybody is happy with the processes and it is decided to go live. In the first few days there’s some tentative use, but everything seems to go perfect.

So, more and more BPELs are activated in production and the workload increases. It’s a new environment, so you decide to keep an eye on the BPEL Console, to see what is happening. Suddenly you notice that there are no activities on the console: no processes . You look up to see what is going on, but everybody seems to be working. Eh? Something weird must be going on, as people are executing processes, but no messages come into the server …. at least, as far as you can see!

Time to take a deep dive into the domain.log from BPEL. There you notice the following message:

<2006-06-21 17:13:58,050>
Error while invoking bean "cube delivery": Cannot insert audit trail.
The process domain was unable to insert the current log entries for the instance "39179" to the audit trail table. The exception reported is: ORA-04021: timeout occurred while waiting to lock object.


What happened? The process was executed normally, the messages were sent to the BPEL Process Manager, but the update of the BPEL Audit trail failed. This means BPEL still thinks it is waiting for input, whereas the input has already arrived. So now you have BPEL processes who do not show the real state!!

What really happens here is that the update of the BPEL Cube Instance fails due to the fact that there is a discrepancy between performance parameters of the various components. The config-files involved are: server.xml, data-sources.xml, orion-ejb-jar.xml and domain.xml

In all those config-files there are settings for the number of threads. There is a very important correlation between these settings, being:
a) the sum of the InvokerBean and WorkerBean threads must be greater than or equal to the value specified for the dspMaxThreads (domain property)
b) the connection pool size must be greater than or equal to the sum of the dspMaxThreads property value in Oracle BPEL Console.

Stated differently:

Threads (Invoke + Worker ) >= dspMaxThreads < = Connection Poolsize

Sources:

In this article I will shed some light on a neat new feature in OWB 10.2: dimension roles.

In a typical datawarehouse you will find that you need to connect the same dimension from the fact table a number of times, with different meanings. A familiar example is the time dimension. ‘Time’ is meaningless untill given the proper foreign key to the fact table. In your fact table (or cube in OWB slang) you could connect to this same table as ‘order date’ , or ‘proces date’ or ‘ shipping date’. All these relations will be implemented as foreign keys between the fact table and the time dimension. Of course, you can program these relations yourself like it was done in the earlier versions of OWB but with this new release the Dimension Roles feature has been implemented which will make this process a lot easier.

This feature is similar to creating aliases in your database or semantic layer (BO Universe, Cognos Catalogs, Discoverer EUL’s); you virtually copy the dimension. In OWB you have the opportunity to give any dimension a role and description in the name tab of the object browser, the first screen when creating or editing the dimension. This role can be selected from your fact table: after you select the appropriate dimension and level you can also select the role in which this dimenion will be given meaning to.

This all returns in the datamodel; the role will show up here as a name tag for your fact-dimension relationship and in the mapping you will be able to distinguish the different meanings of the same dimension easily, and (centrally) maintanable.

The recent new release of OWB has been an incredible step forward for Oracle Business Intelligence. With this new release all sort of intereseting new features have been added like data profiling, ERP support, schedule integration and so on. One of the most interesting features for BI developer is the new support for history storage. OWB now supports the type 1, 2 and 3 concepts for Slowly Changing Dimensions (SCD). In this article I will give some thoughts of the implementation chosen by Oracle, and of course how to develop exactly what you want in the end.

First off, OWB has implemented the history concepts on table level. Which means that either all your attributes, or none, will be processed by the chosen history type. An important feature request would be to implement the history types on attribute level; instead on table level. Dimensions which combine type 1, 2 and even 3 are all too common…

For type 1; it’s easy. Once you have configured your dimension as a type 1 dimension (in OWB terms; all attributes are type 1) you basically don’t have to do anything more. This works fine.

For type 2; it’s another matter. When configuring your dimension attributes as type 2 attributes by setting the trigger history value you effect that changes will lead to closure of the old record and an insert of the new value. Effective and expiration dates are set correctly (although overlapping) and can be changed to suit your needs. But, if one of your attributes is neither a trigger history, effective date, expiration date or business key then the first problem occurs: changes which hit this attribute will be ignored.

Anyway, in most cases doing nothing (i.e. ignoring a change) isn’t a strategy which leads to success for many datawarehouses, but that is exactly what happens if you don’t configure your attributes in OWB type 2. So if you don’t want all of your attributes to be type 2 you will have to develop a seperate process for these attributes. This will typically result in a mapping with a splitter and two targets: the dimension operator for all your type 2 – trigger history – attributes and one target table operator – the dimension table - which is set to update to realize a type 1 change for these attributes.

The same goes for the type 3 settings.

Basically, the features really do shorten development time but untill OWB features a SCD technique which combines (at least) type 1,2 and 3 you still need to work around the dimension a bit and update directly to the table for the part which isnt fully supported (i.e. a type 2 dimension will need a table target operator for type 1 transaction). This bit is just like implementing these concepts in the previous version of OWB.

After finishing my first project with the new OWB R10.2 (AKA Paris) I’ve learned some very interesting things which I will share in this weblog the coming time. To kick this off I’ll post a nasty error, the background and the solution.

Whenever you try to deply a mapping which contains a source in a 9.2 database through a databaselink you may encounter the following error message:

ORA-06550: line 0, column 0:
ORA-04052: error occurred when looking up remote object