test

Using the Sun Data Mashup Engine

Designing Data Mashup Projects

The following procedure leads you through the necessary steps to design a Data Mashup project, and includes:

Note –

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.

ProcedureTo Design a Data Mashup Project

Before You Begin

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.

Note –

To move forward or backward in the procedure, click Next or Back.

  1. Create the Data Mashup Database.

    1. From the NetBeans IDE tool bar select Tools->Virtual Database->Create Virtual Database.

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

    3. After naming the database, click Finish.

      A message confirms that you created the database successfully.

  2. Add external data to the Virtual Database.

    Note –

    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.

    1. From the NetBeans IDE tool bar, select Tools->Virtual Database->Add External Tables.

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

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

      Note –

      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.

    4. In the Add External Tables/Enter Table Details window, confirm that the table name, encoding, table type, and resource URL are correct.

      Note –

      The resource URL is the directory path and file name of the spreadsheet you selected.

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

      Note –

      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.

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

      Note –

      If a data file does not exist, you can select to create one.

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

      FIELD_1

      PRODUCT_IDENTIFIER

      FIELD_2

      PRODUCT

      FIELD_3

      VENDOR

      FIELD_4

      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.

  3. Add external data to the Virtual Database.

    Note –

    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.

    1. From the NetBeans IDE tool bar select Tools->Virtual Database->Add External Tables.

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

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

      Note –

      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.

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

      Note –

      The resource URL in the example is the directory and file name of the delimited text file you selected.

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

      Note –

      If a data file does not exist, you can select to create one.

    6. 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:

      FIELD_1

      VENDOR_CODE

      FIELD_2

      VENDOR

      FIELD_3

      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.

  4. Verify that the database has the correct tables in the NetBeans IDE.

    1. Under the Services tab expand the Databases node.

    2. Right–click the database you created and select Connect.

      You are now connected to the database you created.

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

  5. Create a Data Mashup Project Using the NetBeans IDE.

    1. From the NetBeans IDE tool bar select File->New Project.

    2. In the New Project window do the following in the Choose Project pane.

      Under Categories

      Select SOA

      Under Projects

      Select Data Mashup Module

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

  6. Create a new EDM.

    1. Under the NetBeans IDE Projects tab, right–click the DemoDMProject project you just created, and select New->EDM.

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

      Note –

      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.

Next Steps

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.

See Also