Well, apparently it’s Friday Fun Day over here in Seattle. I was going to head up to the mountains for the weekend but plans changed. So how about a little frankendriver Vess update with some ODI goodness thrown in?

Vess has some really interesting data integration possibilities that I’ve mentioned before, one of which is being able to drop it into Oracle Data Integrator and use it as you would any other JDBC driver. I hadn’t really tested this out though, until yesterday. It turns out that it works surprisingly well.

First off, the Vess driver files need to be added to ODI. I added it to my standalone agent/client:

Adding Vess Java JAR files to ODI12c

As you may or may not be able to tell, adding new drivers to ODI is mostly just a matter of dropping the JAR files into a folder, editing the additional_path.txt file to include that folder, and then starting the client.

I created anew technology type within ODI that was basically just a copy of the Hypersonic SQL driver. Internally, Vess is kind of shoehorned around an H2 database driver, which is actually a bit of a cousin to Hypersonic. In any case, they’re at least close enough to share the same ODI technology type.

After creating the new technology, I defined a new physical server:

Setting up Vess physical topology

As I’ve shown before, Vess has its own JDBC URL syntax that generally includes the Essbase/APS server as well as a reference to an Essbase app and/or cube. In the above screenshot you can see the specific Vess JDBC driver class referenced as well as the URL to use embedded mode to communicate with one of my local Hyperion servers.

Next I needed a data server. Nicely enough, due to the fairly complete metadata that Vess supplies through the JDBC driver model, the list of schemas populated automatically (in this case, SAMPLE) and I knew things were looking good:

Setting up the physical schema

Here’s the same screens but shown together, along with a new logical schema named VESS_SAMPLE_BASIC:

Logical topology shown with JDBC configuration

With the physical and logical topologies setup and just associated through the Global ODI context (never use Global except for possibly some one-off testing like this, by the way), I went over to create the model.  I selected the Vess technology and logical schema, and much to my enthusiastic amazement, when ODI went to reverse engineer the model, it brought back all of the Vess tables perfectly:

Reverse engineering models works out of the box

For the sake of simplicity I just brought in the BASIC_DIM_PRODUCT table:

Viewing attributes on Vess table

You may also notice that the logical lengths, data type, and not nullability status are all brought in perfectly as well, again because Vess goes out of its way to model this column metadata as accurately as possible.

In the world of Vess, it models a schema after an Essbase application, and then tables for each database and its content. In this case, that means that the table SAMPLE.BASIC_DIM_PRODUCT is the table that models the Product dimension in Sample/Basic. I find that this is a good model to balance performance and keep things organized well enough.

For testing purposes, I decided to make a simple mapping that will extract dimension data, then filter it to only level-0 members, then populate that into a simple table in MySQL.

Creating a mapping to extract Vess outline data

This is just a bread and butter mapping in ODI, with the simple condition criteria shown in the bottom middle pane.

After that, we just need a simple physical implementation (using the target database as the work schema for this):

The mapping relies on a work table in MySQL (in this case)

And then try running it:

Viewing the execution in Operator

And it worked! Of course, given that these were disparate data sources with a vanilla IKM, ODI just did a row-by-row mapping, which isn’t ideal for large data movement but works fine in this case.

And sure enough, after inspecting the target table in MySQL, all of the proper data was indeed there:

Viewing the data in the target MySQL table

It looks like I might need to do a small tweak since the shared members under the Product Diet Cola hierarchy got repeated here, so I can either do a DISTINCT or look at tweaking the extraction algorithm a bit. We’ll see.

Next Steps

Vess continues to be an interesting side project. For the moment I have turned off the data extraction tables as I am reworking the way that data from a cube (as opposed to metadata) is “projected” into tables. I think it needs to be a little more flexible in order to be useful in other tools, such as DVD. If you want to test with Vess, please email me for a copy. Many of you have, although some of you are only interested in the data extraction piece, which is still a work in progress. So if you want to play with the outline and other stuff (substitution vars, database stats, server sessions, and others), you’re more than welcome to give it a shot, just send me a message. The pure data stuff is coming along as time permits!