test

Using the Sun Data Mashup Engine

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