The following procedure leads you through the necessary steps to design a Data Mashup project, and includes:
Creating a virtual database
Adding a minimum of two types of external data, for example a spreadsheet and a text file
Creating a Data Mashup project
Requires Java CAPS 6 Update 1 or Sun GlassFish ESB.
For this procedure you are going to create a federated join using two data files, a MS Excel spreadsheet (company-data.xls) and a delimited text file (supplier-address.txt). Ensure that you have your data files accurately set up before beginning the procedure. The files used in the procedure include the following information.
Table 1 Contents of company-data.xls Spreadsheet
EQ162 |
Funnel |
Shelly glass and labs |
LC05647 |
EQ763 |
Spirit |
Torry Harris Equipment |
LC23456 |
EQ785 |
Beaker |
Torry Harris Equipment |
LC23456 |
EQ232 |
Wire mesh |
Angels Enterprises |
LC34267 |
EQ892 |
Stirrer |
Stirrer Angels Enterprises |
LC34267 |
EQ232 |
Wire mesh |
Kittens Inc. |
LC45634 |
EQ763 |
Asbestos sheets |
Labkron Instruments |
LC56473 |
EQ785 |
Beaker |
Labkron Instruments |
LC56473 |
EQ162 |
Funnel |
Labtech Inc. |
LC56743 |
EQ763 |
Asbestos sheets |
Labtech Inc. |
LC56743 |
EQ763 |
Spirit |
Peter Labs and Instruments |
LC63234 |
EQ563 |
Test tube |
Peter Labs and Instruments |
LC63234 |
EQ092 |
Pipette |
Patel Lab Exports |
LC67384 |
EQ243 |
Bunsen burner |
Alaska Lab Equipment |
LC76483 |
EQ892 |
Stirrer |
Alaska Lab Equipment |
LC76483 |
EQ162 |
Funnel |
Safe Labs Inc. |
LC87635 |
EQ563 |
Test tube |
Davidson Equipment Exports |
LC87645 |
EQ092 |
Pipette |
Andrew Instruments |
LC97627 |
EQ243 |
Bunsen burner |
Andrew Instruments |
LC97627 |
EQ162 |
Funnel |
Andrew Instruments |
LC97627 |
Table 2 Contents of supplier-address.txt Delimited Text File
LC76434|Yahoo Labs and Instruments|1285, 1st Avenue 69th and 70th, New York, NY 10021 | |
LC76323|Isabel Instruments|330 SUNRISE HIGHWAY, ROCKVILLE CTR, NY 11570 | |
LC76483|Alaska Lab Equipment|275 SOUTH STREET, OYSTER BAY, NY 11771 | |
LC63234|Peter Labs and Instruments|430 E MAIN ST, BAY SHORE, NY 11706 | |
LC34267|Angels Enterprises|2950 AVENTURA BLVD, AVENTURA, FL 33180 | |
LC45634|Kittens Inc.|South Town Center, 10450 S State St, Sandy, UT 84070 | |
LC87635|Safe Labs Inc.|Guadalupe Center, 333 Montezuma Ave, Santa Fe, NM 87501 | |
LC56473|Labkron Instruments|Casis Village, 2700 Exposition, Austin, TX 78703 | |
LC56743|Labtech Inc.|Midtown, 911 W 38th St, Austin, TX 78705 | |
LC67384|Patel Lab Exports|3 University Dr, Augusta, ME 04330 | |
LC05647|Shelly glass and labs|633 W 5th St Los Angeles, CA 90071 | |
LC87645|Davidson Equipment Exports|South Town Center, 10450 S State St, Sandy, UT 84070 | |
LC23456|Torry Harris Equipment|Fairlawn Drive Up, 5325 Sw 21st St, Topeka, KS 66604 | |
LC97627|Andrew Instruments|Valero 4550, 115 N Greeley Hwy, Cheyenne, WY 82001 |
The spreadsheet and delimited text file can be downloaded from http://www.esnips.com/web/DataMashupSandraUseCaseFiles. Note that the name of the *.xls file that is available to download has been changed from Sandra-data.xls to company-data.xls in the procedure that follows.
Before beginning this procedure, you must have installed the components listed in Sun Data Mashup Engine Overview, have the above-mentioned files ready and stored on your computer, and have started the NetBeans IDE.
To move forward or backward in the procedure, click Next or Back.
Create the Data Mashup Database.
From the NetBeans IDE tool bar select Tools->Virtual Database->Create Virtual Database.
In the Create Virtual Database window, type a Database Name that represents the project you are creating.
In this procedure the database is called VirtualMashupDB.
After naming the database, click Finish.
A message confirms that you created the database successfully.
Add external data to the Virtual Database.
As stated in Before You Begin, two data sources are used, a spreadsheet and a delimited text file. You are going to merge them into one table. For this step add the spreadsheet to the table, and then fine-tune some of its fields.
There are seven substeps that must be completed to add an external table.
From the NetBeans IDE tool bar, select Tools->Virtual Database->Add External Tables.
In the Add External Tables/Choose Virtual Database window, ensure that the appropriate database from the list of available databases is highlighted before continuing.
In the example, VirtualMashupDB_1222123456789, which is the database you just created. The underscore–numerical extension has been automatically added to the database name. The extension guarantees that you cannot create two databases with the same name, as no two extensions are the same.
In the Add External Tables/Choose Data source window, navigate to the directory that contains the spreadsheet, select it, and click Add.
In this procedure use company-data.xls. The file now appears in two locations under the Choose Data source pane: File name and Selected Table Source. You can remove it by highlighting the file name in the Selected Table Source and clicking Remove. When you are satisfied that your selection is correct, continue with the procedure.
Although you are selecting a file that is located in a local directory, you can also select data located on the web by entering the URL.
In the Add External Tables/Enter Table Details window, confirm that the table name, encoding, table type, and resource URL are correct.
The resource URL is the directory path and file name of the spreadsheet you selected.
In the Add External Tables/Choose a Sheet window, click Preview to view the spreadsheet you added to the database.
In the example, you used company-data.xls, which is Sheet1. If this sheet is not the file you want to work with, select another sheet.
After building the table you can change the URL if desired.
For this creation of a Data Mashup project, skip step 5, Choose a (HTML) Table.
In the Add External Tables/Import Table MetaData window, set how you want to parse the file.
If the data in the preview does not contain column names, and in the example it does not, deselect the First line contains field names? check box. This is the case for the .xls file you just added, as well as for .csv files, and you must deselect this check box prior to setting the FIELD names.
If a data file does not exist, you can select to create one.
In the Add External Tables/Enter Column Properties window, double-click on a column field to edit it.
The # symbol represents the numerical order of the columns, and in the example FIELD_1 is the name of the first column. In the example, you are going to change the column names as it makes it easier to know what data is in which column when you merge the spreadsheet and the text file fields together.
Change the column names to names that more appropriately represent the values. Moving forward, change the following spreadsheet column names:
PRODUCT_IDENTIFIER
PRODUCT
VENDOR
VENDOR_CODE
To populate the Preview Table Content columns, click Back once, and then click Next.
When you are satisfied that the table represented in the Preview Table Content in the lower portion of the Enter Column Properties window is correct, click Finish.
A message confirms that you created the table successfully. You are now ready to repeat the process and add the text file listed in Before You Begin to the database.
After making your changes, press Enter to see the changes reflected in the Preview Table Content in the lower portion of the Enter Column Properties window.
Add external data to the Virtual Database.
As stated in Before You Begin, two data sources are used, a spreadsheet and a delimited text file. You are going to merge them into one table. For this step add the delimited text file to the table, and then fine-tune some of the fields.
From the NetBeans IDE tool bar select Tools->Virtual Database->Add External Tables.
In the Add External Tables window, ensure that the appropriate database from the list of available databases is highlighted before continuing.
In the example, VirtualMashupDB_1222123456789, which is the database you created. The underscore–numerical extension has been automatically added to the database name. The extension guarantees that you cannot create two databases with the same name, as no two extensions are the same.
Navigate to the directory that contains the delimited text file, select it, and click Add.
The file now appears in two locations under the Choose Data source pane: File name and Selected Table Source. You can remove the file by highlighting the file name in the Selected Table Source and clicking Remove. When you are satisfied that your selection is correct, continue with the procedure.
Although you are selecting a file that is located in a local directory, you can also select data on the web by entering the URL.
In the Add External Tables/Enter Table Details window, confirm that the table name, encoding, table type, and resource URL are correct.
For a delimited text file, which you are using in this example, the table type should be Delimited Flatfile.
The resource URL in the example is the directory and file name of the delimited text file you selected.
In the Add External Tables/Import Table MetaData window, set how you want to parse the file.
Notice that your choices have changed considerably from what was available with the spreadsheet.
Use the drop-down boxes to make your changes.
In the example the pipe represents the delimiter, so change the field delimiter from a comma (,) to a pipe (|).
If your delimited data does not contain column names as is true for the example, deselect the First line contains field names? check box.
Review the text file to ensure that you are satisfied with your selections.
If a data file does not exist, you can select to create one.
In the Add External Tables/Enter Column Properties window, edit the Column Definitions if desired.
Notice that you now have column names that match the column names in the spreadsheet before you changed them, such as FIELD_1, FIELD_2, and so on. If you changed the column names for the spreadsheet, and you did, you must also change the column names for the delimited text file. If there are columns that are similar between the two data files, the names you use for the related columns in the delimited data file must match the names you set in the spreadsheet.
Change the column names to names that more appropriately represent the column values. Moving forward, make the following changes to the delimited text file FIELD names:
VENDOR_CODE
VENDOR
VENDOR_ADDRESS
To populate the Preview Table Content columns, click Back once, and then click Next.
Click Finish when ready.
A message confirms that you created the table successfully. You are now ready to verify that your Data Mashup database has the correct tables.
Verify that the database has the correct tables in the NetBeans IDE.
Under the Services tab expand the Databases node.
Right–click the database you created and select Connect.
You are now connected to the database you created.
Expand the database, expand the Tables node, right click one of the tables you are using, and select View Data.
Repeat this step to view the other table. The tables used in this procedure are COMPANY_DATA and SUPPLIER_ADDRESS.
Create a Data Mashup Project Using the NetBeans IDE.
From the NetBeans IDE tool bar select File->New Project.
In the New Project window do the following in the Choose Project pane.
Select SOA
Select Data Mashup Module
In the New Business Process Application window, name your Data Mashup project DemoDMProject, retain or change the location where you want to create the project, and click Finish.
The project is created and appears as an active project under the Projects tab.
Create a new EDM.
Under the NetBeans IDE Projects tab, right–click the DemoDMProject project you just created, and select New->EDM.
In the New File window under the Name and Location pane type a name for the Data Mashup file and click Finish.
For this exercise, name the file demoDMfile; the program adds the .edm extension.
The Collaborations directory has already been created and is in the path, which is listed in the Created File field.
The file name you created under the Data Mashup application package (in the example, demoDMfile.edm) appears in the Project tree, the EDM Editor opens in a new blank canvas, and the Tool Operators palette appears.
You are now ready to configure your Data Mashup project. For specific instructions on how to do this see Configuring Data Mashup Projects Using Joins.