If an organization already has an existing data warehouse it can be beneficial to simply use the tables that already exist in the legacy database and integrate them into the Jet Data Manager.
Adding an External Table is a way to incorporate existing tables into the Jet Enterprise project. An External Table will initially not be deployed or executed, but will be available for data movement to data warehouses and data marts, can be used in views and scripts and for cubes and dimensions. Later on in the process, the user can add a custom SSIS package to the table, which can then be executed.
Adding an External SQL Connection
To add an external table, you first need to add an external SQL connection. To add an external SQL connection, follow the steps below:
1. Right click on your data warehouse or a business unit, navigate to Advanced and click Add External SQL Connection . The Add External SQL Connection window appears.
2. Type a name for the connection in the Name box.
3. Click on Use Global Database and choose a global database in the list
Click on Use Custom Settings and type and select your settings. Type the name of the server in the Server Name box and enter the database you wish to use in the Database box. Select Force Codepage Conversion to convert all fields to the collation of the data warehouse, Force Unicode Conversion to declare all alphanumeric fields as nvarchar and Allow Dirty Reads to allow reading from the source without locking the table. In Additional Connection Properties you can type additional properties.
4. Click OK to add the source and close the window.
Adding an External Table
To add an external table, follow the steps below.
1. Navigate to External SQL Connections under your data warehouse or business unit in the project tree, right click on the connection you just created and click Read Objects from Data Source .
2. When the Jet Data Manager has finished reading objects from the data source, the source explorer pane in the right hand side of the window is populated with the objects from the source. Select the tables, views and fields you wish to use in you data warehouse.
Working with External Tables
The external tables in your project are shown in the project tree alongside the standard tables and you can use them in the same way. External tables can be used in dimensions and cubes, for reporting, etc. You can recognize an external table in the project tree on the black table icon.
Some of the transformations and data cleansing that can be done in standard tables can be done with external tables as well. The user can add custom fields, but not lookup fields. For instance, a custom field can be added to the external table and apply a transformation to the field to concatenate two other fields on the table.
Deploying an External Table
To deploy an external table, right click on the table and click Deploy . A view will be created that selects from the external table
Executing an External Table with an SSIS Package
Since the underlying table is set up outside of the Jet Data Manager, the JDM expects it to be executed separately from your project. This means that you initially will not find any execute command on an external table. However, if you have a SSIS Package that is used to populate the table, you can add this package to the table and get the ability to execute the table.
1. Right click on an external table, navigate to Advanced and click Customize Code . The Customize Code window appears.
2. Click the Add button to the right of SSIS Package. The Custom Editor window appears.
3. In the Editor Name list, click on you editor of choice and click OK . The Custom SSIS window appears.
4. Make sure Existing Package is selected and click OK. The Pick SSIS Package window appears.
5. Type the server name in the Server box. Optionally, you can select Use SQL Server Authentication and type your credentials in the User Name and Password boxes as appropriate. In the Location list, click File System or SQL Server and then click the ellipsis ( … ) next to the Package Name box to browse for the SSIS package. When you have found the package and clicked Open in the Open window, click OK and the editor of your choice opens.
6. Make any changes you wish to make in the editor, save the package and close the editor.
7. While you edit the SSIS package, the JDM displays the Custom Code Editor dialog. When you return to the JDM, click Import to import the changes you made to the SSIS package.
8. In the Customize Code window you'll notice that the Add command next to SSIS Package has changed to Edit and that you can now click Parameters and Delete as well. Click Close .
9. Right click on the table and choose Execute to run the SSIS package. You can also execute the table by including it in an execution package, executing the entire project etc.