Heleen Snelting

This article has been kindly and expertly contributed by Spotfire user Paul Hallimond and reviewed by the Spotfire team. If you are a Spotfire user interested in contributing community articles about solutions that you developed that can help other Spotfire users, please contact community@spotfire.com and we will help publish your content! Problem Statement In Spotfire, you can create scheduled updates to cache dashboards in RAM so that very large dashboards containing millions of rows of data load quickly and are responsive to the users. Platform Administrators need to be able to forecast in advance for new capacity to meet the demands of these dashboards and to be able to flag dashboards that may need to be reworked to improve performance. Out of the box, administrators can see at any given moment the Web Players and how much resources are being consumed by each dashboard on each Web Player, but it’s a manual effort to stitch that together in a large clustered environment and to then trend that data for future capacity planning and reviewing with developers. Solution Description This article explains which log file and elements you need to use to show and trend the size of Spotfire dashboards in your environment and gives a way to show the top 10 such dashboards so you can focus your team's developer resources there. Solution Assumptions This document assumes you are using Spotfire 12.0 LTS or above. Solution Approach Step 1 - Viewing the current consumption of a dashboard cached in RAM To view the size of a dashboard in the administrator console you would follow the below steps. If you open the Scheduling & Routing Activity screen for a given dashboard, you can see the history and for each run the Web Player and instance name it was loaded onto. For example, looking at a particular dashboard below you can see the following runs occurred: If you then want to see the current cache consumption for that dashboard, you would click on the link shown above to see details of what is cached on that web player node: The issue is that the above is just the current cache consumption number and for capacity planning we need to see the trends. Step 2 - Identifying the data to trend the consumption of a dashboard cached in RAM To trend the information, you will need to use information in the Spotfire logs from the Web Player tier. The log file directory on a Web Player node is typically here: [node manager install dir]\logs So for example: e:/tibco/tsnm/12.0.8/nm/logs The key log file we will need is called MemoryStatisticsLog*.txt Details about the information in this log file can be found here: MemoryStatisticsLog (tibco.com) Typically, you may have several of these log files depending on the number of instances on that Web Player node and the size of the logs as they max out and start another one. An example of what you might see is below in a Web Player node with two instances: Within that log file, the key data element we will use is SharedApproximateTotalTableSize which is approximately going to equal the Total Table Data Size column above from the UI. Step 3 - Trending the consumption data in a Spotfire Dashboard In this step, we will create a dashboard that pulls in all the log data to a single dashboard and shows the top 10 dashboards in the entire environment in terms of how much RAM they are consuming across the timespan of all the log data available. For this first visual, centralize all the log files into a single directory and then import all the data into Spotfire Analyst client. Create a line chart visualization of the data as shown below. Make sure you only have the below data element checked in the filtering section For the Cached Document Size convert the data to GB using the below expression Expression value: Max([Value]) / 1024 / 1024 / 1024 as [Cached Document Size (GB)] To only show the top 10 largest Cached Dashboards we need to create a rule. Setup the rule as shown below: Use the below expression for the column field of the rule. Column Expression: DenseRank(MAX([Value]) over (All([Axis.Color])),"desc")<=10 After these settings are applied, you should see a revised version of the chart showing the top 10 cached documents by size in descending order. Step 4 - Interpreting the Cached Document Size Chart Some points to note: As the UI screen states ‘table and view sizes are approximate’ and so the trending data will be too. It is however accurate enough for the capacity planning purposes described herein. Most lines will be pretty stable and grow over time slightly in a calendar year as a dashboard would perhaps show YTD data that grows throughout the year until it resets in January. You can use the data shown here to make sure your developers have enabled rolling time periods and are not just charting an ever-increasing data set. If a line has a sharp increase, chances are the developers have brought in additional data to the dashboard. Some of the dashboards may have additional on-demand sections that pull in additional data to the dashboard. You can see that above with the green and purple lines, and you need to make sure you plan capacity for that accordingly. The data for each chart above is only for the cached version, but, if the analysis file is refreshed via the schedule while in memory, double that capacity will be needed to cache the new instance and the old instance until the refresh is complete. You need to plan that capacity accordingly. Overall use this data to plan capacity requirements and to identify dashboards that could present a problem to the environment and need to be reviewed by developers Step 5 - Automation The steps above give you a one-time view but it was manual to produce. You should consider centralizing your log data perhaps with a solution like Splunk or New Relic and then using that centralized data source as the input to the chart above. Review this community article for more on that topic: Splunk Cache Drop Alerting for Spotfire Scheduled Updates - Spotfire - Spotfire Community