This page is for data modellers who want to connect to an external database, either to load data or to export processed data back to it.

Overview

A datasource is a modelling object whose properties provide the information needed to connect to an external database and transfer data to and from it. You must have one datasource for each type of database to which you need to connect. Optionally, you can configure several datasources for the same type of database In an analysis model, you can add:

a Database Collector to load data from a database.
a Database Exporter to use data from the model to update the database.

The datasources you create appear in the Basic Settings → Datasource list in collector and exporter properties.

Each datasource must have at least one datasource instance. You can add several instances if you want to configure different settings. For example you may want to connect to the same database using a different username and password for different scheduled tasks.

In the properties for collectors and exporters, you can optionally specify the datasource instance using the Advanced → Datasource Instance Expression.

The datasource properties are affected by:

the Privilege Allow anonymous updates on datasources. Only a user with this privilege can change the Allow Anonymous Export tick box.
the System Configuration option Allow anonymous export by default. When selected, this system option sets Allow Anonymous Export to be ticked by default on new datasources.

Panel

borderColor	#7da054
titleColor	white
titleBGColor	#7da054
borderStyle	solid
title	Sections on this page

Table of Contents

indent	12px
style	none

Datasource Properties

Insert excerpt

	_standard_settings
	_standard_settings
nopanel	true

In addition to the standard icons, the properties toolbar includes

Insert excerpt

	_datasource_show_tables
	_datasource_show_tables
nopanel	true

, which opens a list of tables visible for this datasource using the default login details; see the Table Details window.

For a datasource, you must:

Enter a Name.
Select a Driver class.
Add at least one datasource instance.
Tick the Enabled check box when it is ready to use.

We recommend you tick Protect during import to prevent this datasource being updated or deleted during import.

Basic Settings

Field	Description
Name	Enter the name for this datasource

object. You will use this name to specify this datasource in other collector and exporter objectsEnter the URL

. It is helpful to use a name that reflects the type of database to which it will connect. This name appears in the Datasource drop-down list for database collectors and exporters.

Enabled

Insert excerpt

	_check_box_ticked
	_check_box_ticked
nopanel	true

when the configuration is complete and the datasource is ready to be used.

URL INSTANCE

Allow Anonymous Export

When an exporter uses this datasource to connect to

the datasource; see Connection URLs below. This option has gone. How does the datasource know where the database is?Allow Anonymous Export

a database, by default PhixFlow will use the PhixFlow username and password.

Use this option when

a datasource is connected to an exporter that will be run by a task, such as

the PhixFlow username and password should not or cannot be used, for example in a scheduled task plan. The target database must permit anonymous updates.

Insert excerpt

	_check_box_ticked
	_check_box_ticked
nopanel	true

to allow anonymous export of data via the datasource.

The target database must permit anonymous updates. The datasource uses the anonymous

PhixFlow uses the username and password

configured for the specific instance (see below)

specified in the datasource instance; see Datasource Instance Properties, below. You must have the Allow anonymous updates on datasources privilege to change this option.
Use User Credentials on Export	Use this option when a

datasource is connected to an exporter being run interactively

user interactively runs an exporter that uses this datasource to connect to a database. This may be via an application action or when a user clicks

Insert excerpt

	_run_analysis
	_run_analysis
nopanel	true

.

Insert excerpt

	_check_box_ticked
	_check_box_ticked
nopanel	true

to connect to the target database using the PhixFlow username and password of the current user.

Note
The target database must already have the same username and password with the required permissions. Contact the database administrator to do this.

Protect during import

Insert excerpt

	_check_box_ticked
	_check_box_ticked
nopanel	true

to prevent this datasource being updated or deleted during import.

When a

The datasource will be deleted when its parent application is deleted by the import

, its datasources will also be deleted

. This prevents orphaned datasources persisting in PhixFlow.

Driver Class

Required.

Select the driver class for the type of database to which the datasource connects.

Tip
The drop-down list may contain driver classes that are deprecated and do not have the necessary database drivers; see Driver Classes.

