13.2. Use the Data Virtualization Web Interface on OpenShift

Procedure 13.1. Install a Box Grinder plug-in

1. Firstly, assign a MySQL database to your OpenShift Data Virtualization instance. Go to the overview page for your Data Virtualization application Click on the Add MySQL 5.5 link (found under the Databases section.)
   The MySQL cartridge will deploy. When complete, you will see a success screen

   Important

   Ensure that you save the credentials information for later reference.
2. Under Tools and Support, click on the Add phpMyAdmin 4.0 link. This adds the web interface for easy management of your MySQL database.
   The MySQL database and management interface deployments are now complete.
3. Load some data into the database using the phpMyAdmin interface. Use the link that was displayed when you installed the phpMyAdmin cartridge at https://myApp-myDomain.rhcloud.com/phpmyadmin
   Use the Root User and Root Password you received when the cartridge was installed.
   In the left tree panel, click on the database that matches the name of your DV application (for instance, jbossdv1). Select the SQL tab. On the SQL tab, copy this DDL and paste it into the text area:

   CREATE TABLE PricesTable  
   (  
     ProdCode    CHAR(40) NOT NULL,  
     SalePrice   DECIMAL,  
     PRIMARY KEY (ProdCode)  
   );  
     
   INSERT INTO PricesTable VALUES   
   ('GC1020', 3499.0),  
   ('GC1040', 19990.0),  
   ('GC1060', 75000.0),  
   ('GC3020', 10200.0),  
   ('GC3040', 38000.0),  
   ('GC3060', 95000.0),  
   ('GC5020', 28000.0),  
   ('GC5040', 59900.0),  
   ('GC5060', 110000.0),  
   ('IN7020', 4000.0),  
   ('IN7040', 16000.0),  
   ('IN7060', 42000.0),  
   ('IN7080', 69000.0),  
   ('SL9020', 4999.0),  
   ('SL9040', 9999.0),  
   ('SL9060', 14999.0),  
   ('SL9080', 19999.0); 
   

   Copy to Clipboard Toggle word wrap
   Finally, click the GO button on the far right of the management interface.
   The PricesTable table has now been created and populated. You can verify the contents by clicking on it.
4. Restart the Data Virtualization cartridge. This is required to initialize the MySQL datasource. Click the Restart application icon on the application overview page
5. Go to http://www.developerforce.com/events/regular/registration.php?d=70130000000EjHb , to register for a Salesforce account. (You will receive an email with a link to login into your account.)
6. Log in to Salesforce interface with your user name an dpassword, then go to Personal Setup - Reset My Security Token. Reset the security token. You will get another email with the security token.
7. When you establish connectivity to the Salesforce instance in the example below, use the Salesforce username. (The password is the combination of Salesforce password and security token.)
8. go to the deployed Web interface on OpenShift at http://APP-NAME-DOMAIN-NAME.rhcloud.com/dv-ui. (APP-NAME is the name you gave the application and DOMAIN-NAME is your OpenShift domain name).
9. From the Data Library screen, click the Create Data Services link. You will be directed to the Create Data Service page.
10. Click on the Manage Sources button. You will be directed to the Manage Data Sources page
11. Click on the MySQLDS source: it is configured, but inactive. Select the mysql5 translator for the source and click the Save Changes button. Upon deployment, the source should become active (a green check mark will appear).
12. Click the Add Source button. This will create a default H2 source called MyNewSource.
13. Click on MyNewSource.
    In the displayed properties, enter SalesforceDS for the Name, Under Source Type, click on the salesforce button. Click the Ok button as this will change the source to salesforce type and set the translator to salesforce. Under Connection Properties, enter the user name for your salesforce account. Enter the password and token combination for the password.
14. Click on the Save Changes button. Click Ok for each dialog box. The salesforce source will deploy (it will take a couple of minutes to finish).
15. Click the back link to go back to the Create Data Service page
16. The next phase is to carete a data service which accesses the MySQL database table. Enter MySQLService for the service name. Enter a description for the service.
17. Click on MySQLDS on the Service Helpers Active Sources tab.
18. Click on the dv61.PricesTable under Tables, then select both columns via the checkboxes.
19. To create the Service View Definition, click the Create Service View button. This will populate the Service View Definition text area.
20. Click the Test Service button to test the service.
21. Click Create Data Service. This will accept your entries and create the service. You will be redirected to the Data Service Details page.
22. The steps to create a salesforce-only service follow the same pattern. Go back to the Data Library then click the Create Data Service button to create a new service.
23. Enter SalesforceProductService for the service name. Enter a description for the service.
24. Click on SalesforceDS on the Service Helpers Active Sources tab
25. Click on Product2 under Tables, then select the Name and ProductCode columns via the checkboxes.
26. To create the Service view definition, click the Create Service View button. This will populate the Service View Definition text area.
27. Test the service as before to see sample data, the click Create Service to create the service.
28. The steps to create a mashup service once more follow the same pattern. Go back to the Data Library then click the Create Data Service button to create a new service.
29. Enter MashupService for the service name. Enter a description for the service.
30. In the Service Helpers section, click on the Join Definition tab. (It is here that you define the join.)
31. Select MySQLDS in the Available Sources drop-down. Under Source Tables, click the PricesTable. Then click the left-hand button to specify it as the Left table.
32. Select SalesforceDS in the Available Sources drop-down. Under Source Tables, click the Product2 table. Then click the right-hand button to specify it as the Right table.
33. In the left-hand side Prices table, check the ProdCode and SalePrice columns. In the right-hand side Product2 table, check the Name column.
34. Leave the JoinType selection on Inner Join.
35. Select ProdCode for the left-hand side Join Criteria column.
36. Select ProductCode for the right-hand side Join Criteria column.
37. Click the Apply button to generate the Service Definition DDL.
38. Click the Test Service button to see example data.
39. Click the Create Service button to create the Mashup service. You will be redirected to the Data Service Details page. The Data Service Details page shows a sample of data and also provides connection instructions and URLs for the different connection options.
40. Click the Back to Library link to go back to your Data Library. On the Data Library page you will see the three services that you just created.

    Note

    Notice that for each service, there are more actions available to you:

    • Edit Service - redirects to the Edit Data Service page.
    • Duplicate Service - makes a copy of the selected service.
    • Test Service - redirects to the Data Service Details page.
    • Delete Service - delete the selected service.
    • Save to File - A service is backed by a teiid 'Dynamic VDB'. This action saves the dynamic VDB xml to a file.