Appboard/old/data sources

Data Adapters

Data Adapters are the AppBoard server-side connectors that communicate directly with external data sources such as databases, file systems, web services, and system APIs. Each adapter can have its own set of unique settings that are used to connect to the external data source.

Data Sources

AppBoard Data Sources identify the adapter and the configuration settings required to access the external data sources and potentially filters on the data to be accessed by the AppBoard server. Data is brought into the AppBoard Server as Data Sets (Entities) and returned to the AppBoard Client as Data Collections. Data Sources are published inside of an AppBoard's namespace model, which is basically a dot notation used to categorize and segement the varioius data sets into uniquely addressable entities. A Data Source may bring one or more unique data sets into the system. Relationships between Entities are modeled as Associations. They can be established through the Data Source UI or imported from existing associations defined by the external data source.

There are several components that comprise creating an AppBoard Data Source. In the Data Source Wizard, first you create a name for the Data Source. This process is managed by the Namespace manager. Next, you select the type of Data Adapter that will be used for the Data Source. Once you complete the wizard, you have a named Data Source. This Data Source can then be used throughout the AppBoard Builder for designing the presentation to the client (in AppBoard Data Collections, Widgets, and Boards).

Associations

Associations are logical connections between two pieces of data; often referred to as "foreign keys" in databases. For example, a collection of records about the individual states within the United States could be associated with a collection of records about the individual counties in each state. The county would have a "stateId" field, that defined the state in which the county resides. Therefore a one-to-many association can be created between a state and counties.

Adding a Data Source

Naming the Data Source

To add a Data Source, click on the "Data Sources" link under the "Builder Modes" section on the left side of the page. At the bottom of the "Data Sources" page, you will need to click the "Add" button to get started. This opens up a "Create New Data Source" window where you will name the new data source and choose the adapter type. When naming your data source, only alphanumeric characters, underscores, hyphens, and periods are allowed and at least one period is required for namespace categorization purposes.

Choosing an Adapter Type

There are several categories of adapters and types to choose from. Here is a list of the adapter types and a description of each type:

Adapter Properties

Category	Name	Description
Database	JDBC SQL	This adapter retrieves data from a RDBMS using SQL queries to create a single data set. The adapter supports connectivity to SQL Server, Oracle, SQL Anywhere, Postgres SQL Hypersonic (HSQLDB), H2, MySQL, and DB2. This is typically used when a JOIN is required to produce a more appropriate view of the data (without unnecessary associations) or for performance reasons.
	JDBC	This adapter retrieves a filtered set of tables from a RDBMS to create multiple Data Collections. Equivalent to executing "select * from <table_name>" for the selected tables. The adapter supports connectivity to SQL Server, Oracle, SQL Anywhere, Postgres SQL Hypersonic (HSQLDB), H2, MySQL, and DB2. Advanced configuration may be set per entity to control the caching of a particular data set or to limit the amount of data by using a "where" clause.
	EclipseLink	This adapter retrieves a filtered set of tables from a RDBMS to create multiple Data Collections using EclipseLink and is provided as a secondary adapter to the JDBC adapter. Advanced configuration may be set per entity to control the caching of a particular data set or to limit the amount of data by using a "where" clause.
	MySQL	This adapter retrieves a filtered set of tables from a MySQL RDBMS to create multiple Data Collections and is provided as a secondary adapter to the JDBC adapter. Advanced configuration may be set per entity to control the caching of a particular data set or to limit the amount of data by using a "where" clause.
File	CSV File	This adapter imports a single CSV file residing on the AppBoard sever into a single Data Collection.
	CSV Directory	This adapter imports all of the CSV files residing on the AppBoard sever from a specified directory. Each CSV file is imported as a separate Data Collection.
	XLS File	This adapter imports a single MS Excel file residing on the AppBoard sever into a single Data Collection.
	XLS Directory	This adapter imports all of the MS Excel files residing on the AppBoard sever from a specified directory. Each MS Excel file is imported as a separate Data Collection.
