Applies to :
- Denodo 8.0
Last modified on:
05 May 2021
Download: Document

You can translate the document:

Goal

This document describes how to connect to the Denodo Platform from SAP PowerDesigner Tool.

Introduction to SAP PowerDesigner

SAP PowerDesigner (PowerDesigner) is a collaborative enterprise modelling tool. It supports model-driven architecture software design, and stores models using a variety of file extensions, such as .bpm, .cdm and .pdm. The internal file structure can be either XML or a compressed binary file format. It can also store models in a database repository.

PowerDesigner allows you to define data connections to access information in different database management systems using SQL:

JDBC connection profile
Reverse Engineering from an ODBC Data Source.
ODBC machine or file Data Source.

Connecting to Denodo using JDBC

In SAP PowerDesigner, before connecting to any database through a JDBC driver, it is mandatory to create an appropriate connection profile.

To create a connection profile, you can perform the following steps:

Go to Database > Configure connections and navigate to the Connection Profiles tab.

Click on the Add data source option to specify the connection details.

Set the name for the connection profile and specify the directory in which the .dcp connection file will be created. By default .dcp files are stored in the Connection Profiles directory directly beneath the PowerDesigner installation directory.

Select the type of connection profile as JDBC and the DBMS type as Other.

For connecting to Denodo Platform using the JDBC Driver, you can use the below configuration details:

JDBC driver class - com.denodo.vdp.jdbc.Driver
JDBC Connection URL - jdbc:vdb://<host>:<port>/<database_name>
JDBC driver jar files - <DENODO_HOME>/tools/client-drivers/jdbc/denodo-vdp-jdbcdriver.jar

Finally, you can click on the Test connection button and enter the credentials for connecting to Denodo Platform. This will show whether the connection is established successfully.

Once the connection profile is created, it will be displayed in the connection profiles list.

Now, to connect to this connection profile, you can go to the Database menu and click on the Connect option. Select the created data source by choosing the Connection Profile option as shown below.

After the connection is established, you can execute the queries using the Database > Execute SQL option.

Connecting to Denodo using ODBC

SAP PowerDesigner can reverse engineer a Physical Data Model(PDM) for one or more SQL script files. The script will normally be the script used to generate the database but can also include other scripts. PowerDesigner can reverse engineer a PDM from a live database connection. Additionally, PowerDesigner has the capability to connect to the Denodo Platform using the ODBC Machine or File data source.

Reverse Engineering from an ODBC Data Source

For performing reverse engineering, it is necessary to define a data source and connection information.

First, to create an ODBC DSN for connecting to the Denodo Platform, follow the steps in the “Access through ODBC” section of the Virtual DataPort Developer Guide. Once the DSN is configured, Denodo Platform can be connected from SAP PowerDesigner Tool using the ODBC access method.

Launch the SAP PowerDesigner Tool and navigate to “File > Reverse Engineer > Database”

In the General tab, select ODBC 3.0 as DBMS.

In the Selection tab, choose the option Using a data source and select the created DSN (Denodo8.0).

Click OK. On doing this, all the views in the Denodo Virtual DataPort databases along with their Primary keys, Foreign keys, Indexes etc are introspected.

Select the views with necessary attributes to create the model.

Connecting using the ODBC Machine or File Data Source

It is possible to connect to ODBC using either a File or Machine data source. ODBC Machine data source is created on the client machine (stored in the current user settings in the registry), and is only available to the user currently logged onto the system, whereas, ODBC File data source is stored as a file with the extension .dsn, which can be used by users if it is placed in the default location for file data sources. These File data sources are usually managed by database administrators.

To connect using ODBC Machine Data source, the following steps can be utilized:

In SAP PowerDesigner, we can create a new data model in SAP designer tool by navigating to the File > New model option. Here, we can choose the Model Type as Physical Data Model and Diagram as Physical Diagram.

After doing so, set a name for the Model that is going to be built. Under the DBMS section, it is necessary to choose the method in which the connection will be established. Here, specify ODBC 3.0 as DBMS .

Now, as the model is successfully built. Navigate to the option “Database > Configure Connections” to configure the connection details.

From the dialog box that appears, click on the Add data source icon to select the ODBC data source.

In the new data source pop up box, choose the User Data Source option and select the DenodoODBC Unicode driver.

Then, click on Finish and configure the new DSN which indicates the details on the Denodo Platform to be connected with.

As shown in the image, configure the details and click on Test the connection. If the connection is established, the connection successful message appears.

Clicking on Ok, the newly created ODBC data source will be displayed in the ODBC Machine Data Sources section.

As the data source is created, lets connect to it. For establishing the connection from SAP PowerDesigner, navigate to the Database Menu and choose the Connect option.

From the ODBC Machine Data Sources, select the created Data source which is Denodo8.0 (DenodoODBC Unicode). Provide the necessary login information to connect to Denodo Platform.