Getting Started with Data Sources

Data sources identify data that the DSP® can access. This data can be held in an application such as SAP ERP or Salesforce, a database such as SQL Server or Oracle, or a file such as an Excel spreadsheet or a delimited text file.

Once a data source is configured, the diverse set of tools within the DSP rely on it to read data from or post data to an application, a database, or a file. For example, a legacy Oracle database registered as a data source can be used by Collect to create a local copy, which dspMigrate™ then transforms and posts to a different data source (such as an SAP ERP application).

While it is possible to register data sources in System Administration, it is recommended that all compatible data sources be registered in Common. File data source types (Local File, Remote File (FTP), Remote File (HTTP) or Remote File (UNC) must be registered in System Administration. Database data sources that are registered in System Administration are available in Common on the Data Source Registry page, but file data sources will not sync to Common. Changes in System Administration are not synced to Common (even for database data sources). Only the initial creation of a database data source is synced.

Application data sources such as Data Services data sources must be registered in Common, while File data source types must be registered in System Administration.

This article includes:

Types of Data Sources
How DSP Applications Use Different Data Source Types
Sync Data Sources in Common and System Administration
Register a Database for a Custom WebApp

Types of Data Sources

Data Sources Registered in Common

Application Data Sources

Data sources of this type must be registered in Common.

Data Services Repository—A specific repository within an SAP Data Services application. Data Services repositories can be hosted anywhere.
IG Universal Connect—A Dell Boomi application hosted on the DSP server.
SAP Application Server—An SAP ERP application hosted anywhere.

Data Sources Registered in System Administration

File Data Sources

File data source types cannot be registered in Common; they must be registered in System Administration.

Local File—Points to a folder on the web server. Used for importing or exporting data to flat files: Excel, CSV, etc.
Remote File (FTP)—Similar to local file, but located on another machine and accessed through the File Transfer Protocol.
Remote File (HTTP)—Similar to local file, but located on another machine and accessed through the Hypertext Transfer Protocol.
Remote File (UNC)—Similar to local file, but located on another machine and accessed through the Uniform Naming Convention.

Database and Connection Data Sources

These data sources should be registered in Common whenever possible. Database and connection data sources registered in System Administration are generally databases other than Oracle and Microsoft SQL Server, if the ODBC driver is available.

External System Connection—System connections for:

ODBC—Generally for databases other than Oracle and Microsoft SQL Server, if the ODBC driver is available.
OleDB—Generally for databases other than Oracle and Microsoft SQL Server, if the OleDBdriver is available.
Oracle—Used for configuring connections to Oracle databases that use native connection drivers.

Local Utility Database—A SQL Server database hosted on the DSP database server
Migration Object Database—A SQL Server database hosted on the DSP database server. BOA methodology states the db name must begin with 'dsw' and the database contains SQL objects for executing data transformation in a migration scenario.
Migration Source Database—A SQL Server database hosted on the DSP database server. BOA methodology states the db name must begin with 'sdb' and the database contains an image of a customer's legacy database in a migration scenario.
Target System Database—SQL Server database hosted on the DSP database server. BOA methodology states the db name must begin with 'dg' and the database contains an image of a customer's database. In a migration scenario, this is either a production database or one that mirrors production (for practice loads). In a governance/quality scenario, this is always a production database.

How DSP Applications Use Different Data Source Types

Data Source Type	Use in DSP
Data Services Repository	Used by dspMigrate and Collect to execute batch jobs via SAP's Data Services application.
SAP Application Server	Used by: Collect to populate 'sdb' and 'dg' databases via RFC. Integrate to post data to SAP's ERP application.
IGUC	Used by: Collect to populate 'sdb' and 'dg' databases from any application Boomi can connect to. Integrate to post data to any application Boomi can connect to. Any custom DSP WebApp.
ODBC	Used by Collect or Assemble to populate 'sdb' and 'dg' databases.
OleDB	Used by Collect or Assemble to populate 'sdb' and 'dg' databases.
Oracle	Used by Collect or Assemble to populate 'sdb' and 'dg' databases.
Migration Source Database	Used heavily by dspMigrate. Populated by Collect or Assemble.
Migration Object Database	Used heavily by dspMigrate, especially Transform. Commonly used by Integrate and dspMonitor.
Target System Database	Used heavily by dspMigrate, dspCompose, & dspConduct. Populated by Collect or Assemble.
Local Utility Database	Typically used to hold SQL objects for the development/operation of a custom DSP Webapp.
Local File	Used by: Collect, Common, dspConduct, dspMonitor, Integrate, Map, & Transform to write files to the DSP application server. Assemble to read from or write to a file.
Remote File (HTTP)	Used by Assemble to read from or write to a file.
Remote File (FTP)	Used by Assemble to read from or write to a file.
Remote File (UNC)	Used by Assemble to read from or write to a file.

Sync Data Sources in Common and System Administration

When an Administrator registers a data source in System Administration, it is available in Common on the Data Source Registry page. However, after the initial record is added to Common, changes made to data sources in System Administration are not applied to data sources in Common. Changes made to data sources in Common are reflected in System Administration when the data source record is saved or validated in Common.

On the Data Source Registry page in Common, a data source that has been updated in System Administration displays the Out of Sync icon in the OUT OF SYNC column. To view the updates in System Administration, click the Admin Data Source icon.

NOTE: You must have access to the Data Sources page in System Administration to view these updates.

You can either:

Apply the updates made to the data source in Common to the data source in System Administration by validating or saving the record in Common.
Apply the updates made to the data source in System Administration to the data source in Common manually.

To update the data source in Common with changes made to the data source in System Administration, apply the changes manually on the Data Source Registry page.

Data Source Type

Common Syncs to Admin

Admin Syncs to Common

Data Services Repository	Must be registered in Common	N/A. This data source cannot be registered in Admin
SAP Application Server	Must be registered in Common	N/A. This data source cannot be registered in Admin
IGUC	Must be registered in Common	N/A. This data source cannot be registered in Admin
ODBC	When you validate/save	When you first save
OleDB	When you validate/save	When you first save
Oracle	When you validate/save	When you first save
Migration Object Database	When you validate/save	When you first save
Target System Database	When you validate/save	When you first save
Local Utility Database	When you validate/save	When you first save
Local File	Does not appear in the Data Source Registry	Must register in Admin
Remote File (HTTP)	Does not appear in the Data Source Registry	Must register in Admin
Remote File (FTP)	Does not appear in the Data Source Registry	Must register in Admin
Remote File (UNC)	Does not appear in the Data Source Registry	Must register in Admin

Register a Database for a Custom WebApp

The database must be registered as a data source in DSP to create a WebApp. In most cases, the System Administrator registers the database.

The database contains the tables and views needed to design the WebApp. Additional views are automatically added from the DSP after the data source is added and the WebApp is created and validated. These views pull in BOA views from the CranSoft database. This is beneficial for development when a user may not have access to the CranSoft database or if the WebApp database is on a different server than the CranSoft database.

To register the database:

Click Admin > Data Sources in the Navigation pane.
Click Add.
Enter a unique name in the DATA SOURCE NAME text box.
NOTE: The Data Source Name is a logical name and may contain any alpha/numeric character, including spaces.
Click Save, the Vertical View displays.
Enter the server address where the data source is stored in the Server Address combo box.
Enter the database name in the Database text box.
NOTE: The database name must match the actual name of the database located on the SQL Server.
Enter the user account to access the database in the User ID text box.
Enter the password to access the database in Password text box.
NOTE: The Security or System Administrator may have to establish the User ID and Password to the database.
Click Save, a message displays.
NOTE: An informational message displays indicating some views need to interact with other databases, such as CranSoft, on the server and need to be enabled. This enables views necessary to build the WebApp to populate the data source.
Click OK.
Click Advanced Properties.
Click System Views check box to enable.
NOTE: Having System Views checked will automatically install all BOA views into the data source. They are useful for applications that need to select data from the CranSoft database.
Click Test Connection on the Page toolbar; a confirmation message
NOTE: If the connection is not made, verify the database name and login information are correct and try again.
Click Append Columns on the Page toolbar, a confirmation message
NOTE: Refer to the Append BOA Reserved Columns to Tables section below for more information.
Click OK.

NOTE: Use the Recompile Objects icon on the Page toolbar to recompile every object in a database to ensure they are still valid, if needed. A list of object names is returned if they fail to compile. The failed objects need to be manually fixed.

Append BOA Reserved Columns to Tables

BOA reserved columns are:

boaStatus
AddedOn
AddedVia
ChangedBy
ChangedOn
ChangedVia
Locked On
Locked By

The first six columns above must be added to every table.

When the user clicks the Append Columns icon on the Page toolbar of the Data Sources page’s Vertical View, most of these reserved columns are appended to all of the tables that do not already have them in the database. A Designer can also add these columns manually.

NOTE: Locked On and Locked By should be added manually where appropriate. Add these columns for any table where multiple users may be editing the same record at the same time.

With the exception of boaStatus, the appended column should not display on the page so should not be added to the view.

NOTE: If the columns exist on the table, they display when a user hovers the mouse cursor over the row edit pencil icon. This allows the developer to exclude the columns from the view, but still take advantage of them.

NOTE: Reserved columns are only automatically added to tables when the Append Columns icon is clicked on the Data Sources Vertical View. Columns are not automatically appended to tables added after this operation is performed. In other words, if tables are added to the data source later, click the Append Columns icon again to add the reserved columns to the new tables.