PhixFlow Help

Datasource

Owned by Former user (Deleted)
Last updated: Mar 23, 2021 by Fiona Sargeant (Unlicensed)
4 min read

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.


Sections on this page

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.

Datasource Properties

For information about the properties toolbar, and about the sections Parent Details, Analysis Models, Description and Audit Summary, see Common Properties.  For a full list of all the PhixFlow property tabs and windows, see Property Tabs.

In addition to the standard icons, the properties toolbar includes  Show Tables, 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

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

Protect during import

 Tick to preserve this datasource during import. This prevents the datasource being updated or deleted.

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. 

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  Add . PhixFlow opens the properties for a new datasources instance; see Datasource Instance Properties, below.

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

Database Collectors

This section has a toolbar with the   Show detailed list and   Refresh buttons; see standard buttons.

The grid contains a list of database collectors that use this datasource. Double-click on a row to open the database collector properties. You can change whether or not the collector uses this datasource or whether the collector is enabled.

Any changes you make will affect the analysis models in which the collector is used.

Datasource Instance Properties

For information about the properties toolbar, and about the sections Parent Details, Analysis Models, Description and Audit Summary, see Common Properties.  For a full list of all the PhixFlow property tabs and windows, see Property Tabs.

For a datasource instance, you must:

  • Enter a Name.
  • Enter the URL for the database.
  • Specify theUsername 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

 Tick 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;
Authorisation Type

Select:

  • Local: to provide user credentials from PhixFlow. When you select this option, the Username and Password properties are available.
  • External: to provide user credentials from the secure keystore. When you select this option, the Username Key and Password Key properties are available.
    Use this option for greater security.
UsernameEnter a username.

These options are available when Authorisation Type is Local.

This username and password must match an account that is configured to access the datasource instance. PhixFlow connects to the datasource using this username and password.

PasswordEnter a password.
Username Key

Enter the alias that identifies the username in the keystore.

These options are available when Authorisation Type is External.

The aliases must match a username and password stored in the keystore and these are configured to access the datasource instance. PhixFlow connects to the datasource using this username and password.


Your system administrator adds usernames, passwords and their aliases (keys) to the keystore. They will notify you of the key to use. See Adding Data to a Keystore.

Password KeyEnter the alias that identifies the password in the keystore.

Advanced

Only use these options if you know about database connections and SQL queries. For help with these options, contact support@phixflow.com.

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

Enter the path to an external datasource connection profile.

PhixFlow will use the configuration information in the profile to connect to the target database. 

DescriptionOptionally enter a information about the advanced configuration.

Remember to click  OK to save and close the  datasource instance properties and return to the datasource properties tab.








Please let us know if we could improve this page feedback@phixflow.com