Using JBoss DataVirtualization to access SalesForce

teiid-logoIn this blog post I would like to explain how to access SalesForce.com with JBoss DataVirtualization (JDV). For those of you who do not know what JDV is, here a short introduction:

What is JBoss DataVirtualization?

JDV is the Red Hat productized version of the Teiid project by JBoss. But what is JDV doing?

JDV is a data virtualization tool which collects data from different sources and provides a simple access system to those data sources for the clients via REST, SOAP and JDBC/ODBC. But it is not “just another data source” which would copy all the data into another black hole. JDV is a meta engine. It only contains the routes to how to access the data in those different sources.

JDV_explained

JDV itself has a powerful QueryEngine which makes sure that the native data is retrieved in an optimal way. It could be used embedded in you own Java application but the supported way is to use JDV in combination with JBoss Enterprise Application Platform (EAP). In this combination, JDV leverages the transaction and security engines and many more of the underlying application server.

Prerequisites

In this blog post you should be familiar using JBoss Developer Studio (JBDS) and you should already have installed the Integration Tools Pack.

Please Download JBDS from here:
http://www.jboss.org/products/devstudio/overview/

And download JDV from here:
http://www.jboss.org/products/datavirt/overview

All the above products can be used for free as long as you are only using them in development. You should get a subscription from Red Hat as soon as you’re going to use it in test or production environments.

Installing JDV

Now install JBoss DataVirtualization. For this, please open a terminal window and type

java -jar {path-to-jdv}.jar

After a few moments the graphical installer pops up and asks you to accept the end-user license agreement. The next step is about selecting a folder in which you would like to install JDV.

After choosing which components you would like to install (all), you are asked to create some users. Check the option “Check to use one password for all default users” and choose a password. For example “change12_me” which is long enough, contains digits and non-alphanumeric characters.

The users are:
– admin for all JBoss EAP administrative tasks
– dashboardAdmin for using the dashboard of JDV
– teiidUser is the user to access JDV generated data sources

Make sure to select “Enable odata access”, which makes it easy to access generated data sources via OData protocol (which is REST) and click on next to proceed.

For our example, we do not need to have a properly configured maven repository, so please click on “Do not configure maven” for now.

Red_Hat_JBoss_Data_Virtualization_Installer_and_Blogo_and_Get_Started

Then click next and use defaults for every other step until JDV is getting installed.

Configuring JBDS to use JDV

JBDS is based on a recent Eclipse version, so everybody who is experienced in Eclipse knows how to use JBDS. After starting up JBDS, you’ll see a pane called JBoss Central.

Teiid_Designer_-_JBoss_Central_-_JBoss_Developer_Studio_-__Users_wanja_workspaces_partner-conf

Click on the Tab “Software/Updates” to install the SOA and Integration Tooling. Once done and after a restart of JBDS you should be able to create a new Teiid Model Project.

Creating a Teiid Model Project and importing the Salesforce Model

Creating a Teiid Model Project is like creating any other project with Eclipse: It will open a wizard where you should make sure to check the default folders of “sources”, “views”, “schemas” and “web_services” on the Model Project Options panel.

Now you should have a new project (I called mine TestSFDC) with those 4 different folders created… finally!

Go to the sources folder, right-click and choose Import… Choose Salesforce >> Source Model and click next:

Import_and_Teiid_Designer_-_JBoss_Central_-_JBoss_Developer_Studio_-__Users_wanja_workspaces_partner-conf

Create a new Connection Profile, choose Salesforce and enter your credentials of Salesforce in the corresponding fields. Please make sure to add the Security Token after your regular password if you are using a company salesforce account.

Click on Connect when wizard completes and check with Test Connection if your credentials are correct.

Then click on next and the wizard will start to gather salesforce metadata.

What is this all about?
Salesforce is like any other relational or object database. It contains objects like Address, Account, User etc. with fields and relations to each other. An account has users and addresses etc. And those metadata needs to be scanned now so that the designer is able to draw the correct graphical representation. This metadata is also being used within the query engine of JDV to optimize access to fields and relations.

Create_Relational_Model_from_SalesForce_Data_Model_and_Teiid_Designer_-_JBoss_Central_-_JBoss_Developer_Studio_-__Users_wanja_workspaces_partner-conf

Now you should only select what you really need to work with. Depending on how much customization your company / customer has done with Salesforce the data model could look really confusing. If you need other objects later to work with, you can always regenerate the source model. This is one of the biggest benefits of using a data virtualization tool like JDV: You are not any longer dependant on the model. But this gets clearer later.

For now you should only select Account and User objects.

In the next step you should give your model a name like TestModel and make sure that you are generating the audit fields. This is to make sure that you are able to see fields like Created By, Created date etc.

Click on Finish and the wizard is done and your source model should be created.

Creating a Virtual Database (VDB)

The next thing which needs to be created is the so-called virtual database or VDB. A virtual database contains all necessary models and metadata configuration and security configurations to make it possible to access any data through it. This VDB gets deployed into a JDV enabled JBoss Application Server and can then be accessed via REST or JDBC.

