Steps: Set Up JDBC Database Data Sources

Adaptive Planning

You can use

Adaptive Planning

Integration to extract information from JDBC-compliant databases. Most databases used for accounting and finance applications are JDBC-compliant (Java database connectivity) databases. JDBC is a standard that uses the Java programming language for providing connectivity to a wide range of databases.

A JDBC-compliant database data source requires a driver. The table below gives information on common JDBC drivers.

Adaptive Planning data agents only connect to on-premises databases. Connecting to cloud databases with an agent isn't supported.

JDBC Source	Overview	Provider jar	Driver Class	JDBC/URL example	JDBC URL info
Oracle	Introduction to theOracle Database	ojdbc6-11.2.0.3.jar You can obtain this file from the Oracle website or through Oracle's Maven repository.	oracle.jdbc.OracleDriver	jdbc:oracle:thin:@example-win-db:1521:ANACONDA	Class OracleDriver
SQL Server	Microsoft SQL Server	jtds-1.3.1.jar distributed within the Data Agent Plugins folder. see Integrated Security forJDBC Data Sources for dll location details	net.sourceforge.jtds.jdbc.Driver	jdbc:jtds:sqlserver://127.0.0.1:1433/AGENT_TEST; domain=ADAPTIVE;instance=SQLEXPRESS	jTDS formats
SQL Server	Microsoft SQL Server	sqljdbc41.jar distributed within the Data Agent Plugins folder. see Integrated Security forJDBC Data Sources for dll location details	com.microsoft.sqlserver.jdbc.SQLServerDriver	jdbc:sqlserver://localhost:1433;databaseName=AdventureWorks;user=MyUserName;password=*****;	Building the Connection URL
MySql	MySQL Reference Manuals	mysql-connector-5.1.17.jar	com.mysql.jdbc.Driver	jdbc:mysql://localhost:3306/AGENT_TEST	URL Syntax
h2	H2 Database Engine	Look for h2*.jar in the Workday Adaptive Planning Data Agent Service Manager distribution. For data agent versions 7.1.44 and older, look for h2.jar in the Adaptive Data Agent Service Manager distribution.	org.h2.Driver	jdbc:h2:/data/test	H2 Cheat Sheet

The jar files reside in the

C:/Program Files/Workday Adaptive Planning Data Agent/Plugins.

For data agent versions 7.1.44 and older, use

C:/Program Files/Adaptive Data Agent/Plugins

directory on the machine hosting the Data Agent Service.

After you add a jar file, you need to go into the local services and restart the Data Agent Service.

Before you can set up a data source from a local database, you need to have a Data Agent created that tells

Adaptive Planning

Integration how to communicate with the database. For information on how to create data agents, see Create Data Agents.

To set up a JDBC database data source, follow these steps:

Access the Data Designer by going to Integration > Data Designer
.

In the Data Source folder in the Component Library on the right side of the screen, click Create New Data Source
. The Create New dialog box appears.

Select JDBC database as the data source type, enter the database's name, and select the agent you want to use for this data source.

Click Create
. The center area of the screen displays your new data source's settings and other information.

Enter the data source's information:

Associated Agent:

Select the Adaptive Planning data agent you want to connect to. This list contains all of the data agents found in

Component Library

Data Agents

Driver class:
Enter the driver class for the database. (You can obtain this information from the database vendor.)

URL:
Enter the URL of the database.

Username:
Enter the username used for accessing the database.

Password:
Enter the password associated with the username.

Schema Filter:
Enter the name of the schema(s) from which you would like to import data. If you are entering the names of more than one schema, enter the schema names separated by a semicolon. Use this field to restrict the number of tables that need to be refreshed in

Adaptive Planning

Integration. This field is optional.

Log level:
Select a log level from the drop-down list to specify the detail for the logging for this data source. Your options are:

Error:
Only logs serious errors.

Info:
Logs all basic information, such as when the data source was updated.

Verbose:
Provides very detailed information about all phases and actions. (This level is used primarily for debugging or auditing, as it may produce more log information than is practical for typical use.)

Click Save
on the Actions menu.