3rd Party	Tivoli ObjectServer	This adapter retrieves a filtered set of tables from an IBM Tivoli Netcool Object Server to create multiple Data Collections. SQL queries can be applied against this Data Source to create a specific View of the data using the Entity Query Adapter. Advanced configuration may be set per entity to control the caching of a particular data set or to limit the amount of data by using a "where" clause.
Web Service	XML REST	This adapter retrieves XML data from a REST data source. Hierarchical relationships within the XML data are captured as associations within the AppBoard system. XSLT style sheets are utilized to transform the XML data into the XML format used by AppBoard to ingest the data.
	XML Config	This adapter is utilized by developers to read and write XML-based AppBoard configuration information into the AppBoard system.
Utilities	Entity Query	This adapter provides a means to create new Data Sources by applying SQL queries against other AppBoard Data Sources to create a specific View of the data. For example, the JDBC adapter might be used to bring in a specific set of tables into AppBoard, and the Entity Query would then be used to create a specific View of that data. The AppBoard Data Source and Collections wizards provide a subset of the capabilities provided by the Entity Query adapter (pivot, sort, group), but do not provide the flexibility to do so that is provided by SQL.

Data Source Wizard - Connect

Database

After selecting a DB adapter type, you come to the "Data Source Wizard" window and begin in the "Connect" step. Here you must decide on the template for your data source, and fill in the necessary information. These are the possible boxes you will need to fill in:

Name	Description
url	The Java class name for the JDBC driver
driver	The JDBC connection string/URL for defining the connection. This typically contains information about the database server and name, the user id and password for conencting to the database.
platform	EclipseLink Platform Class supporting the specific database. Typically pre-filled by selecting the appropriate template.
username	User name credential used to connect to database.
password	User password credential used to connect to database.
poolsize	Number of connections to pool. This adapter re-uses connections for efficiency.
readpool	Number of read connections to pool. This adapter re-uses connections for efficiency.
writepool	Number of write connections to pool. This adapter re-uses connections for efficiency.
timeout	Time to wait before timing out on connecting to the datasource.
cacheTimeout	Time limit before re-loading results from the database.
catalogName	Database catalog name. It is not required, used to limit the set of tables found on the search.
schemaName	The database schema name. It is not required, used to limit the set of tables found on the search.
tableNameRegex	Regular expression that defines the database tables that will be returned by the adapter. It is not required, but may be used to limit the set of tables found on the search.
sqlQuery	SQL query statement to be run on the database. Typically used when it is necessary to perform more advanced JOINS to produce a more appropriate view of the dataset.

Additional configuration may be set on a per-entity basis via the Data Source Wizard by selecting the Entity and selecting the 'Advanced' button. These are the possible boxes you will need to fill in:

Name	Description
cacheTimeout	Time limit before re-loading results from the database.
filter	A "where" clause used to filter data pulled from database. User input should exclude the term 'where'. This can be used to reduce the view of data that may be retrieved.
order	An "order by" clause used to control the order of data returned by the SQL statement.

File

After selecting a data adapter type, you arrive in the Data Source Wizard window in the "Connect" step of the process. Here you are given data boxes to fill out before moving on. This is some information that will be useful in filling these out:

Name	Description
directory	The directory path containing the files to be read by the data adapter. Where ${application.home} is <appboard install root>\webapps\enportal\WEB-INF\data
filePath	The file path for the file to be read by the data adapter. Where ${application.home} is <appboard install root>\webapps\enportal\WEB-INF\data
fileExtension	The extension of the file to be read. For example: ".csv"
sheetName	The name of the sheet to be read from the designated MS Excel file by the adapter.
delimiter.data	The character used to delimit the row of data to be read by the adapter. For example: ",".
headerLine	Whether the data includes a header line used to define the columns/field names. It is recommended to update File sources to provide a header line so that AppBoard can generate the proper attribute names to reference the data. If this is not found, the first row will be used as the attribute names.
useQuoteChar	Whether the data fields should be written within quotes. This should be set if the 'delimiter.data' separator character is used within the values.

Tivoli ObjectServer

After selecting a data adapter type, you arrive in the Data Source Wizard window in the "Connect" step of the process. Here you are given data boxes to fill out before moving on. This is some information that will be useful in filling these out:

Name	Description
host	IP Address or DNS resolvable host name of the ObjectServer.
port	TCP port for the ObjectServer connection. Default is 4100.
dbname	Netcool ObjectServer name / database name.
tableNameRegex	details|journal|iduc_messages|application_types).*
username	Username credential used to connect
password	Password credential used to connect
poolsize	Number of connections to pool
timeout	Time to wait before timing out on connecting to the datasource.
cacheTimeout	Time limit before re-loading results from the database.

