Any source as an OData feed using Data Virtualization

This article will guide you through the Teiid Designer steps to expose a source as an OData feed.  We will use a Salesforce source for this example, but this process applies to any Data Virtualization supported source.  This example uses an OpenShift Data Virtualization instance, but any Data Virtualization or Teiid instance may be used. 

Prerequisites:

1. Install JBoss Developer Studio with Data Virtualization

• Install Developer Studio with Data Virtualization tooling to your system.  Just follow the instructions here to install JBoss Developer Studio. 

2. Add a JBoss Server with Teiid instance in Developer Studio

• Refer to the Provision Data Virtualization on OpenShift instructions .  Creation of a local server is essentially the same process.

3. Get a Salesforce developer account

• Go to http://www.developerforce.com/events/regular/registration.php?d=70130000000EjHb , to register for your account.  You will get an email with a link to login into your account.
• Login to Salesforce interface with your user/password, then go to "Personal Setup > Reset My Security Token".  Reset the security token.  You will get another email with the security token.
• When connecting from Teiid Designer, use the Salesforce username.  The password is the combination of Salesforce password + security token.
• The developer account is populated with some data, but you can create more demo data easily via the Salesforce interface.

Create the Model project and Salesforce source model

1. Launch JBoss Developer Studio and switch to the Teiid Designer perspective.  The perspectives are shown in the upper right corner of JBDS.

2. Create a Model Project

• in ModelExplorer, Rt-click > New > Teiid Model Project
• in the Model Project wizard, enter SFProj for the Project name.  Click Finish to create the empty project:

3. 'Import' the source model from your salesforce account

• Select File > Import... > Salesforce >> Source Model , then click Next

• On the 'New Connection Profile' page, Salesforce should be selected.  Type in SFDemoCP for the profile Name, then click Next

• On the Salesforce Connection Properties page of the Connection Profile Wizard, enter your Salesforce User Name <myuser@mycompany.com> and Salesforce Password. The 'Password' is the Salesforce password and security token combination as required by Salesforce.  Your Salesforce user must also have API access permission granted in Salesforce.  Click on the 'Test Connection' button to verify a successful connection.  Click Next.

• Click Finish on the Connection Profile wizard summary page.  Then back in the wizard, on the 'Salesforce Credentials' page, click Next.  Assuming a successful connection, the Salesforce details will be presented as shown below.  On the 'Select the Salesforce objects to Model' page, you can choose the Salesforce Objects that you want to import.  For this demo, click Deselect All.  Then check only the Price Book Entry and Product tables. click Next

• On the 'Target Model Selection' page you are presented with some additional import options.  For the Model Name, enter SFSourceMdl.  Under Import Options, leave everything unchecked. Click Finish

• Upon finish, you will see the source model in Model Explorer.  SFSourceMdl is the relational represention of your Salesforce source.

Create a custom view

Data Virtualization allows you to abstract your data any way you wish.  We will add a single view layer which exposes our desired Salesforce data as a view.

• Create a View Model.  In Model Explorer select SFProj then Rt-Click > New > Teiid Metadata Model.  In the New Model Wizard, enter SFViewMdl for the Model Name.  Choose Model Class : Relational and Model Type : View Model.  Click Finish
  
• In Model Explorer, select the empty view model SFViewMdl.  Rt-Click > New Child > Table...

On the Create Relational View Table dialog, enter ProductInfo for the name.

We want our view to contain just a few of the available columns.  Select the Transformation SQL tab.  Enter the SQL below for the Transformation SQL.  Click Finish.

SELECT ProductCode, Name, UnitPrice
FROM SFSourceMdl.salesforce.PricebookEntry

• The ProductInfo view is added as shown in ModelExplorer

• Add a Primary Key to ProductInfo

When a VDB is deployed to Teiid, OData support is automatically provided.  However, each table to be exposed as an OData feed must have a primary or unique key.  See Teiid OData Support documentation for more info.

To add the Primary Key to ProductInfo, select ProductInfo in ModelExplorer then Rt-Click > New Child > Primary Key .  In the dialog (shown below) move the ProductCode column to the right, then click OK to dismiss the dialog.

Create VDB for deployment

We've finished the view definition for our demo.  Now we need to bundle everything into a VDB (Virtual Database) for deployment to our Teiid instance.

In Model Explorer select SFProj then Rt-Click > New > Teiid VDB.  On the New VDB dialog, enter SFVdb for VDB Name.  Then click the Add button - add SFSourceMdl and SFViewMdl from your workspace.  Click Finish.

• The VDB is created and is shown in Model Explorer.  We are now ready to deploy the VDB

Deploy the VDB

• First, make sure your Data Virtualization server instance is started.  This can be an instance on your local system, or it may be an Data Virtualization instance on OpenShift.  If OpenShift, refer to the Provision Data Virtualization on OpenShift instructions.
• Deploy the VDB.  In Model Explorer, select SFVdb.  Rt-Click > Modeling > Deploy - to deploy the VDB.  You will see a status dialog as datasources are created (if necessary) and the VDB is deployed.  At completion, check the Servers View.  See below that the Data Source SFSourceMdl has been created and the SFVdb has been deployed.

Verify the OData feed

On completion of the VDB deployment, we can access the OData feed.

• To access the OData feed, open a browser.  I've deployed to OpenShift, so the URL looks like this:

http://[app]-[domain].rhcloud.com/odata/sfvdb.1/SFViewMdl.SFProductInfo

on a local server, it would be

http://localhost:8080/odata/sfvdb.1/SFViewMdl.SFProductInfo

We can also get JSON format with a URL like this

http://[app]-[domain].rhcloud.com/odata/sfvdb.1/SFViewMdl.SFProductInfo?$format=JSON

You are prompted for username and password.  The user must be defined for the teiid instance and must have the odata role.  For the OpenShift instance, enter username user and the password that was provided when the cartridge was installed.

Conclusion

This ends our demo - illustrating how to use Data Virtualization to create a view of our Salesforce data and expose as an OData feed.  This process can be applied to any datasource that Data Virtualization supports, not just Salesforce!  See Supported Configurations for more info on the supported sources.  Any you can create views that join disparate sources if you wish - in any combination!