Versions Compared

Old Version 29

changes.mady.by.user Fiona Sargeant (Unlicensed)

Saved on

compared with

New Version 30

changes.mady.by.user Fiona Sargeant (Unlicensed)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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. You must have one datasource for each type of database to which you need to connect.  In an analysis model, you can add:

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
titleColorwhite
titleBGColor#7da054
borderStylesolid
titleSections on this page

Table of Contents
indent12px
stylenone


Datasource Properties

Insert excerpt
_standard_settings
_standard_settings
nopaneltrue

In addition to the standard icons, the properties toolbar includes 

Insert excerpt
_datasource_show_tables
_datasource_show_tables
nopaneltrue
, 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

FieldDescription
Name

Enter the name for this datasource. 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
nopaneltrue
 when the configuration is complete and the datasource is ready to be used.

Allow Anonymous Export

When an exporter uses this datasource to connect to a database, by default PhixFlow will use the PhixFlow username and password. 

Use this option when 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
nopaneltrue
 to allow anonymous export of data via the datasource. PhixFlow uses the username and password 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 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
nopaneltrue
.

Insert excerpt
_check_box_ticked
_check_box_ticked
nopaneltrue
  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
nopaneltrue
 to prevent preserve this datasource during import. This prevents the datasource being updated or deleted during import.

Exception: The datasource will be deleted when its parent application is deleted by the import. This prevents orphaned datasources persisting in PhixFlow.

Driver Class

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. 


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

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

FieldDescription

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
nopaneltrue
 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.
PasswordEnter a password.

Advanced

FieldDescription

Check Query

Use this field when connections to the target database might go stale. This can happen if the target database disconnects long-running connections.

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 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 has additional requirements not covered by the datasource and datasource instance properties.

Enter a value to use an external datasource connection profile that PhixFlow will use to connect to the target database.

DescriptionOptionally enter a information about the advanced configuration.


Remember to click 

Insert excerpt
_finish
_finish
nopaneltrue
 to save and close the properties and return to the datasource properties tab.