Configuring Spotfire Reports

Though similar, there are two slightly different flows for configuring the TIBCO Spotfire reports:

MySQL Report Configuration

You can generate the following reports using Oracle MySQL: EMS Server Message Metrics Report (using ems_serverinfo_mysql.dxp and ems_serverinfo_mysql.txt) and EMS Queue Message Metrics Report (using ems_queues_mysql.dxp and ems_queues_mysql.txt).

  1. Open the ems_queues_mysql.dxp Spotfire Analysis file in the rtvapm/emsmon/projects/reports/Spotfire directory that was created during the EMS Monitor installation.

    2. The Data Connection Login window displays.

    dataconnectionlogin.png

     

  2. Click the Skip button (there is no need to log in at this point).

    3. The TIBCO Spotfire dashboard displays.

    TibcoSpotfireDasboard.png

     

  3. Click File> Replace Data Table.

    4. The Replace Data Table window displays.

    ReplaceDataTable.png

     

    Note: When connecting the ems_queues_mysql dashboard to your MySQL data, Spotfire’s Replace Data Table functionality may run very slowly, or even time-out, if the dataset is too large.

  4. Click the Select button (associated with the Select source to replace with field) and select Oracle MySQL.

    5. The Oracle MySQL Connection window displays.

    Mysqlconnect.png

     

  5. Enter the Server, Username, Password, select Database authentication as the Authentication Method, and click the Connect button.

    6. The Database drop down should be populated.

  6. Select rtvhistory from the Database drop down and click the OK button.

    7. The Views in Connection window displays.

    CustomQuery.png

     

  7. Select the Custom Query drop down list and select New Custom Query.

    8. The Custom Query window displays.

    customQuery_emsserverinfomysql.png

     

  8. Enter the desired name (whatever name is meaningful for you) into the Query_name field, open the text file in your installation directory associated with your table (for example, if you are selected ems_queues_mysql.dxp initially, then open ems_queues_mysql.txt), copy and paste the SQL code in the file into the Query field on the Custom Query window, and click the Verify button.

    9. Note: This step is required because the database contains data that has been compacted as well as data that has not yet been compacted. The SQL code compacts the data that has not been compacted and adds the newly compacted data to the already compacted data so that all the “bucket” values are the same. For example, let’s say the compacted data is compacted so that the oldest data is contained in 15 minute buckets, but the more recent data is contained in 5 or 10 minute buckets. The SQL code takes the data contained in the 5 and 10 minute buckets and compacts it into 15 minute buckets so that all the data is consistently bucketed.

    Once the SQL has been verified, the column names display in the Result Columns tab.

  9. Click the OK button on the Custom Query window.

    10. The new query (for example, EMS_queues_mysql) should display in the list of Custom queries on the Views in Connection window.

    addconnectionstep8.png

     

  10. Select your new custom query and click the Add button.

    11. Your new custom query should display in the Views in connection region and the query’s associated columns should display in the Columns in selected view region.

    addconnectionsstep8clickadd.png

     

  11. Click the OK button on the Views in Connection window.

    12. The Replace Data Table window displays.

    replaceDataTableStep9.png

     

  12. Select the Import data table radio button and click the OK button.

    13. Your data should display in TIBCO Spotfire.

    MySqlQueuesRpt.png

     

  13. Repeat the same steps above for the ems_serverinfo_mysql.dxp Spotfire Analysis file and the ems_serverinfo_mysql.txt file to create the EMS Server Message Metrics Report.

Oracle Report Configuration

There are two different Oracle reports that can be generated: EMS Server Message Metrics Report (using ems_serverinfo_sql.dxp and ems_serverinfo_sql.txt) and EMS Queue Message Metrics Report (using ems_queues_sql.dxp and ems_queues_sql.txt).

  1. Open the ems_queues_sql.dxp Spotfire Analysis file in the rtvapm/emsmon/projects/reports/Spotfire directory that was created during the EMS Monitor installation.

    2. The Data Connection Login window displays.

    dataconnectionlogin00001.png

     

  2. Click the Skip button (there is no need to log in at this point).

    3. The TIBCO Spotfire dashboard displays.

    TibcoSpotfire_emsqueues.png

     

  3. Click File> Replace Data Table.

    4. The Replace Data Table window displays.

    ReplaceDataTable_sql.png

     

  4. Click the Select button (associated with the Select source to replace with field) and select Oracle.

    5. The Oracle Connection window displays.

    OracleConnection.png

     

  5. Enter the Server, select SID in the Connect using drop down (and enter orcl in the associated field if not defaulted), select Oracle authentication as the Authentication Method, enter the Username and Password, and click the Connect button.

    6. The Select Database Tables window displays.

    selectdbtables.png

     

  6. Select ACATB.EMS_QUEUES from the Available Tables select list and click the Add button.

    7. The table displays in the Selected tables region.

    selectdbtables2.png

     

  7. Click the OK button.

    8. The Views in Connection window displays with the selected table listed in the Available tables in the database region.

    viewsinconnection_orcl.png

     

  8. Select the EMS_QUEUES table from the list and click Custom Query > New Query.

    9. The Custom Query window displays.

    custom_query_orcl.png

     

  9. Enter the desired name (whatever name is meaningful for you) into the Query_name field, open the text file in your installation directory associated with your table (for example, if you selected ems_queues_sql.dxp initially, then open ems_queues_sql.txt), copy and paste the SQL code in the file into the Query field on the Custom Query window, and click the Verify button.

    10. Note: This step is required because the database contains data that has been compacted as well as data that has not yet been compacted. The SQL code compacts the data that has not been compacted and adds the newly compacted data to the already compacted data so that all the “bucket” values are the same. For example, let’s say the compacted data is compacted so that the oldest data is contained in 15 minute buckets, but the more recent data is contained in 5 or 10 minute buckets. The SQL code takes the data contained in the 5 and 10 minute buckets and compacts it into 15 minute buckets so that all the data is consistently bucketed.

    Once the SQL script has been verified, the column names display in the Result Columns tab.

  10. Click the OK button.

    11. The new query displays under Custom queries in the Available tables in database list on the Views in Connection window.

    viewsinconnection_customqueryadded.png

     

  11. Select your newly added query/view and click the Add button.

    12. The new query displays in the Views in connection list and the associated columns display in the Columns in selected view region.

    viewsinconnection_addcustomquery.png

     

  12. Click the OK button.

    13. The Replace Data Table window displays.

    replacedatatable_sqlstep12.png

     

  13. Select Import data table as the Load Method and click OK.

    14. Your report should display in the TIBCO Spotfire dashboard.

    ems_queues_sql_rpt.png

     

  14. Repeat the above steps using the ems_serverinfo_sql.dxp Spotfire Analysis file and the ems_serverinfo_sql.txt files to create the EMS Server Message Metrics Report.