Index Changes

Using the Enterprise Data Mashup Service Engine


Contents

Designing Data Mashup Projects

The following exercise leads you through the steps of designing a Data Mashup project, and includes the following steps:

  • 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

For this exercise you will create a federated join using two data files, a Microsoft Excel spreadsheet (company-data.xls) and a delimited text file (supplier-address.txt). Ensure that you have the data files accurately set up before beginning the procedure.

Perform the following steps in the order given to develop the Data Mashup project:



To Create the External Data Files

You can either create the spreadsheet and delimited text file or you can download the files 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 for this exercise.

  • Create an Excel spreadsheet named company-data.xls that contains the following information:
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 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
  • Create a delimited text file named supplier-address.txt that contains the following information:
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


To Create the Data Mashup Database

Before You Begin
You must have the above-mentioned files ready and stored on your computer, and have started the NetBeans IDE.
  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.
    For this exercise, name the database VirtualMashupDB.
  3. After naming the database, click Finish.
  4. Click OK on the configuration message that appears.
    You are now ready to start adding external data to the virtual database you just created.


To Add External Data to the Virtual Database (Excel Spreadsheet)

Before You Begin
Make sure you have the two data sources, a spreadsheet and a delimited text file. These will be merged into one table. For this step add the spreadsheet to the table, and then fine-tune some of its fields.
  1. From the NetBeans IDE tool bar, select Tools→Virtual Database→Add External Tables.
  2. In the Choose Virtual Database window, select the appropriate database from the list of available databases.
    For this exercise, select VirtualMashupDB, which is the database you just created.
  3. In the File field on the Choose Data source window, navigate to the directory that contains the spreadsheet and double-click the file.
    For this exercise, select company-data.xls. The file now appears in the Selected Table Source list. You can remove it by highlighting the file name in the Selected Table Source and clicking Remove.
    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. Click Next.
  5. In the 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.
  6. Click Next.
  7. In the Choose a Sheet window, select the sheet containing the data to use, and then click Preview to view the information.
    In the example data, the information is in Sheet1.
  8. Click Next.
    Step 5, Choose a (HTML) Table is skipped since no HTML sources were selected. The Import Table MetaData window appears.
  9. In the Import Table MetaData window, define how to parse the file.
    If the data in the preview does not contain column names, and in the sample it does not, deselect the First Line Contains Field Names? check box. This is also the case 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. The available fields on this window vary based on the type of data you are importing into the database.
  10. Click Next.
  11. In the 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. For this example, change the column names to names that more appropriately represent the values. This makes it easier to know what data is in which column when you merge the spreadsheet and the text file fields together. Use the following column names:
    • FIELD_1 - PRODUCT_IDENTIFIER
    • FIELD_2 - PRODUCT
    • FIELD_3 - VENDOR
    • FIELD_4 - VENDOR_CODE
  12. To populate the Preview Table Content columns, click Back once, and then click Next.
  13. 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 to the database.


To Add External Data to the Virtual Database (Delimited Text File)

Before You Begin
Make sure you have the two data sources, a spreadsheet and a delimited text file. For this step, you will 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, select the appropriate database from the list of available databases.
    In this exercise, VirtualMashupDB is the database you created.
  3. Click Next.
  4. In the File field on the Choose Data Source window, navigate to the directory that contains the delimited text file and then double-click the file name.
    The file now appears in the Selected Table Source list. You can remove the file by highlighting the file name in the Selected Table Source and clicking Remove.
    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.
  5. Click Next.
  6. In the 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 is the directory and file name of the delimited text file you selected.
  7. Click Next.
  8. In the Import Table MetaData window, define how to parse the file.
    Note - The available fields on this window vary based on the type of data you are importing into the database.
    • 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 in our sample file, deselect the First Line Contains Field Names? check box.
    • Review the text file in the Preview panel to ensure the selections are correct.
  9. Click Next.
  10. In the Add External Tables/Enter Column Properties window, edit the Column Definitions if desired.
    In this exercise, 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 used in the spreadsheet. Change the column names to names that more appropriately represent the column values. For this exercise, use the following values:
    • FIELD_1 - VENDOR_CODE
    • FIELD_2 - VENDOR
    • FIELD_3 - VENDOR_ADDRESS
  11. To populate the Preview Table Content columns, click Back once, and then click Next.
  12. 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.


To Verify the Virtual Database Tables

  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.


To Create the Data Mashup Project

Once the virtual database is populated, you can create the Data Mashup project. This project will include an EDM collaboration that defines how the data is mashed up into one table.

  1. From the NetBeans IDE tool bar select File→New Project.
    The New Project Wizard appears.
  2. In the Choose Project window, select SOA under Categories, and select Data Mashup Module under Projects.
  3. Click Next.
  4. In the New Business Process Application window, name the Data Mashup project DemoDMProject and enter the location where you want to store the project files.
  5. Click Finish.
    The project is created and appears as an active project under the Projects tab.


To Create the EDM Collaboration

  1. In the NetBeans IDE Projects panel, right–click the DemoDMProject project you just created, and select New→EDM.
    The New File Wizard appears.
  2. On the Name and Location window, 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.
  3. Click Finish.
    The EDM Editor opens in a new blank canvas and the Tool Operators palette appears.
Tip - You could click Next instead to add the virtual database tables to the collaboration. This will be done in a later step for this exercise.

Next Steps

You are now ready to configure your Data Mashup project. For instructions on how to do this see Configuring Data Mashup Projects Using Joins.

Previous Next


Return to GlassFish ESB Documentation Home

JSPWiki v2.4.100
[RSS]
« Home Index Changes Prefs
This page (revision-76) was last changed on 16-Jun-09 14:56 PM, -0700 by CarolT