Any source as an OData feed using Data Virtualization

Version 4

    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

    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.

    JBDSDesignerPerspective.png

    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:

    JBDSCreateProj.png

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

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

    JBDSImportFromSF.png

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

    JBDSSFConnProfile1.png

    • 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.

    JBDSSFConnProfile2.png

    • 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

    JBDSSFImport-SelectObjs.png

    • 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

    JBDSSFImport-SelectModelAndFinish.png

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

    JBDSExplorerWithPhysicalModel.png


    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
      JBDSCreateViewModel.png
    • In Model Explorer, select the empty view model SFViewMdlRt-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
    
    
    
    
    
    
    
    
    
    

    JBDSCreateViewTable.png

    • The ProductInfo view is added as shown in ModelExplorer

    JBDSExplorerAfterCreateViewTable.png

     

    • 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.

    PkColumnDialog.png

    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.

    JBDSCreateVDB.png

    • 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 SFVdbRt-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.

    JBDSServerViewAfterDeployment.png

    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!