You can test the connection information in the new data source by clicking Test connection
in the Actions
menu. The system tries to connect to the database using the information in the data source fields. If the system can connect to the database, click OK
. If there are connection problems, fix the errors and try again.

When you can connect to the database, you're ready to import the data structure. This shows you the tables and columns in the database.

Only columns for supported types appear. Supported Column Types, lists the supported column types for the various types of databases.

To import the JDBC database's structure, do the following:

Click Import structure
in the Actions
menu. The tables and columns appear in the Data Components
menu.

The data import modes affect the way in which the data is synchronized. The default import mode (all records replaced) for a JDBC data source is as follows:

The JDBC data imports.

Data from JDBC source populates staging table.

If there is a custom SQL filter, it applies to the data to importing from the database server to determine the subset of data to import.

You can also choose to append imported information, in which case imported data is appended to the existing staging table, or merge imported information, in which case existing rows in the JDBC data are merged with the rows in the staging area.

Adding Tables to a JDBC Data Source

After you have imported the database's structure, you are ready to specify the information you want to import from the database. The Tables to Import
section lets you configure the data imported from the database.

To specify the tables to import from the database, follow these steps:

Expand the entry for the data source in the Data Components
menu.

Each table in the database appears as a separate item.

Drag and drop the tables you want to import to the Tables to Import
section in the center of the screen.

As you drag each table to the Tables to Import section, a tab appears for the table and information from the database appears in the Tables to Import section letting you see the type of information you've added. Each table's tab also has a drop-down menu, which you can access by clicking the arrow next to its name.

Managing Columns

If a table you are working with has a large number of columns, you can use Manage Columns
to do quick edits for which columns to import. The Manage Columns
dialog box displays the columns in a table so you can then check or uncheck columns for importing.

To manage columns for importing, follow these steps:

Click the arrow on the tab to the right of the table name.

Click Manage Columns
on the floating menu.

Check the box next to a column to import it.

When you check a column, the column also appears within the preview window after the popup contents are saved. Unchecking a column removes the column from the preview window.

You can also change the import status on individual columns by:

Selecting column properties and setting the property.

Dragging and dropping the columns onto the designer.

You can't change the import status on system-generated fields such as isDeleted
.

Customizing the Tables

For each table, you can customize the way in which data is imported.

To customize the table settings, follow these steps:

Click the arrow on the tab to the right of the table name.

Click Table Settings
on the floating menu.

Enter the table settings:

Data Import Mode:
Select one of the options from the drop-down list.

All records replaced each time a data import is run
This the default. Replaces the data in the staging table with data imported from the source.

Remote cache used to identify and send changed records on import
Only database records that have changed or are new are imported. Changed records overwrite existing records in the staging area. New records are added to the end of the information in the staging area.

All records that fall within a period range transmitted
All of the records in the period range you specify are imported. Records in the staging table that match the imported periods get deleted before importing the latest data from the source.

Data Import Filter:
Use this to enter a SQL expression that filters or formats information being imported. The SQL filter is applied before data is extracted from the database to reduce the amount of data that needs to be imported, giving you an additional level of control for which data is brought into the staging area. Click the field to display the Edit SQL Filter
dialog box (the filter you enter here is applied to the actual data in the database rather than the preview data in the staging area). Not all data sources support the same level of SQL filtering capability.

Click Apply
to apply the settings.

Repeat steps 1-3 for each table.

If a database table has keys defined, this information is brought into the staging table as well. You can review this information and modify as needed.

Click Save
to save the settings in the data source.

Setting Column Options

While using the Tables to Import
section, you can see a preview of the data as it is in the data source or as it is in the staging area after it has been imported. You can change the options for each column by mouse hovering its heading and then clicking the drop-down menu next to its name. These options are:

Sort Ascending
: Sort the entire table based on this column in ascending order.

Sort Descending
: Sort the entire table based on this column in descending order.

Column Settings
: This dialog is used to change the properties of the column within the staging table.

Name
: The name of the column, displayed in the header.

Column Type
: The type of data used in the column.

Convert Column Type
: Select the new type from this drop-down menu.

Exclude column from import
: Select this to remove this column from the data being imported.

