Overview of the TMSM Report Viewer
Teradata Multi-System Manager is an application for monitoring and administration of single and multi-system environments. TMSM Report Viewer allows TMSM (Teradata Multi-System Manager) users to create and view reports about the Teradata ecosystem using data collected by TMSM. The TMSM data model is in Appendix E of the Configuration Guide. For more information about TMSM, please read this article.
This article provides information about TMSM reporting capabilities, shows how to create custom reports, and reviews the out-of-the-box reports provided.
TMSM Report Viewer features
The TMSM Report Viewer Portlet provides a number of features to the user:
- This portlet provides the ability to create and save reports in several formats: table, line, bar and pie charts.
- These reports are defined by an administrator and can be made accessible to other users who may use the Report Viewer portlet to display the reports in their individually defined dashbord in Viewpoint.
- This portlet, along with information about the TMSM metadata repository, will provide the ability to create reports that utilize TMSM metadata about different events and resources within the ecosystem that TMSM monitors. As a reminder, the TMSM metadata repository is an instance of the Teradata database.
- The TMSM Report Viewer portlet provides predefined sample reports which can be copied and modified. These reports include:
- "ETL Process Execution time by Teradata System" in Bar chart format
- "Average ETL Process Execution time by week by process by system" in Table format
- "Total ETL process execution time by day" in line chart format
- "Application availability (application count by overall state)" in pie chart format
- The report result set is created by running SQL to extract results from the MSM metadata repository. This SQL is provided for the sample reports. The Administrator will need to create the SQL for other reports. The datamodel for the MSM repository is provided in the MSM Configuration Guide to assist the administrator.
- This portlet provides functionality to validate the report’s SQL code and show execution errors.
- Each each result set is assigned to a temporary table though which the user can view page by page (paginated reports)
- The actual report provides spreadsheet like presentation of data with paging, searching and filtering capabilities. The charting capabilities allow the presentation of the data in different formats such as bar, line and pie
- The TMSM report user can be given a lower priority so as not to impact the overall performance of TMSM as reports are ran against its metadata repository.
Steps to create a custom report
The following steps you through how an Adminstrator will create a report.
- Select MSM Setup in the Admin drop down on top right of Viewpoint portal.
- Expand the Custom Reports menu item on the right and select “Manage Custom Reports”
- Click on “Add” on the top right of the portlet
- Add new report by providing report name, type, description and SQL code then click on Apply
- Click ‘back to portal’ link
- In the user portal open Report Viewer (by going to Add Content, MSM) and find the newly created report via portlet preferences icon. Select report, number of rows to display per page and frequency for refresh.
- Once the preferences have been set, the selected report will be displayed in this instance of the portlet.
- Optionally, the user can add more reports in their dashboard by opening a new instance of the Report Viewer portlet and selecting a new report to display.
- The Administrator can share reports with other users (instructions below).
Report usage examples
- Need to find all alerts of category BATCH (starts with ‘ba’) for application names that start with ‘da’. Report Viewer generates graphical “human form” representation like this one: “Category Column - ba* and ApplicationId Column - da* “ - This request will get translated into where clause: "Category" LIKE ''ba%'' AND "ApplicationId" LIKE ''da%''
- Need to know how many processes of each type are monitored by MSM (example in charting support section)
- Need to see availability history of a set of MSM Applications for a specific time period
- Need to monitor process duration for specific set of processes (example in the charting support section)
How to share reports
The following outlines the steps used to share a report that has been created. This can only be done by a user with TMSM administrator priviledges.
- Log in to Viewpoint as an admin
- Click the share icon on the portlet’s right upper corner (share icon shaded in image below)
- Next, name the portlet for this report
- Select "Role’s Manager" (under Admin on top right) and enable the new portlet by selecting the "Portlets" tab and scrolling down to the "Shared" category and click the "Enable for role" check box
- Select "Portlet Library" (under Admin on top right), scroll down to find the report and check the enable box and click on Apply.
- Go back to the portal and select the new portlet via "Add Content", "Shared" menu item
Chart types available in the Report Viewer
The following provides a brief description of each report type that is available with TMSM Report Viewer (Bar, Line, Table, Pie). This section includes example reports with the SQL code used to produce the resultset and the report output when necessary.
Bar & Line Charts
For Bar and Line charts, the portlet will support large result sets by providing pagination. The example below represents one page of data (the preferences for this report was set to show 10 rows per page).
This example report is for Process Duration for 3 processes (catload, pload, sload)
SELECT prr.RunDate, SUM(CASE WHEN ResourceId LIKE 'load_cat%' THEN prr.TotalRunTime ELSE NULL END) AS "catload", SUM(CASE WHEN ResourceId LIKE 'load_pro%' THEN prr.TotalRunTime ELSE NULL END) AS "pload", SUM(CASE WHEN ResourceId LIKE 'load_sal%' THEN prr.TotalRunTime ELSE NULL END) AS "sload" FROM ( SELECT ResourceId, ResourceType, TDPID, RunDate, SUM(RunTime) TotalRunTime FROM ( SELECT e.ResourceId, e.ResourceType, e.TDPID, e.UOWId, MIN(CAST(e.EventTS AS DATE)) RunDate, (CASE WHEN (MAX(CASE WHEN EventType='START' THEN EventTS END)) (Named RunStart) IS NULL OR (MAX(CASE WHEN EventType='END' THEN EventTS END)) (Named RunEnd) IS NULL THEN NULL ELSE TimestampDiffSeconds( RunStart,RunEnd) END) RunTime FROM TMSMViews.MSMEvent e WHERE EventType IN ('START','END') AND e.UOWId<>'NA' AND ResourceId LIKE 'load%' GROUP BY 1,2,3,4 )pr GROUP BY 1,2,3,4 )prr GROUP BY 1;
Result set data and format:
The first column is type label (either time or name). The next columns are the value series for each measured object where the column titles represent the measured objects (processes in this example).
Pie charts are not paginated and can only accommodate up to 20 rows of data. If the data returned has more than 20 rows the portlet will generate an error.
SELECT ResourceType as name, count(distinct ResourceId) as cnt FROM TMSMViews.MSMEvent GROUP BY 1 WHERE EventDate between current_date - 60 and current_date
Result set format is in name and value pairs.
The Table format report type is shown in a spreadsheet style with pagination.
Example of report code for viewing MSM events:
SELECT r.ResourceCategory, r.ResourceSubCategory, e.EventType, e.EventTS, e.TDPID, e.ResourceId, e.ResourceType, e.JobName, e.JobStep, e.UOWId, e.EventMsg EVENT_MESSAGE, e.UOWHealthAmt, e.UOWHealthStr, e.AlertCode, e.SeverityLvl Condition, e.EcosystemId, e.EventSourceSystem, e.EventSourceUser, e.EventDML, e.AffectedDatabaseName DatabaseName, e.AffectedTableName TableName, e.Optional1, e.Optional2, e.Optional3, e.ApplicationId, e.StateCode State, OREPLACE(e.LogFile,'\','\\') LogFile, e.EventDate FROM MSMEvent e LEFT OUTER JOIN MSMResource r ON r.ResourceId=e.ResourceId;
There are no format restrictions for the Table result sets.
Challenge to Readers and users of TMSM Report Viewer
Only a few sample reports are released with the TMSM product, but we expect that there will be many customers and professional services people who will design clever and useful reports that could be helpful for others. If you are one of those people we request that you either publish your SQL template on DevX or provide the SQL and the explanation to us and we will publish for you on the Teradata Developer Exchange.