1 / 17

Extending the Performance Warehouse

Extending the Performance Warehouse. Dennis Kuipers. Topics. Why extend the PW? What are the steps? Accessing External data Creating Customer Tables Loading external data in the PW Integration with Inform/Alerts Presenting the external data. Benefits of Loading external data in the PW .

leonh
Download Presentation

Extending the Performance Warehouse

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Extending the Performance Warehouse Dennis Kuipers

  2. Topics • Why extend the PW? • What are the steps? • Accessing External data • Creating Customer Tables • Loading external data in the PW • Integration with Inform/Alerts • Presenting the external data

  3. Benefits of Loading external data in the PW • Correlation between external and i3 data • Possibility to keep the external data for years • Easy to implement because of the PW API • No extra data maintenance policies because the PW is taking care of your data • Integration with Foresight for building customized reports with external data. • Integration with Alerts

  4. What do we need? • External data from log files, databases or other sources. • Create scripts for gathering data and loading data using PW API • Create performance warehouse customer tables (part of the PW API) • Integrate with Inform/Alerts for gathering/loading data from the monitored server. • Integrate customer tables with Inform/Foresight. • Create a GUI or use Inform/Foresight for displaying the customer data.

  5. External data? • Where to get the data from? Log files, databases, management system or other data sources. • What kind of format/structure does the data have? • What should the customer data model look like? • How often does the data need to get loaded?

  6. Scripts for gathering data and loading data into the PW • Build customized scripts for gathering data from the external data source. This can be shell script, sql statements or other programming language. • The script must translate the customer data to xml format according to the PW API interface. This interface is described in the PW reference guide. • All kind of data can be loaded as long it match with the customized tables in the PW • The scripts can be scheduled by Inform/Alerts

  7. Translating customer data to PW xml format PART I • Customer data: • [2005/10/06 00:01:33] [TEST] [TEST1] TEST2 INFO: Messages 01012.00100 Server TEST12 (1 instance)client 0 messages queues • PW xml data: • <!-- the connection pool name should always be "pw" --><rowsets connection-pool="pw"> <!-- table name and indaction to commit only after all rwos are loaded into PW --> <rowset object="PW_CDTU_QMESSAGE_DATA_T" single-commit="false"> <!-- the list of colums should be kept in the rows creation  <columns> <column> CDTU_TIMESTAMP </column> <column>CDTU_PWII_INSTANCE_ID</column><column>CDTU_TIME</column> <column>CDTU_APPLICATION</column><column>CDTU_HOST</column><column>CDTU_QUEUE</column><column>CDTU_SERVICES</column><column>CDTU_NRMESSAGES_SUM</column></columns> <row rownum="1"><![CDATA[2192005-10-19 00:00:00^ 1510000^ 2192005-10-19 0:00:16^16TEST19TESTING20^21101000.00010^18TESTINGS^110^]]> </row></rowset></rowsets> • A data tag has three values • A number that tells how many digits we have for the length of string value. • A number that has the value of the string length • The data that needs to be loaded

  8. Translating customer data to PW xml format PART II • No new lines in the PW xml file. • No space between the tags • The XML file contains the layout of the customer table only for those columns that needs to be inserted. Columns such as RECEIVED_TIMESTAMP, MINUTES_COUNT_SUM, PWHG_ID are completed by the PW load process. • Each row has its own identifier <row rownum="1">. This tells you how many records need to be loaded. • If you have a look at the CDATA sections of any of the data/prepare files from other products, then the API doc contains info on how this works, which basically breaks down being a single number which defines how many of the next characters are a number, and that number then defines how many of the next characters are the data, and this will then end with a ^ to mark the next single number etc etc, until the end of the data • For the row in the example above(<![CDATA[2212003-08-2811:30:00.0^]]>), the first number is 2, which means the next 2 numbers are a value, which is 2 and 1, so the next 21 characters are the data (2003-08-2811:30:00.0), and then a ^ marks the end of that data. If we had another column this would start after the ^. • A problem that you should note, is that the date/time info that you enter for the custom data is determined to be i3 time by the PW, so to have the correct time correlation in i3, you will need to calculate the actual i3 time (i.e. if you are in a zone that is GMT +2, you will need to deduct 2 hours from the current time (which sounds simple until you realise that you might be collecting data just after midnight on the 1st March 2008, and other such scenarios)).

  9. Create PW customer tables PART I • Use PW API for creating • Tables • Views • Foreign keys • Indexes

  10. Create PW customer tables PART II • Tables for time slice, hourly, daily, weekly and monthly periods are automatically created with the same layout using the DDML documents. • If the table is a statistics table, you must include the following columns: • <column-definition name=”<table shortcut>_TIMESTAMP” data-type=”TIMESTAMP” null=”FALSE” type=”DATE”/> • <column-definition name=”<table shortcut>_MINUTES_COUNT_SUM“ data-type=”INTEGER” data-length=”9” null=”FALSE” type=”SUM” default=”1”/> • <column-definition name=”<table shortcut>_PWHG_ID” data-type=”INTEGER” data-length=”4” null=”FALSE” type=”IDENTIFIER”/> • <column-definition name=”<table shortcut>_PWII_INSTANCE_ID” data-type=”INTEGER” data-length=”9” null=”FALSE”type=”IDENTIFIER”/> • <column-definition name=”<table shortcut>_RECIEVED_TIMESTAMP” data-type=”TIMESTAMP” null=”FALSE” default=”CURRENT TIMESTAMP” type=”DATE”/> • The data is controlled by the PW which means all PW summarize and purge jobs are executed against the customer tables. • Partitions are created for each customer table.

  11. Create PW customer tables PART III Table Create DDML document <tables-definition product="C1"> <table-definition name="PW_CDTU_CUSTOMER_INTDATA_T" type="STATISTICS" filterable="TRUE" oracle-storage-clause="NEXT 4M PCTINCREASE 0"> <column-definition name="CDTU_TIMESTAMP" data-type="TIMESTAMP" null="FALSE" type="DATE"/> <column-definition name="CDTU_PWHG_ID" ……………………………… <index-definition name="IW_CDTU_01_T" unique="FALSE" primary="FALSE" clustered="FALSE" columns-order="CDTU_PWII_INSTANCE_ID ASC CDTU_TIMESTAMP ASC CDTU_TUSCRIPT ASC CDTU_APPLICATION ASC CDTU_QUEUE ASC CDTU_SERVICES ASC" oracle-storage-clause="NEXT 4M PCTINCREASE 0"/> <summary-hour name="PW_CDTU_CUSTOMER_INTDATA_H"/> <summary-day name="PW_CDTU_CUSTOMER_INTDATA_D"/> <summary-week name="PW_CDTU_CUSTOMER_INTDATA_W"/> <summary-month name="PW_CDTU_CUSTOMER_INTDATA_M"/> <baseline name="PW_CDTU_CUSTOMER_INTDATA_B" oracle-additional-clause="NOLOGGING"/> </table-definition> </tables-definition>

  12. Create PW customer tables PART IV • Columns that needs to be summed, averaged, minimumed or maximumed should end with SUM, AVG, MIN or MAX so the PW processes know this columns needs be processed in the hourly, daily, weekly and monthly tables. • The ddml should be saved in products/dbms/files/tables_definition/pw_cd_db_entities.xml • After creating the pw_cd_db_entities.xml file you can run the ./products/pw/bin/install_customer_schema.sh that create the customer table in the PW. As part of this process the partitions are created for tables and indexes. • Create a customized instance in the PW with ./products/pw/bin/install_instance.sh • After the customized instance has been created the instance shows up in the “warehouse status” in the admin point so you can see the customized tables. After the first load the columns of the “warehouse status” get updated. • The column names should start with the name of the table followed by a description of the column.

  13. Custom tables in PW status (ending in CD) related to customized instance and product

  14. Data gets summarized from Slice table (automatically)

  15. Loading the customer data using the http_requestor utility • Use the http_requestor to send the xml file from the remote server to the focalpoint server • Infra\bin\psin_http_requestor.exe -p -x <pw-fp-server> <listener-port> pw-data-load PW < <file with loading data> •  When the load has been successful then the httpd_requestor return a xml message that tells you how many rows are loaded or in case of failure why it fails.

  16. Integrate customized scripts with Inform/Alerts PART I • Two customized matrices : • Load Tuxedo log files -> for loading the xml files into the PW. Runs every 15 minutes. • Get messages -> getting data from tuxedo log files and translating it to PW xml format. During this translation you are able to check the data in front so if the number of messages is breaching the threshold you can trigger an alert.

  17. Conclusions • The customer data can be integrated within the Inform/Foresight customized report view... • The PW controls the customer data. No need for summarize or other customized maintenance. • By integrating the customer data with Inform/Alerts you can have two functionalities in one (check data up front and load data in the PW). • Correlation between customer data and i3 data is very easy because they are in the same database. • Any kind of data can be loaded because the customer is owner over the customized data model. • External data can be saved for years

More Related