1. GENERAL INFORMATION
1.1 System Overview
The Reporting application is built as a bridge between the underlying Domino data and the MySQL Relational Database (“RDB”) used for reporting purposes within Integrum BI. Its intent is to allow through ease of use the ability to create and maintain a client specific data reporting infrastructure.
This application is a Lotus Notes based database that makes use of JDBC to export data on a pre-set frequency to a MySQL server.
The norm is for the Domino data to get exported daily. Thus the data being reported on will always be 1 day old. Each export will truncate and repopulate all the RDB tables.
2. SYSTEM SUMMARY
2.1 Installation
Make sure that the application (rreports.nsf) is in the same relative path or folder as the rest of the integrum system suite.
2.2 User Access Level
General users do not need access to this application. Only super users with the relevant knowledge should be allowed to access this application. These super users must have the ACL Admin role assigned in order to use this application. Users that do not have the Admin role and has Editor access assigned will be able to see the Home Page (if configured) in order to run client specific functions.
3. APPLICATION CONFIGURATION
3.1 Getting Started
On opening the Reporting application you will be presented with the main navigator in the left hand frame and the corresponding view displaying its saved documents.
Use the main navigator to navigate through the different schemas, view Agent logs or configure a Home Page.
3.2 RDB Server Schemas
The Server Schema is used to specify the address or universal location of the MySQL server and the creation of the relational database. Initially a Server Schema document must be created.
From the main navigator, select RDB Server Schema. In the view frame select ‘New’. A new Server Schema document will be created with some default values set.
Field Label | Description |
History Information | This is a basic table displaying when this Server Schema was created, last modified and by whom. |
Server Type | The Server Type is used to determine which JDBC driver to use. We only allow the MySQL driver option at present. |
Server LOCAL URL | This URL address means local to the Domino server. The scheduled agents that export the Domino data will use this address to push the data to the MySQL server.
It is vital that the correct URL format is used. See examples below;
Examples 1: “jdbc:mysql://localhost/”
Examples 2: “jdbc:mysql://127.0.0.1/”
Once this address has been captured you can attempt connecting to the MySQL server by using the ‘Test Connection’ button to insure your address is valid. Bear in mind that this must be done on the server. |
Server REMOTE URL | This URL address is the remote or external address to the MySQL server. This allow users with the correct access level to create or apply changes to the Table and Field Schemas (more detail later in this manual) on the MySQL Server from their local personal computers running the Lotus Notes client.
It is vital that the correct URL format is used. See examples below;
Examples 1: “jdbc:mysql://192.168.0.1/”
Examples 2: “jdbc:mysql://mydomain.com.au/”
Once this address has been captured you can attempt connecting to the MySQL server by using the ‘Test Connection’ button to insure your address is valid.
NOTE: If the Lotus Notes client is running on the server it will still use the Server REMOTE URL address. Only the scheduled agents use the Server LOCAL URL address. Thus if the MySQL Server does not reside on the same server as Domino this address needs to point to the correct intranet IP. |
Database Name | You can choose what the RDB is called by specifying a unique name here. As a standard practise this will be the name of the integrum system instance, as there could be multiple instances of the integrum system running on the Domino server using the same MySQL Server.
Example: jetstaranz
Please use best practise and use short but descriptive names. |
Database Username | A username should be provided by the Server Administrator. Specify the username with the desired mandates here. |
Database Password | A password for the provided username must be captured here. |
Once all the above fields have been captured and the connection tested successful you should create the relational database.
Click the “Create Database” button. This will create the database on the specified MySQL Server. You also have the option to drop or delete the specified database if required.
NOTE: Be extremely careful when deleting a database as all its containing tables, indexes and data will be deleted as well.
Ensure that the Result at the bottom of the table is Success as seen below.
Save and Close the Server Schema.
3.3 RDB Table Schemas
The next step after creating the RDB is to create the necessary tables used to store the data.
From the main navigator, select RDB Table Schema. In the view frame select ‘New’. A new Table Schema document will be created with some default values set.
The Table Schema form has the following sections;· Table Schema Description
· Table Schema Details
· Notes Database Details
· History
· Field List
· Index List
3.3.1 RDB Table Schema Description
Field Label | Description |
Schema Description | This field should contain a short description for this Table Schema. The actual table name is not very descriptive of what data the table contains and its intended use. |
NOTE: There is a Comments field at the bottom of the form. This is a rich text fields that can be used to store attachments with additional information.
3.3.2 RDB Table Schema Details
Field Label | Description |
Server Schema | Select the preconfigured Server Schema used by this Table Schema. |
Table Name | Specify the required table name. As a standard practise use the name of the data source (Domino Form) as the table name.
Please use best practise and use short but descriptive names. |
Table Storage Engine | MySQL uses the InnoDB storage engine as the default, however as a standard we use the MyISAM storage engine for performance related reasons. |
Table Primary Key | A primary key uniquely identifies a row in a table. One or more columns may be identified as the primary key. The values in a single column used as the primary key must be unique (like the Domino Document ID).
You can choose to use the default Primary Key or to use your own custom key. As a standard practise leave this option as “Default”. Only if a column exists that has a unique value and is used to filter or compare data in your reports then use that column as a primary key as this column will be indexed by default. |
3.3.3 Notes Database Details
Field Label | Description |
Notes Database | Click on the “Select Database” hotspot, navigate to and select the relevant database. The Server, File Name and Title values will be populated. |
Export View Name | The Export View Name field should contain the name of the view used to obtain the source data from the selected Notes database.
NOTE: If unsure please consult your Lotus Notes Developer to which view to use. If this view does not exist NO data will be exported. |
Export Frequency / Status | At present the Reporting application will export the data on a daily basis.
By default the Status is set to “Inactive”. Once you are done and satisfied with your Table Schema you should change this to “Active”. The Domino data will not be exported to this table if this status is set to “Inactive”. |
3.3.4 History
This is a basic table displaying when this Table Schema was created, last modified and by whom.
3.3.5 Field List
This embedded view will list all the Field Schemas (more detail later in this manual) associated with this table. These Field Schemas can be edited from this view by double clicking on the relevant field.
To create a new Field Schema click the “New Field Schema” button.
If you want to use the same Field Schemas for another table you have the ability to clone these Field Schemas without having to recreate them again.
To clone a table’s Field Schema click on the “Clone Field Schema” button. You will be asked to select the table of which Field Schemas you would like to clone.
Example:
You have an existing table called EMPLOYEES which has 4 columns called FirstName, Surname, Email and MobileNo. You have created a new table called CUSTOMERS that requires the same set of columns (Field Schemas). You can clone all the EMPLOYEES table’s columns to the CUSTOMERS table without having to re-create each one again. Edit the CUSTOMERS table, click on the “Clone Field Schema” button and select the EMPLOYEES table.
3.3.6 Index ListThis embedded view will list all the Indexes (more detail later in this manual) associated with this table.
Once you are done creating the required Field Schemas you can create the RDB table. As good practise make sure that you can connect to the MySQL Server by clicking on the “Test Connection” button first. To create the RDB table click on the “Create Table” button.
Ensure that the Result at the bottom of the table is Success as seen below. The result will also display the amount of indexes created. This total will correspond with the total in the Index List view.
NOTE: Every time an amendment is made to any of Field Schemas associated with this table the RDB table must be dropped (more detail later in this manual) and re-created in order to apply those amendments.
Now that your relational database and table has been created, you can manually export the Domino data by clicking on the “Run Export Now” button if you choose to do so. This will get the source data from the view in the selected Notes Database and export the selected data accordingly. Once the export is done you should get the following result as seen below.
To verify that the export was successful please review the log file. (more detail later in this manual)
There are two additional options to help you manipulate the table and its content.
1. The “Drop Table” button will delete the RDB table and its content.
NOTE: If the table already exists you should drop the table before recreating it.
2. The “Clear Table” button will delete all the content in the RDB table.
3.4 RDB Field Schemas
A new Field Schema can only be created from within a Table Schema. This insures that the Field Schema gets associated with the correct Table Schema.
To create a new Field Schema, open the relevant Table Schema, and click on the “New Field Schema” button. A new Field Schema document will be created with some default values set.
The Field Schema form has the following sections;· Field Schema History
· Data Source
· RDB Field Information
3.4.1 Field Schema History
This is a basic table displaying when this Field Schema was created, last modified and by whom.
3.4.2 Data Source
There are three Data Source options.
Option | Description |
NSF | Being the default, use this option when your data reside in a Lotus Notes database. |
CSV/EXCEL | In some cases specific data might be provided in a CSV or EXCEL file.
This option is only used in cases where a specific agent has been developed for this particular RDB table. The Table and Field Schema still need to be created in order to create and maintain the RDB infrastructure. |
Other | This option is only used in cases where a specific agent has been developed for this particular RDB table. The Table and Field Schema still need to be created in order to create and maintain the RDB infrastructure. |
If you have chosen NSF as your data source you will be presented with the following mandatory fields.
Field Label | Description |
Value From | The NSF data source caters for three different sources.
Field
Allows you to select a Notes Field name for direct data interpretation.
LotusScript
By using some reserved variables you can write LotusScript code to access underlying Notes Document to extract and manipulate data into the correct format for exportation.
Only use this option if you have sound knowledge of the LotusScript language and are sure of your intended result.
Document URL
This option will generate a URL link to this underlying document. |
Notes Field Name | The name of the Notes Field used to obtain the data, if you have selected Field as your value source. |
LotusScript Code | The actual LotusScript code used to generate the end result value.
There are two variables pre-declared to consider;
“docExec” - underlying Notes Document
“vExecValue” - temporary variable of type variant. Apply the end result to this variable. |
Multi Value Field | Indicate whether the Notes Field has got multiple values. The separate values will be concatenated using a semi-colon. |
3.4.3 RDB Field Information
Field Label | Description |
Table Name | The name of the RDB Table as specified on the Table Schema. This value gets populated automatically and cannot be changed. |
Field Name | The field or column name. As standard practise use the Notes Field Name.
Please use best practise and use short but descriptive names. |
Field Type | The field or column type. This should correspond with the Notes Field type where possible. As a standard practise always use the CHAR type instead of the TEXT type where the source value is text. The TEXT column type is very performance and space intense and must only be used as a last resort when values exceed 255 characters.
NOTE: There is a section at the bottom of the form that has detailed information about these options. Please reference this section if you are unsure of the desired type. |
Field Character Size | Specify the column length for Field Type CHAR. As standard practise be sure to only specify the required size.
Example: If you know that the CARNumber field is comprised out of 15 characters then set your Field Character Size to 15. If this was set to 50 then the MySQL server will store 50 characters worth of bytes even though only 15 characters is used. This puts unnecessary overhead on the server. Multiply this by the amount of fields and the amount of tables on the server and you quickly see the holistic gain. |
Field Decimal Places | The Decimal data type will allow you to store numerical values with decimal places. Select your desired decimal places, a maximum of up to 5 decimal places are allowed. |
Create Index | Select whether you want an Index created for this column.
When the table is created it will automatically create the relevant indexes for the specified fields/columns. These indexes can be viewed in the Table Schema INDEX LIST embedded view.
It is important that NOT all fields in a table should be indexed as this will put tremendous strain on the server and will take up allot of unnecessary space. This will be counterproductive and will actually slow down the server even more.
The aim is to create an index for key fields that is used typically in filters and comparison operators. |
4. ADITIONAL FUNCTIONALITY
4.1 Agent Logs
When data is exported certain information gets written into a log document. These log documents can be used to validate the success of the various data exports. The Reporting Application will create a log document for every Active Table Schema export as well as manual exports ran from the Table Schemas.
These log documents will typically contain the following;· If a successful connection could be established to the MySQL Server
· If the RDB table has been truncated successfully.
· Any errors that may have occurred during the export process.
· The total number of records exported
To view the existing logs navigate to the Agent Logs view on the main navigator.
4.2 Home Page
This functionality is similar to the Group Profile Home Page on the integrum Portal application. Any additional or specific functionality that is required can be placed here.
To create a new Home Page navigate to the Home Page view on the main navigator. Click on the “New Home Page” button. Add your content into the Rich text field, and click on the “Save” button to save your document.
Only one Home Page can be used. All users that do not have the ACL Admin role will be presented with this Home Page when accessing this application.
(See attached file: Integrum User Manual - Reporting Application.docx)