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
Comments
Post a Comment