Appboard/2.6/builder/data sources: Difference between revisions

imported>Jason.nicholls
 
imported>Jason.nicholls
 
(One intermediate revision by the same user not shown)
Line 52: Line 52:
!Description
!Description
|-
|-
|rowspan = "2"|'''''[[appboard/2.6/builder/data_sources/database|Database]]'''''
|rowspan = "3"|'''''[[appboard/2.6/builder/data_sources/database|Database]]'''''
|Database Query
|Database Query
|This adapter retrieves specific data from a RDBMS using SQL queries. The adapter supports connectivity to SQL Server, Oracle, SQL Anywhere, PostgreSQL, 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.
|This adapter retrieves specific data from a RDBMS using SQL queries. The adapter supports connectivity to SQL Server, Oracle, SQL Anywhere, PostgreSQL, 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.
Line 58: Line 58:
|Database Table
|Database Table
|This adapter retrieves one or more tables from a RDBMS to create Data Collections.  It is 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.
|This adapter retrieves one or more tables from a RDBMS to create Data Collections.  It is 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.
|-
|[[appboard/2.6/builder/data_sources/database/mdx_query|MDX Query]]
|This adapter provides a way to execute MDX Queries against an OLAP server that implements an XML/A service. Examples include Pentaho Analysis (Mondrian), Microsoft SQL Server Analysis Services, Palo, and SAP BW. AppBoard is restricted to queries that return two dimensions, additional dimensions are not supported.
|-
|-
|rowspan = "5"|'''''[[appboard/2.6/builder/data_sources/file|File]]'''''
|rowspan = "5"|'''''[[appboard/2.6/builder/data_sources/file|File]]'''''

Latest revision as of 14:37, 21 July 2015

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 segment the various 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, create a name for the Data Source. This process is managed by the Namespace manager. Next, select the type of Data Adapter that will be used for the Data Source. Complete the wizard to create 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.

Refer to the Data Source Associations page for more information.


Data Processing Scripts

Data Processing Scripts are a method of processing data returned from Data Sources before it's made available to the rest of the system. This is a very flexible method to transform data in order to meet the specific needs of a solution.

Scripts are written in the Groovy programming language, which is a Java like dynamic language, and attached to Data Source entities.

For more details and an example of implementing Data Processing Scripts, see Data Processing Scripts.


Adding a Data Source

Naming the Data Source

To add a Data Source, log in to AppBoard as an administrator and click on the "Data Sources" label in the left "Builder Modes" tool palette. At the bottom of the "Data Sources" page, click the "Add" button. This displays the "Create New Data Source" dialog. Name the new Data Source and choose the adapter type. When naming a Data Source, only alphanumeric characters, underscores, hyphens, and periods are allowed and at least one period is recommended (but not required) for namespace categorization purposes.


Choosing an Adapter Type

There are several categories of adapters. The following is a list of the adapter types and a brief description of each type:


Adapter Properties

Category Name Description
Database Database Query This adapter retrieves specific data from a RDBMS using SQL queries. The adapter supports connectivity to SQL Server, Oracle, SQL Anywhere, PostgreSQL, 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.
Database Table This adapter retrieves one or more tables from a RDBMS to create Data Collections. It is 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.
MDX Query This adapter provides a way to execute MDX Queries against an OLAP server that implements an XML/A service. Examples include Pentaho Analysis (Mondrian), Microsoft SQL Server Analysis Services, Palo, and SAP BW. AppBoard is restricted to queries that return two dimensions, additional dimensions are not supported.
File 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.
CSV File This adapter imports a single CSV file residing on the AppBoard sever into a single Data Collection.
Shell Command Provides a way to execute a command and the output is captured by AppBoard. The output expected by AppBoard is the same as for the CSV adapters.
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.
XLS File This adapter imports a single MS Excel file residing on the AppBoard sever into a single Data Collection.
Third Party HP NNMi This adapter retrieves information about nodes and incidents from the HP Network Node Manager (NNMi) application.
Tivoli Netcool/OMNIbus This adapter retrieves information from an IBM Tivoli Netcool/OMNIbus ObjectServer.
Service Now
Web Service CSV Web Query web services that return CSV formatted responses.
JSON Web Query web services that return JavaScript Object Notation (JSON) responses.
XML XSLT Web This adapter retrieves data from an XML web service and transform it using XSLT into records that are in a format applicable to AppBoard.
Sub-Query This is a special adapter used to transform the results of another data source entity. This does not appear in the adapter type chooser when adding a data source, instead select an existing data source and click the Create Sub-Query button.

