Versions Compared

Version Old Version 76 New Version Current
Changes made by

Fiona Sargeant

Anthony George

Saved on

Key

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

Insert excerpt
_Banners
_Banners
nameanalysis
nopaneltrue

Insert excerpt_terms_changing_terms_changingnopaneltrue

Live Search
spaceKey@self
additionalnone
placeholderSearch all help pages
typepage

Panel
borderColor#00374F
titleColorwhite
titleBGColor#00374F
borderStylesolid
titleSections on this page

Table of Contents
maxLevel3
indent12px
stylenone

   

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.


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.

To add a new datasource to an analysis model:

  1. Go to the model's toolbar → Create group.
  2. Click
    Insert excerpt
    _database
    _database
    nopaneltrue
     to expand the menu.
  3. Drag a 
    Insert excerpt
    _datasource
    _datasource
    nopaneltrue
     onto the analysis model.

To add an existing datasource to an analysis model, in the model diagram toolbar:

  1. Go to the model toolbar → List group.
  2. Click
    Insert excerpt
    _database
    _database
    nopaneltrue
     to expand the menu.
  3. Click 
    Insert excerpt
    _datasource
    _datasource
    nopaneltrue
     to open the list of available datasources.
  4. Drag a datasource into the analysis model.

Insert excerpt
_property_toolbar
_property_toolbar
nopaneltrue

Datasource 
Insert excerpt
_property_tabs
_property_tabs
namebasic-h

-basic

nopaneltrue

Insert excerpt
_parent
_parent
nopaneltrue

Basic Settings

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

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_tick
_check_box_tick
nopaneltrue
 when the configuration is complete and the datasource is ready to be used.

Protect during import

Insert excerpt
_check_box_untick
_check_box_untick
nopaneltrue
 to allow this datasource to be updated or deleted by import from another PhixFlow instance.

Insert excerpt
_check_box_tick
_check_box_tick
nopaneltrue
 to retain these settings if this datasource is imported from another PhixFlow instance.

Expand
titleMore ...

Insert excerpt
_protect_import
_protect_import
nopanel

true
Note

Exception: If the datasource parent and the datasource have been deleted in the source instance, then the import deletes the datasource from the target instance. This prevents orphaned datasources persisting in PhixFlow.

Database Driver

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. 

When a driver class is selected, to open its properties, click the Driver Class heading.

Image Removed

Datasource Instances 

This section has a toolbar with 

true


Note

Exception: If the datasource parent and the datasource have been deleted in the source instance, then the import deletes the datasource from the target instance. This prevents orphaned datasources persisting in PhixFlow.


Database Driver

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

Tip

The drop-down list may contain database drivers that are deprecated and do not have the necessary jar files; see Database Driver. 

When a database driver is selected, to open its properties, click the blue chevron Image Added next to the Database Driver heading.

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

Insert excerpt
_delete
_delete
nopaneltrue
.

Database Collectors

This section has a toolbar with standard buttons.

The grid contains a list of

datasource instances that belong to

database collectors that use this datasource. Double-click

on a row to open the datasource instance properties. To add a datasource to the list, click To remove a datasource instance from the grid, use the toolbar button  Insert excerpt_delete_deletenopaneltrue.Database Collectors

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.

Note

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

Rate Limiting

Insert excerpt

_new_newnopaneltrue . PhixFlow opens the properties for a new datasources instance; see Datasource Instance Properties, below.

HTTP Configuration Properties
HTTP Configuration Properties
nameRateLimitingDesc
nopaneltrue

For Database Exporters this set per batch of records committed to the database, controlled by its Commit Size option.

Insert excerpt
HTTP Configuration Properties
HTTP Configuration Properties
nameRateLimiting
nopaneltrue

Advanced

FieldDescription
Internal IDRead-only. PhixFlow displays the identifier for this datasource. The PhixFlow support team may ask for this identifier during troubleshooting.

Analysis Models

This section has a toolbar with standard buttons.

The grid contains a list of

database collectors

analysis models 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.
Note

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

Analysis Models

This section has a toolbar with standard buttons.

The grid contains a list of analysis models that use this datasource. Double-click on a row to open the analysis model properties. 

row to open the analysis model properties. 

Insert excerpt
_description
_description
nopaneltrue

Insert excerpt
_audit
_audit
nopaneltrue

Anchor
instance
instance
Datasource Instance 
Insert excerpt
_property_tabs
_property_tabs
namebasic-h
nopaneltrue

Insert excerpt
_

description_descriptionnopaneltrue Insert excerpt_property_tabs_property_tabsnameh-auditnopaneltrue

parent
_parent
nopaneltrue

Basic Settings

For a datasource instance, you must:

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

Other settings are optional.

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_

audit

box_tick
_

audit

check_box_tick
nopaneltrue

AnchorinstanceinstanceDatasource Instance  Insert excerpt_property_tabs_property_tabsnameh-basicnopaneltrue Insert excerpt_parent_parentnopaneltrue

Basic Settings

For a datasource instance, you must:

  • Enter a Name.
  • Enter the URL for the database.
  • Specify the 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_tick_check_box_ticknopaneltrue 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 key that identifies the username in the keystore.

These options are available when Authorisation Type is External.

The keys 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.

Insert excerpt_keystore_keystorenopaneltruePassword KeyEnter the key 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 Properties 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  Insert excerpt_finish_finishnopaneltrue to save and close the  datasource instance properties and return to the datasource properties tab. Insert excerpt_property_tabs_property_tabsnameh-auditnopaneltrue Insert excerpt_audit_auditnopaneltrue

 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
  • MySQL 
    jdbc:mysql://hostname/phixflow
  • Big Query
    jdbc:bigquery://@hostname:@port;ProjectId=@Project;#
  • SQL Server 
    jdbc:sqlserver://hostname\myservice;databaseName=phixflow;encrypt=false
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 key that identifies the username in the keystore.

These options are available when Authorisation Type is External.

The keys 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.

Insert excerpt
_keystore
_keystore
nopaneltrue

Password KeyEnter the key 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 

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

Insert excerpt
_audit
_audit
nopaneltrue


Live Search
spaceKey@self
additionalnone
placeholderSearch all help pages
typepage

Panel
borderColor#00374F
titleColorwhite
titleBGColor#00374F
borderStylesolid
titleSections on this page

Table of Contents
maxLevel3
indent12px
stylenone

Learn More

For links to all pages in this topic, see Analysis Models for Batch Processing Data.

Insert excerpt
_terms_changing
_terms_changing
nopaneltrue