How to build Server Side Data Mashup using Project Mural

Here is step-by-step write-up on how SUN's Open Source Master Data Management (MDM) Suite can be used to build Server Side Data Mashup without practically writing any code!

Please note that Data Mashup technology at SUN is being constantly evolved even as you are reading this blog. Intent of this write up is to give readers a detailed peek into existing capabilities and what it could offer when made available in the coming Mural releases.

Here's the use case to aid the concept being explained ...

Sandra owns a small laboratory equipment shop in Denver. Her suppliers have levied a 2% additional tax on the transportation cost which varies based on the distance of delivery. (gas is expensive and nothing is free). Sandra is looking out to optimize her profit margins by sourcing more from near by dealers. She needs a service that gives her distance of delivery of her suppliers. She has her supplier data on a spread sheet and she is able to Google out delivery point address of all lab equipment vendors in the US from Internet (each with unique state allotted vendor ids). Lets see how data mashup can help her with the cost cutting mission.

Prerequisites
1. NetBeans IDE

2. Glassfish App Server

3. SUN Data Integrator and SUN Data Mashup Service Engine (JBI Runtime)

4. SUN Data Integrator and SUN Data Mashup Design time environment NetBeans Modules

Step 1: Identify your data sources

There are two data sources involved here.

• Spreadsheet (Data format available with Sandra)

• A text file with csv data (Data from Internet)

 

Step 2: Create Mashup Database 

1. Launch your NetBeans IDE
2. Go to Tools --> Mashup Database --> Create Mashup Database.
3. Name the database appropriately for the project/service it serves. Say Finish. A success message confirms that the database was created successfully.

Step 3: Add External table (Spread Sheet) to the created mashup database

As the database involved here is non-relational, data needs to be staged into a relational database so as to use it into a federated data pool.

1. Go to Tools --> Mashup Database --> Add External Tables ...
2. Add the spread sheet data into the mashup database
3. Browse to the xls folder and select the file. Add it into Selected table list.
4. Ensure that correct table type is listed by verifying the highlighted blocks.
5. Preview the data and say next.
   
6. Edit the column properties by double clicking on the column fields in the table. Press enter when done. The changes get reflected in preview table window.
7. Press Finish and look out for success message to confirm that the table was added.
   

Step 4: Add External table (Text File) to the created mashup database

As the database involved here is non-relational, data needs to be staged into a relational database so as to use it into a federated data pool.

1. Go to Tools --> Mashup Database --> Add External Tables ...
2. Add Text file (delimited) data into the mashup database
3. Browse to the folder with text file. Select and add it into table list. Go to Next.
4. Ensure that correct table type is listed by verifying the highlighted blocks.
5. Change the table Metadata properties like default precision, delimiter etc suitable for file setting. Preview if the data in the text file is parsed properly and say 'Next'.
6. Change the default column names to some appropriate names. Say Finish.
7. Look out for success message to confirm that the table was added.

Step Verification

• Verify that the database was created in the NetBeans Services Tab with tables and columns.
• Right click on the table and 'View Data' to see if the data from spread sheet and CSV file in available in the database.

Step 5: Creating Mashup Project using NetBeans

1. On the NetBeans menu, go to File --> New Project --> SOA --> EDM Module --> Next
2. Name your mashup project and select the location where you want the project to be created. Finish the wizard.
3. Observer the Mashup project being created at the specified location. Project also shows up as active project on the NetBeans projects tab
   
4. On the NetBeans Project tab, browse to the Mashup Project (SandraEDMApp for this demo). Right click on the 'defaultpackage' node and select New --> Others --> SOA --> EDM --> Next on the pop-up.
5. Name the EDM file (SandraEDM for this project ) and Finish the wizard. A blank canvas is created on the active NetBeans window. Also, observe that SandraEDM.edm is created under default package of your SandraEDMApp project.

Step 6: Creating Data Join

Till the previous step, we have achieved bringing the diverse data into a common staging area. In order to create federated view, data in the staging tables need to be federated. Here is how it can be done by creating data joins.

1. From the NetBeans palette, drag-and-drop the join operator on the mashup canvas.
2. Create New Join View editor comes up on this action.
3. Press "All" button on the editor to join all the tables.
   Note that join preview gives an option of editing join types i.e. inner, outer etc.
4. Select OK to finish the wizard. Canvas will be added with additional root join widget.
5. Open Properties panel on NetBeans and select Root Join Widget by clicking over it.
6. Edit the join condition by edit button on Join condition property.
7. For this case join condition is (SANDRA_DATA.VENDORID = SUPPLIER_ADDRESS.VENDORID) where vendorid was the Licence Id for the vendor.
8. Do not forget to insert the Runtime Input Argument into the mashup design. You can insert the argument (arg_0 which is the default value) as shown below.
9. Save the project. Right click on the project and select Build Project. Observer that SandraEDM.wsdl is created below in the project node.
10. Your Data Mashup is ready to be deployed as a service !!

Step 7: Deploy Data Mashup in Glassfish App Server

1. To create a composite application, go to NetBeans files menu. Under Files, select --> New Project --> SOA --> Composite Application -- Next.
2. Select Project Location and Name for the Composite Application Project. For this project, name being selected is SandraEDMCompApp.
3. Select Finish. Observe that the Composite App project is created on the NetBeans Projects tab. Also observer that a casa (composite application service assembly) editor opens up in the NetBeans.
4. Now add Mashup Application to the JBI modules of the composite app. Right-click on the JBI Modules and select Add JBI Module.. from the list. Select SandraEDMApp project in the list shown and say Add Project Jar File. Observe EDM app being added as JBI Module in the Composite App.
5. From the casa editor palette, drag and drop the HTTP WSDL bindings into the editor and build the comp app once. An end point for connection to SandraEDMApp comes up on the editor.
6. Connect the HTTP consumer endpoint to the SandraEDMApp endpoint.
7. Build the Composite App project once again.

Step 8: Tweaking the Composite App wsdl

* Note that this step would eventually be removed but this is what is needed for now.

1. Double click and open the wsdl generated in SandraEDMCompApp --> Process files --> SandraEDMCompApp.wsdl
2. Here is how to tweak the wsdl :

• Include part into node <http:operation location="(part)"/>
• change <http:urlEncoded/> to <http:urlReplacement/>
• Change the http service url to include a concerete port instaed of the default port from where mashup service can be accessed i.e. replace ${HttpDefaultPort} with <available port no on the host>. For this project, I am using 9097

Right click on the Composite Application project and Deploy the Application. Look for the success message and verify the same on the Glassfish Application Server.

Step 9: Accessing the Mashup Service.

Open the browser and hit the following URL

• http://129.158.238.222:9097/casaService1/casaPort1/execute

where 129.158.238.222 is the ip address of the host running the service.

Observe that the federated view of the data in two different data sources is reported in JSON format.

Note that the output data format can be changed to xml or webrowset by changing mashup-engine properties and redeploying the same into Glassfish JBI services.

Step 10: Using the Data from Mashup Service

The JSON output from the service can be parsed using standard JSON parsers in any application. The Vendor addresses can be parsed and fed into a Google Locator service and approximate distance to business addresses can be fetched. Based on this, Sandra (hope we still remember her !) can get some crucial insight about sourcing her business from near-by vendors. By the virtue of being a JBI service, data can also be sent over to other services using SUN Enterprise Service Bus.

Database files used to create this Mashup can be downloaded from here.

Sphere: Related Content