SaaS Reporting Tutorial Entity Metrics Worksheet

We'll show an example of how to create a query referencing data from the "Entity Metrics Worksheet". Specifically, we'll create an Answer that shows the top 50 most utilized VMs by vMem 95th percentile.

1. Login to Thoughtspot.

2. Click on the Search data button on the top.
   

3. On the left, locate the Sources section. Click on the wrench to select a source.
   

4. In the Worksheets section, check the box for the Entity Metrics worksheet. Click Done.
   

5. Let's add some filters first to help speed up the query.

   1. On the left-hand side, hover your mouse over Time and click the filter icon.
      
   2. From the drop-down, select Last. Then fill out 30 and Days. Click Apply.
      
   3. In the query bar at the top, type virtual_machine. A drop-down will appear with suggested filters, click the virtual_machine for Entity Type in Entity Metrics.
      
   4. In the query bar at the top, type vmem. A drop-down will appear with suggested filters, click the vmem for Metrics Type in Entity Metrics.
      

6. Now let's add some columns to our query. On the left-hand side, double-click on the following columns to add them to the query box at the top:

   • Name - the name of the VM
   • Metric Type - the metric name
     
   

7. Now let's plot a few different utilization values.

   1. In the query bar at the top, type average Utilization. A drop-down will appear with suggested values, click the Utilization from Entity Metrics.
      
   2. In the query bar at the top, type max Utilization. A drop-down will appear with suggested values, click the Utilization from Entity Metrics.

8. Now let's add a formula that converts the latest vMem value from KB to GB for better legibility.

   1. On the left-hand sidebar, locate Formulas and click the + button.
      
   2. In the Formula name box, type Latest Capacity (GB).
      
   3. In the formula box, type the following: if (group_max(Time) = Time ) then if (Metric Type = 'vmem') then Capacity/1024/1024 else Capacity else null
      
   4. Click Save

9. Now let's add a formula that calculates the 95th Percentile.

   1. On the left-hand sidebar, locate Formulas and click the + button.
      
   2. In the Formula name box, type 95th Percentile.
      
   3. In the formula box, type the following: percentile(Utilization,95,'asc')
      
   4. Click Save

10. Now let's sort and filter to show the Top 50 vMem consumers by 95th percentile. In the query box at the top, type top 50 by 95th Percentile.
    

11. For legibility, let's set the Number format to be a "percentage" for the 95th Percentile column.

    1. Hover over the 95th Percentile column and click the three dots > click Number Format
       
    2. In the right sidebar, in the Category drop down, select Percentage.
       

12. Now let's save this visualization (ThoughtSpot terminology: "Answer"). Click on the More icon (three dots) and select Save.
    

13. Provide a Name and Description for your visualization and then click Save Answer.

    • Name: Top 50 VMs by VMem 95th Percentile
    • Description: Plotting: Max, 95P, Average.
      
    

14. You can export the table as a XLSX, CSV, or PDF by click on the More icon (three dots) and selecting Download > XLSX|CSV|PDF.
    

15. You now have a table showing the Top 50 VMs by vMem utilization. Continue to the optional steps to visualize this on a chart.

16. Optional: Visualization and Legibility Improvements

    1. To visualize the difference between a VM's Maximum, 95th Percentile, and Average vMem values, let's switch to the chart View. Click on the View Chart icon beneath the query bar.
       
    2. A default visualization will appear. Let's make some modifications. Click on the Change visualization icon on the right and select Column.
       
    3. Click on the Edit chart configuration (gear) button on the right.
       
    4. Drag the entities according to the following organization:
       • X-axis
         • Name
       • Y-axis
         • Maximum Utilization
         • 95th Percentile
         • Average Utilization
       • Slice with color
         • Leave empty
       • Not visualized
         • Metric Type
         • Latest Capacity (GB)
           
       
    5. Click Apply
    6. For legibility, if any Y-axis legends are plotted on the right, you can position them on the left by clicking on the Y-axis title drop-down > Position > Left
       
    7. For legibility, set the maximum Y-axis values to be 150% (1.5).
       1. Click on one of the y-axis title drop-down menus > Edit.
          
       2. In the Max text box, type 1.5
          
       3. Repeat for all 3 y-axis (Max, 95th Percentile, Average)
    8. For legibility, add X-axis gridlines. Click on the Edit chart configuration (gear) button on the right > select the Settings tab > check the boxes for X-axis gridlines and Y-axis gridlines.
       
    9. Now let's save this visualization again. Click on the More icon (three dots) and select Save.
       

17. Your query and visualization are complete! You can now see the Top 50 VMs by vMem utilization.