Appboard/old/data sources: Difference between revisions

imported>Jason.nicholls
imported>Jason.nicholls
No edit summary
Line 1: Line 1:
{{DISPLAYTITLE:Data Source}}
[[Category:AppBoard old]]
[[Image:HeaderFlow01.png]]
[[Image:HeaderFlow01.png]]



Revision as of 03:35, 17 July 2014

HeaderFlow01.png

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.