Additional configuration may be set on a per-entity basis via the Data Source Wizard by selecting the Entity and selecting the 'Advanced' button. These are the possible boxes you will need to fill in:

Name	Description
cacheTimeout	Time limit before re-loading results from the database.
filter	A "where" clause used to filter data pulled from database. User input should exclude the term 'where'. This can be used to reduce the view of data that may be retrieved.
order	An "order by" clause used to control the order of data returned by the SQL statement.

WebService

After selecting a data adapter type, you arrive in the Data Source Wizard window in the "Connect" step of the process. Here you are given data boxes to fill out before moving on. This is some information that will be useful in filling these out:

Name	Description
url	This is the location of the information file used for your data source. Included in the distribution are some sample xml files which can be accessed by using the 'file://' url. For example, on windows it would look like 'file://localhost/${application.home}/data/pkg/sample/atlas/atlas.xml' and on UNIX it would look like: 'file://${application.home}/data/pkg/sample/atlas/atlas.xml'. Please see http://en.wikipedia.org/wiki/File_URI_scheme for more details on using file:// urls; although you will normally be using http:// to reference the RESTful WebService.
stylesheetPath	The path from '${application.home}/stylesheets/' of the XSL stylesheet to be used to convert the data from the web service into the AppBoard internal XML representation.
httpAuthUsername	Username to use for HTTP Auth (Basic, Digest, etc.). Ignored if HTTP Auth is not required.
httpAuthPassword	Password to use for HTTP Auth (Basic, Digest, etc.). Ignored if HTTP Auth is not required.
cacheTimeout	Time limit before re-loading results from the RESTful web service. Note: if a timeout is not specified, it will calculate the timeout based on the timeout specfied by the response header. If the request should be run on every request, then a value of '0' should be specified.

Utilities - Query Entity

After selecting a data adapter type, you arrive in the Data Source Wizard window in the "Connect" step of the process. Here you are given data boxes to fill out before moving on. See the reference guide at http://code.google.com/apis/visualization/documentation/querylanguage.html for the detailed specification on valid queries (namespace is an internal term used by AppBoard to identify a data set). This is some information that will be useful in filling these out:

Name	Description
namespace	Fully qualified entity name identifying the registered data set to query (for example: "appboard.tutorial.Airports").
select	Attributes and/or aggregate/scalar functions to apply.
filter	Conditional test to restrict records to retrieve.
sort	Allows record resuls to be sorted.
group	Grouping of records to apply scalar funcitons against.
pivot	Attribute to pivot on unique values will become the attributes.
limit	Limit of records to return.
offset	Offset of records to return.
cacheTimeout	Time limit before re-running the query on the identified namespace.

Data Source Wizard - Explore

After filling in the blanks, you will proceed with the "Next" button, which leads you to the "Explore" step. You should see the file and a drop-down box for the "Primary Key." Select the appropriate key from the list and click the "Next" button.

Data Source Wizard - Associate

In the "Association" step, you can add an association to your data source by clicking the add button towards the bottom of the screen. This brings up a window where you name the field you are adding, and define the association by relating two entities. Click the "Finish" button and you have successfully created a Data Source.

Editing a Data Source

1. Click "Data Sources" in the left tool palette.
2. Click on the desired Data Source row.
3. Click the "Next" button.
4. Follow the "Adding a Data Source" instructions.

Copying a Data Source

1. Click "Data Sources" in the left tool palette.
2. Click on the Data Source row to be copied.
3. Click the "Copy" button.
4. In the "Copy Data Source" window, enter a name for the new Data Source.
5. Click the "Copy Data Source" button.
6. Follow the "Adding a Data Source" instructions if you wish to make any changes.

Removing a Data Source

Note that removing a data source will not automatically remove the associated Data Collections, Stacks, Boards or Widgets. You can use this feature to simply replace one data source with another, assuming the namespaces and published record columns match.

1. Click "Data Sources" in the left tool palette.
2. Select the Data Source you would like to remove.
3. Click the "Delete" button at the bottom of the screen.
4. Confirm the removal when the dialog box appears.