[OCI][MySQL] Raw Capacity from dwh_capacity (with an exception for EMC ECS)

Change highlighted bit to change the day, or can use the following to get the latest:

WHERE f.dateTk = (SELECT MAX(dateTk) FROM dwh_capacity.storage_and_storage_pool_capacity_fact)

The SQL Query:

SELECT * FROM(
SELECT
  s.manufacturer,
  s.family,
  SUM(rawCapacityMB) / 1048576 AS rawTB
FROM dwh_capacity.storage_and_storage_pool_capacity_fact f
JOIN dwh_capacity.storage_dimension s ON f.storageTk = s.tk
WHERE f.dateTk = (SELECT tk FROM dwh_capacity.date_dimension WHERE fullDate LIKE '2022-04-06%' GROUP BY dayInMonth)
AND family != 'ECS'
GROUP BY s.family) AS t0
UNION
SELECT * FROM(
SELECT
  s.manufacturer,
  s.family,
  SUM(f.capacityMB) / 1048576 AS rawTB
FROM dwh_capacity.disk_group_capacity_fact AS f
JOIN dwh_capacity.storage_dimension s ON f.storageTk = s.tk
WHERE f.dateTk = (SELECT tk FROM dwh_capacity.date_dimension WHERE fullDate LIKE '2022-04-06%' GROUP BY dayInMonth)
AND s.family = 'ECS'
GROUP BY s.family) AS t1
ORDER BY 3 DESC;

Continued ...

If you don't need an exception for  ECS, then it is simply:

SELECT
  s.manufacturer,
  s.family,
  SUM(rawCapacityMB) / 1048576 AS rawTB
FROM dwh_capacity.storage_and_storage_pool_capacity_fact f
JOIN dwh_capacity.storage_dimension s ON f.storageTk = s.tk
WHERE f.dateTk = (SELECT tk FROM dwh_capacity.date_dimension WHERE fullDate LIKE '2022-04-01%' GROUP BY dayInMonth)
GROUP BY s.family

ORDER BY 3 DESC

VMs

For licensing queries (i.e. why is the cost more this year than last) you may also want to look at virtual machines (especially for CI.)

SELECT os,count(*)

FROM(

SELECT vd.os

FROM vm_capacity_fact AS vf

JOIN vm_dimension AS vd ON vd.tk = vf.vmTk

WHERE vf.dateTk = (SELECT tk FROM dwh_capacity.date_dimension WHERE fullDate LIKE '2022-04-01%' GROUP BY dayInMonth) GROUP BY vmTk

) AS t

GROUP BY os

ORDER BY 2 DESC