To create a VDB, right-click on the project and select New —> Teiid VDB, give it a name – for example “accounts” – and add the TestModel.xmi to it.

New_VDB_and_Teiid_Designer_-_TestSFDC_sources_TestModel_xmi_-_JBoss_Developer_Studio_-__Users_wanja_workspaces_partner-conf

This is already enough to test your newly created VDB. It would already be enough to have an easy way for your own application to access salesforce.

To test if everything works so far, right-click on accounts.vdb and select Modeling —> Execute VDB from the context menu.

This deploys the VDB to the default Teiid Server instance and opens the Database Development perspective in JBDS.

Database_Development_-_accounts_-_localhost_-_Teiid_Connection_SQL_Scrapbook0_-_JBoss_Developer_Studio_-__Users_wanja_workspaces_partner-conf

As you can see, the VDB is just another database in your eclipse perspective. JDV comes with a JDBC driver which this perspective is using. You could also use SQuirreL SQL or any other SQL tool as long as you are using the necessary JDBC driver to access JDV.

Now lets check to see if you are able to access Salesforce through JDV:

Simply enter
SELECT firstname, lastname, email FROM salesforce.User_ u where lastname like ‘P%’

This will create a list of all users with a last name starting with P.

Creating a View Model

As you can see you are able to access Salesforce through JDV without a lot of work. Now you have created a 1:1 view to the pure salesforce model. When you are using data virtualization technologies you do not necessarily want to have 1:1 models. Instead, you want to change the work model so that it reflects your own needs. To do that we are creating a view model now which is our own representation of the salesforce model.

Go back to the Teiid Designer Perspective in JBDS, right click on views and select New —> Teiid MetaData Model

Give that model a name (account_view), make sure it is a relational model and of type View model. Choose Transform from an existing model and select your TestModel.xmi (ignoring the message which will pop up) and click on finish.

In your views folder should now be a new XMI file called accounts_view. Until now it’s still a 1:1 copy with all fields and all other relationships. Now it’s time to change that.

First lets rename both tables (by right clicking on them and choosing Rename from the context menu):

  • Rename User_ to Person
  • Rename Account to MyAccount

Then let’s double click on the Person table which will open the accounts_view.xmi panel. This panel is divided into two parts. The left side is the view which represents the model as you will see it afterwards in the SQL editor. The right side shows all the source models which are used to build the view.

Double click on the big arrow sign with the T on it to open the transformation editor.

1430490705_full.jpeg

It’s time to change your model now. Instead of having a

SELECT *
FROM TestModel.salesforce.User_

Make sure that we only have those columns here which are of interest, like ID, Username, Lastname, Firstname, Name and Division. So in the SQL editor simply type:

SELECT Id, username, lastname, firstname, name, division
FROM Testmodel.salesforce.User_

Save the model and click on the “Reconcile Transformation SQL with target columns” button (the one next to the “Supports update” check box). In this dialog you’ll be able to remove unused virtual target columns from the view model by simply selecting and then deleting them. Click on OK to finish this dialog. Now your transformation model should look like the one in the screenshot.

There are just a few things left to do now:

  1. Removing all of the foreign key constraints from the newly created view model (MyAccount and Person)
  2. Adding the accounts_view.xmi to the VDB
  3. Synchronize the content of the VDB
  4. Publishing the VDB to the JDV instance to test the changes we’ve made

1) On the Model Explorer open MyAccount and select all foreign key constraints except the one with the primary key (id_PK) and select DELETE from the context menu.

Do the same for the Person object in the model explorer.
2) Open the accounts.vdb and add the accounts_view.xmi view model to the content of the VDB by clicking on the green plus sign on the bottom left side of the VDB editor.

1430491955_full.jpeg

3) Click on synchronize models button (see picture above) to resynchronize the VDB.

4) Right click on the accounts.vdb VDB and select Modeling —> Execute VDB to deploy the VDB to the configured JBoss Application Server.

Using build in REST access via ODATA

JBoss DataVirtualization has by default a build in REST interface which allows everybody to access the data in a RESTful way. This REST interface is using the ODATA protocol, which was originally invented by Microsoft but it’s now getting a standard protocol of RESTful access to data.

You can access the ODATA interface by using the following URL:
http://localhost:8080/odata/<vdb-name>/<table-name>

In our case it is:
http://localhost:8080/odata/accounts/salesforce.Person

To access the ODATA interface, you have to provide the teiidUser user name and password (which was change12_me).

More about the ODATA protocol could be found in the official Red Hat documentation of JBoss Data Virtualization.

localhost_8080_odata_accounts_salesforce_Person__00560000000m64FAAQ__

Summary

In this blog post you have learned to setup JDV and how to connect JDV to Salesforce.com to access the data either via JDBC or via REST / ODATA.

Those examples here are quite easy. We have not talked about security nor about creating a view model which contains multiple data sources. And we do not even have scratched the surface of optimizing the model.

This is enough stuff for another posts in this series.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: