Saturday, 9 May 2020

Application and Capacity Report - NetApp OCI DWH & IBM Cognos

An OCI DWH mini project/task to try.

Mini Project/Task:
Create an Application and Capacity report, to list all applications with their allocated and used data totals.

At first hand the task sounds a little daunting and you are unsure where to start, but it turns out to be quite an easy task as you will see below.

What is the first thing you do?

This is what I would do.

1) Search through ‘Team Content’ to see if there is already an application specific related report.
2) If there is nothing suitable, have a look on the Storage Automation Store > OnCommand Insight > Reports and see if there’s anything usable.
3) If there is, import the report from the ‘Storage Automation Store’ and modify it as per your requirements.

I did not find anything when I searched for content in my lab Cognos Analytics portal.
On the Storage Automation Store, I did find a report that looked very promising: the Application Capacity and Performance report. The report has more than I need, I just need to create an excel report with a list of Applications and their Capacity used. Essentially, if you look at the graphic below, all I need is the left bit of the bottom table.

Image: The Application Capacity and Performance report from Storage Automation Store

I download the report and following the instructions 'How To - Open a report from clipboard: This is an 'how-to-guide' to open a report from clipboard and to copy XML to clipboard in Cognos 11' (this content would be much better in a blog post somewhere ...) I “upload” the report to a test IBM Cognos Analytics instance.

The bit of the table I am interested in is in the graphic below.

Image: Cognos Analytics 11: Application v Capacity table on the report page

In the table, if you click on - #Application#, #Allocated (GB)#, #Used (GB)#, #Used (%)# - you can find out where the data is coming from.

Expression Definitions:
#Application# = [SQL5].[Application]
#Allocated (GB)# = [SQL5].[Allocated (GB)]
#Used (GB)# = [SQL5].[Used (GB)]
#Used (%)# = [SQL5].[Used (GB)] / [SQL5].[Allocated (GB)]

So, we need to look at the SQL5 query. Remember there will be a lot of stuff in that query that is not useful to us (the right side of the table includes performance data which I am not interested in.) And below is that SQL5 SQL query - all 126 lines of it! All we must do now is trim out the stuff we do not want (to be honest, we could just leave it in), and we have got what we wanted.

I have tried to make it clear with colour where each SELECT FROM starts and ends.
GREEN is the top level SELECT FROM.
CYAN is the next level SELECT FROM (nested inside GREEN)
YELLOW is the next level SELECT FROM (nested inside CYAN)
PINK is the final level SELECT FROM (nested inside YELLOW or CYAN - no further nesting’s inside.)

SELECT total.app AS 'Application',
 SUM(total.allocatedGB) AS 'Allocated (GB)',
 SUM(total.usedGB) AS 'Used (GB)',
 SUM(total.totalIops) AS 'Total Average IOPS',
 SUM(total.maxTotalIops) AS 'Peak Total IOPS',
 SUM(total.totalMbps) AS 'Total Average MB/s',
 SUM(total.maxTotalMbps) AS 'Peak Total MB/s',
 CEIL(AVG(total.avgMs)) AS 'Average Latency',
 CEIL(MAX(total.maxMs)) AS 'Peak Latency'
FROM (
 SELECT chargeback.application AS app,
  chargeback.provGB AS allocatedGB,
  chargeback.usedGB AS usedGB,
  performance.totalIops AS totalIops,
  performance.maxTotalIops AS maxTotalIops,
  performance.totalMbps AS totalMbps,
  performance.maxTotalMbps AS maxTotalMbps,
  performance.avgMs AS avgMs,
  performance.maxMs AS maxMs
 FROM (
  SELECT      vmChargeback.appTk,
   vmChargeback.application,
   SUM(vmChargeback.provGB) provGB,
   SUM(vmChargeback.usedGB) usedGB
  FROM (            
   SELECT      IFNULL(dwh_inventory.application.id, 'N/A') appTk,
    IFNULL(dwh_inventory.application.name, 'N/A') application,
    ROUND(SUM(dwh_inventory.hv_virtual_disk.capacityMB)/1024, 2) provGB,
    ROUND(SUM(dwh_inventory.hv_virtual_disk.usedCapacityMB)/1024, 2) usedGB
   FROM       dwh_inventory.hv_virtual_disk
   JOIN       dwh_inventory.hv_virtual_machine_to_disk
   ON dwh_inventory.hv_virtual_disk.id = dwh_inventory.hv_virtual_machine_to_disk.virtualDiskId
   JOIN       dwh_inventory.hv_virtual_machine
   ON dwh_inventory.hv_virtual_machine_to_disk.virtualMachineId = dwh_inventory.hv_virtual_machine.id
   LEFT JOIN   dwh_inventory.vm_to_application
   ON dwh_inventory.hv_virtual_machine.id = dwh_inventory.vm_to_application.vmId
   LEFT JOIN   dwh_inventory.application
   ON dwh_inventory.vm_to_application.applicationId = dwh_inventory.application.id
   GROUP BY
    dwh_inventory.application.id
   UNION
   SELECT      dwh_capacity.application_dimension.id appTk,
    dwh_capacity.application_dimension.name application,
    ROUND(SUM(dwh_capacity.chargeback_fact.provisionedCapacityMB)/1024, 2) provGB,
    ROUND(SUM(dwh_capacity.chargeback_fact.usedCapacityMB)/1024, 2) usedGB
   FROM       dwh_capacity.chargeback_fact
   JOIN       dwh_capacity.application_dimension
   ON dwh_capacity.chargeback_fact.applicationTk = dwh_capacity.application_dimension.tk
   AND dwh_capacity.chargeback_fact.mappedByVM = 0
   JOIN       dwh_capacity.date_dimension
   ON dwh_capacity.chargeback_fact.dateTk = dwh_capacity.date_dimension.tk
   AND dwh_capacity.date_dimension.latest = 1
   GROUP BY
    dwh_capacity.application_dimension.id
   ) vmChargeback
  GROUP BY
       vmChargeback.appTk
  ) chargeback
 LEFT JOIN     ( ## Performance Section START
  SELECT      perf.appTk,
   CEIL(AVG(perf.totalIops)) AS totalIops,
   MAX(perf.totalIops) AS maxTotalIops,
   CEIL(AVG(perf.totalMbps)) AS totalMbps,
   MAX(perf.totalMbps) AS maxTotalMbps,
   ROUND(AVG(perf.avgMs), 2) AS avgMs,
   MAX(perf.maxMs) AS maxMs
  FROM (
   SELECT      dwh_inventory.application.id appTk,
    dwh_performance.time_dimension.hourDateTime,
    ROUND(SUM(dwh_performance.vm_hourly_performance_fact.totalIops),0) totalIops,
    ROUND(SUM(dwh_performance.vm_hourly_performance_fact.totalThroughput),0) totalMbps,
    ROUND(AVG(dwh_performance.vm_hourly_performance_fact.totalResponseTime),0) avgMs,
    ROUND(MAX(dwh_performance.vm_hourly_performance_fact.totalResponseTime),0) maxMs
   FROM       dwh_performance.vm_hourly_performance_fact
   JOIN       dwh_performance.time_dimension
   ON dwh_performance.vm_hourly_performance_fact.timeTk = dwh_performance.time_dimension.tk
   JOIN       dwh_performance.vm_dimension
   ON dwh_performance.vm_hourly_performance_fact.vmTk = dwh_performance.vm_dimension.tk
   AND dwh_performance.vm_dimension.latest = 1
   JOIN       dwh_inventory.vm_to_application
   ON dwh_performance.vm_dimension.id = dwh_inventory.vm_to_application.vmId
   JOIN       dwh_inventory.application
   ON dwh_inventory.vm_to_application.applicationId = dwh_inventory.application.id
   GROUP BY
    dwh_inventory.application.id,
    dwh_performance.time_dimension.tk
   UNION
   SELECT      dwh_performance.application_dimension.id appTk,
    dwh_performance.time_dimension.hourDateTime,
    ROUND(SUM(dwh_performance.volume_hourly_performance_fact.totalIops),0) totalIops,
    ROUND(SUM(dwh_performance.volume_hourly_performance_fact.totalThroughput),0) totalMbps,
    ROUND(AVG(dwh_performance.volume_hourly_performance_fact.totalResponseTime),0) avgMs,
    ROUND(MAX(dwh_performance.volume_hourly_performance_fact.totalResponseTime),0) maxMs
   FROM       dwh_performance.volume_hourly_performance_fact
   JOIN       dwh_performance.time_dimension
   ON dwh_performance.volume_hourly_performance_fact.timeTk = dwh_performance.time_dimension.tk
   JOIN       dwh_performance.application_dimension
   ON dwh_performance.volume_hourly_performance_fact.applicationTk = dwh_performance.application_dimension.tk
   GROUP BY
    dwh_performance.application_dimension.id,
    dwh_performance.time_dimension.tk
   UNION
   SELECT      dwh_performance.application_dimension.id appTk,
    dwh_performance.time_dimension.hourDateTime,
    ROUND(SUM(dwh_performance.internal_volume_hourly_performance_fact.totalIops),0) totalIops,
    ROUND(SUM(dwh_performance.internal_volume_hourly_performance_fact.totalThroughput),0) totalMbps,
    ROUND(AVG(dwh_performance.internal_volume_hourly_performance_fact.totalResponseTime),0) avgMs,
    ROUND(MAX(dwh_performance.internal_volume_hourly_performance_fact.totalResponseTime),0) maxMs
   FROM       dwh_performance.internal_volume_hourly_performance_fact
   JOIN       dwh_performance.time_dimension
   ON dwh_performance.internal_volume_hourly_performance_fact.timeTk = dwh_performance.time_dimension.tk
   JOIN       dwh_performance.application_dimension
   ON dwh_performance.internal_volume_hourly_performance_fact.applicationTk = dwh_performance.application_dimension.tk
   GROUP BY
    dwh_performance.application_dimension.id,
    dwh_performance.time_dimension.tk      
   ) perf
  GROUP BY
   perf.appTk
  ) performance ## Performance Section END
 ON chargeback.appTk = performance.appTk
 GROUP BY
  chargeback.appTk
 ) total
GROUP BY
 total.app

Since we do not need any of the performance data, we can get rid of the entire performance section, and any lines with performance in them. Which makes the query I was looking for 51 lines instead of 126. As below:

SELECT total.app AS 'Application',
 SUM(total.allocatedGB) AS 'Allocated (GB)',
 SUM(total.usedGB) AS 'Used (GB)'
FROM (
 SELECT chargeback.application AS app,
  chargeback.provGB AS allocatedGB,
  chargeback.usedGB AS usedGB
 FROM (
  SELECT      vmChargeback.appTk,
   vmChargeback.application,
   SUM(vmChargeback.provGB) provGB,
   SUM(vmChargeback.usedGB) usedGB
  FROM (            
   SELECT      IFNULL(dwh_inventory.application.id, 'N/A') appTk,
    IFNULL(dwh_inventory.application.name, 'N/A') application,
    ROUND(SUM(dwh_inventory.hv_virtual_disk.capacityMB)/1024, 2) provGB,
    ROUND(SUM(dwh_inventory.hv_virtual_disk.usedCapacityMB)/1024, 2) usedGB
   FROM       dwh_inventory.hv_virtual_disk
   JOIN       dwh_inventory.hv_virtual_machine_to_disk
   ON dwh_inventory.hv_virtual_disk.id = dwh_inventory.hv_virtual_machine_to_disk.virtualDiskId
   JOIN       dwh_inventory.hv_virtual_machine
   ON dwh_inventory.hv_virtual_machine_to_disk.virtualMachineId = dwh_inventory.hv_virtual_machine.id
   LEFT JOIN   dwh_inventory.vm_to_application
   ON dwh_inventory.hv_virtual_machine.id = dwh_inventory.vm_to_application.vmId
   LEFT JOIN   dwh_inventory.application
   ON dwh_inventory.vm_to_application.applicationId = dwh_inventory.application.id
   GROUP BY
    dwh_inventory.application.id
   UNION
   SELECT      dwh_capacity.application_dimension.id appTk,
    dwh_capacity.application_dimension.name application,
    ROUND(SUM(dwh_capacity.chargeback_fact.provisionedCapacityMB)/1024, 2) provGB,
    ROUND(SUM(dwh_capacity.chargeback_fact.usedCapacityMB)/1024, 2) usedGB
   FROM       dwh_capacity.chargeback_fact
   JOIN       dwh_capacity.application_dimension
   ON dwh_capacity.chargeback_fact.applicationTk = dwh_capacity.application_dimension.tk
   AND dwh_capacity.chargeback_fact.mappedByVM = 0
   JOIN       dwh_capacity.date_dimension
   ON dwh_capacity.chargeback_fact.dateTk = dwh_capacity.date_dimension.tk
   AND dwh_capacity.date_dimension.latest = 1
   GROUP BY
    dwh_capacity.application_dimension.id
   ) vmChargeback
  GROUP BY
       vmChargeback.appTk
  ) chargeback
 GROUP BY
  chargeback.appTk
 ) total
GROUP BY
 total.app

And ‘Miracle of Miracles’, the SQL query works perfectly on my LAB DWH. The below image is using MySQL command line.

Image: OCI LAB DWH output of Application with Allocated (GB) and Used (GB)

All that is left to do is the usual IBM Cognos activities to turn it into a report in IBM Cognos.

THE END

No comments:

Post a comment