Use External Tables in the Jet Data Manager

Overview

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 Analytics 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 your data warehouse or a business unit, navigate to Advanced and click Add External SQL ConnectionThe Add External SQL Connection window appears.

   

2. Enter a name for the connection in the Name box.

3. Click Use Global Database and choose a global database in the list

   or

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

   Note : The SQL Server needs to be on the same physical SQL Server instance as your data warehouse.

4. Click OK to add the source and close the window.

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

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

Deploy an External Table

To deploy an external table, right click the table and click DeployA view will be created that selects from the external table.

Execute 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 an external table, navigate to Advanced and click Customize CodeThe 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 you editor of choice and click OKThe 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 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.