Delete custom column
: Select this to delete the column. (Available for custom columns only.)

Downloading Data from a Staging Table

You can download data from a single staging table.

To download data from a single staging table, follow these steps:

Click the down arrow in a table heading in the Tables to Import section.

Select Download Data
.

Click Send
at the prompt This operation will send an email with a url to download the data
.

Adaptive Planning

Integration sends an email containing a URL to the email account associated with the data designer.

Open the email and click the URL.

The data downloads in .csv format.

Using the Advanced Filter

Below the Source
drop-down menu, you can click Advanced Filter
to reveal options for filtering the data in the Tables to Import
preview area. The Advanced Filter
provides tools to view and browse a subset of the data within a staging table or directly from the remote source (if you select JDBC Database from the Source drop-down menu). By changing filter properties, you can change the view of the data shown within the preview window without changing the data in the staging area.

The preview area is specifically for previewing a small portion of the data in the staging area (or JDBC database). You can explore the data that is available from the JDBC Data source and verify that you are getting the data you expect by examining the staging area. You can also use this to refine the data import filter you want to use. (Filters set in the preview function do not affect the actual data in the staging area.)

Advanced Filter
include:

Distinct Rows
: Check this box to hide duplicate rows.

Max Rows
: Limit the number of rows displayed in the preview area by entering a number in the text box.

Columns
: Check or uncheck columns in this drop-down menu to show or hide the columns displayed.

Removing a column from this list does not remove the column from the import list.

SQL Filter
: Click the large text box to display the Edit SQL Filter
dialog box, where you can enter a SQL filter to restrict the rows being previewed. Click Apply
to automatically check the SQL syntax of the filter. (If there are any errors, the error is indicated in the expression.) The expression you entered appears in the Advanced Filter
field. For detailed SQL syntax help, you can click online help in the Notes
section.

Click Remove Filter
(next to the Source drop-down menu) to clear all advanced filter settings.

As part of the table customization process, you can also create join tables and custom columns using SQL expressions.

Importing Data from a JDBC Database

When you have set up and saved your JDBC data source, you can import data.

To import data from a JDBC database, do the following:

Click Import data
in the Actions
menu.

The selected data is filtered by any SQL expressions and loaded into the staging area. You can preview the data in the staging area.

Information remains in the staging area until you clear it manually, or until a subsequent data load clears it in accordance with the import mode.

To clear data from the staging area, do the following:

Click Clear staging tables
in the Actions
menu. The staging area for all the tables in the data source is cleared.

To remove specified characters from Input Text Streams in JDBC data sources:

Refer to Remove Specified Characters from Input Text Streams in JDBC and Microsoft Dynamics GP

Associate a JDBC Data Source with a Different Adaptive Planning Data Agent

You can change what Adaptive Planning data agent you associate with your JDBC data source.

Verify that the data agent you want to connect to is listed in

Component Library

Data Agents

Navigate to the JDBC Data Source in Design Integrations.

Select the data agent in the

Associated Agent

field in the

Settings

section.

JDBC Data Source Excluded Schemas

JDBC data sources that connect to Oracle or MS SQL Server explicitly exclude some schemas that do not relate to financial data.

Oracle JDBC Data Source Excluded Schemas

CTXSYS, MDSYS, SYSTEM, SYS, XDB

MS SQL Server JDBC Data Source Excluded Schemas

INFORMATION_SCHEMA, SYS

Troubleshooting Tips

There are a few data source problems you may run into that have solutions that are not immediately apparent.

When you import the structure for a database and the database has more than 1000 tables, you get an error advising you to filter the table list. You can get past this error by doing either of the following:

Editing the JDBC data source and entering a schema filter.

Changing the permissions associated with the login you're using to access the server so fewer tables are accessible through that login.

When you import the structure of an Oracle database, the following schemas are not imported as part of the structure:

CTXSYS, MDSYS, SYSTEM, SYS,

and

XDB

. These are Oracle systems schemas and are not necessary for data integration purposes.

When you import the structure of an MS SQL database, the following schemas are not imported as part of the structure: INFORMATION_SCHEMA and sys. These are MS SQL systems schemas and are not necessary for data integration purposes.