Database Instance

Advanced have these gone away? I don't see an Advanced section?

Datasource Instances

This section has a toolbar with standard buttons.

The grid contains a list of datasource instances that belong to this datasource. Double-click on a row to open the datasource instance properties.

To add a datasource to the list, click

Insert excerpt

	_add
	_add
nopanel	true

. PhixFlow opens the properties for a new datasources instance.

To remove a datasource instance from the grid, use the toolbar button

Insert excerpt

	_delete
	_delete
nopanel	true

.

Datasource Instance Properties
Anchor
instance
instance

For a datasource instance, you must:

Enter a Name.
Enter the URL for the database.
Specify the Anonymous username and Password.
Tick the Enabled check box when it is ready to use.

Other settings are optional.

Basic Settings

Field Description

Name

Enter the name for this instance. In the properties for collectors and exporters, you can specify this instance by using its name in the Datasource Instance Expression.

Enabled

Insert excerpt

	_check_box_ticked
	_check_box_ticked
nopanel	true

when the configuration is complete and the datasource is ready to be used.

URL

Enter the URL for the target database.

Example URLs

Oracle
jdbc:oracle:thin:@hostname:1521:phixflow
Oracle for versions above 12c, with PDB containers
jdbc:oracle:thin:@hostname:1521/phixflow
SQL Server
jdbc:sqlserver://hostname\myservice;databaseName=phixflow
MySQL
jdbc:mysql://hostname/phixflow
Big Query
jdbc:bigquery://@hostname:@port;ProjectId=@Project;

Anonymous Username

Enter a username.

PhixFlow will use the username and password to connect to the database when the datasource property Allow Anonymous Export is ticked.

Password Enter a password.

Advanced

Field	Description
Check Query	Use this field when connections to the target database might go

"

stale

", e.g.

. This can happen if the target database disconnects long-running connections

. Setting this field causes PhixFlow to run

.

Enter a database query that will return a row from the database.

PhixFlow runs the query against the database just before each use of a

"

pooled

"

connection. If the query does not return a row, the connection is discarded and a new connection is created. To avoid any performance impact, the query should execute quickly.

See also database collectors for guidance on writing

See the Query String section of the Database Collector page for information about writing SQL queries in PhixFlow.

Datasource Profile

Use this field when connections to the target database

have certain

has additional requirements

that are

not covered by

PhixFlow's default external Datasource configuration. The value entered in this field will determine which

the datasource and datasource instance properties.

Enter a value to use an external datasource connection profile that

this datasource instance

PhixFlow will use to connect to the target database.
Description	Optionally enter a information about the advanced configuration.

Connection URLs Anchorurlurl Still needed if URL option has gone away?

A datasource instance must have a name, a URL, and a username and password used to connect to the external datasource. The URL should be in one of the following formats:

Platform

Example Connection String

Oracle

jdbc:oracle:thin:@hostname:1521:phixflow

Oracle > 12c with PDB containers

jdbc:oracle:thin:@hostname:1521/phixflow

SQL Server

jdbc:sqlserver://hostname\myservice;databaseName=phixflow

MySQL

jdbc:mysql://hostname/phixflow

Big Queryjdbc:bigquery://@hostname:@port;ProjectId=@Project;

Remember to click

Insert excerpt

	_finish
	_finish
nopanel	true

to save and close the properties and return to the datasource properties tab.

Versions Compared

Old Version 27

New Version 28

Key

Overview

Datasource Properties

Basic Settings

Database Instance

Datasource Instances

Datasource Instance Properties
Anchor
instance
instance

Basic Settings

Example URLs

Advanced

Page Comparison

Versions Compared

Old Version 27

New Version 28

Key

Overview

Datasource Properties

Basic Settings

Database Instance

Datasource Instances

Datasource Instance Properties Anchorinstanceinstance

Basic Settings

Example URLs

Advanced

Datasource Instance Properties
Anchor
instance
instance