Data Source Wizard - Connect

After clicking "Add Data Source", the "Data Source Wizard" begins with the "Connect" step. Enter the necessary information for the data source to connect to AppBoard. For more information on how each type of data source is connected, click on the corresponding link in the left-hand column of the "Adapter Properties" table above.

A "Enable Failover" option is provided on the "Connect" panel for certain adapters. For detailed instructions on enabling failover, see Data Source Failover.

Template-warning.png
If checked, the "Force Reset & Rediscover" option will initiate a rediscovery of the data source. In the process any existing configuration information such as queries, attribute types, association definitions, script references, etc will be removed. This option has no effect when adding a new Data Source; however, it will remove configuration information if used when editing an existing Data Source.

Data Source Wizard - Explore

After completing the "Connect" step, proceed with the "Next" button, which leads to the "Explore" step. If this screen is displayed, AppBoard has successfully connected to the back-end data source and is ready for detailed configuration of the data. A folder icon will show the name of each data set that was discovered, and when expanded, will display a list of fields (columns) discovered in the data set.

Perform the following steps to configure the attributes of the data:

  1. Publish - Check the box for any data set that you want to be available for use in AppBoard. The default value is publish (checked). If a data set id un-checked, it will not be available for use in Data Collections and Widgets. Select one of the publishing options for each field in the data set:
    • Always - The field is available at any time for use.
    • On Demand - The client will not retrieve the data for the item until the moment that it is actually requested by a Widget for display in the client. This feature can improve the overall efficiency of the system, but may cause lags when the field is loaded by demand.
    • Never - The field is not available at any time for use.
  2. Type - Select the appropriate type for the field. For some data adapters, this will be detected from the data source and configured automatically. For others, it should be supplied so the data can be formatted and interpreted correctly. If the "Date" type is selected, and "Apply Format" button is provided to allow for detailed configuration of the date's formatting.
  3. Default Value - A value to be supplied by AppBoard for a field, only if it is null in the original data source
  4. Primary Key - Click "Edit" to launch the "Set Primary Key" dialog. One or more primary keys can be selected. Some Widgets require the selection of a primary key in a Data Source in order to function correctly.
  5. Alias - Each field can optionally have an alias. This is an alternate display name to be used that is different from the name of the field that AppBoard detected when loading the data attributes. To create an alias for a field, select the row for that field and then click the "Alias" button at the bottom.
Template-warning.png
Creating an Alias for a field that is already in use can result in a broken configuration anywhere the old field name was referenced. Such references may include sorts, filters, widget configuration, and so on. This is the same issue that would occur if a column were renamed in the source data file.


Data Source Wizard - Associate

In the "Association" step, add an association to a data source by clicking Add. This brings up a window to name the field, and define the association by relating two entities. Click the "Finish" button. For instructions on creating Associations in AppBoard, see Data Source Associations.

Editing a Data Source

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


Renaming a Data Source

  1. Click "Data Sources" in the left tool palette.
  2. Click on the row of the Data Source to rename.
  3. Click the "Edit" button.
  4. In the "Name" box, change the name displayed for the Data Source.
Template-note.png
Renaming a Data Source, or SQL Data Source entity, may result in references within Data Processing Scripts to become invalid. In cases where AppBoard can detect this an error message is presented to the administrator identifying the affected scripts.
Template-note.png
Users or administrators who are in the AppBoard client concurrent to when a Data Source is renamed can experience unpredictable results. It is recommended that renaming of Data Sources is done on a development server, tested, and then put into effect during a maintenance window.

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 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. 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 to remove.
  3. Click the "Delete" button at the bottom of the screen.
  4. Confirm the removal when the dialog box appears.