[OCI DWH] Object Chargeback (Recharge) Example (with dwh_inventory)

The below is an example MySQL query that can be used to generate an Object Chargeback/Recharge report from data in the NetApp OnCommand Insight DataWarehouse.

Some of the data (highlighted) comes from custom annotations.

When Object was using the Cloud model, this was not so easy because you could not annotate your buckets. Now in the Inventory schema, things are a lot more simple.

SELECT
    s.country AS 'Country'
  , CONCAT(p.name,':',iv.name) AS 'lobTenantBucket'
  , p.name AS 'Tenant'
  , (date_format(Date(now()),'%Y/%m')) AS 'monthLabel'
  , s.building AS 'datacenter'
  , FLOOR(iv.totalUsedCapacityMB/1024) AS 'Chargeable Capacity GB'
  , FLOOR(iv.totalAllocatedCapacityMB/1024) AS 'Provisioned Capacity GB'
  , FLOOR(iv.totalUsedCapacityMB/1024) AS 'Used Capacity GB'
  , iv.costcode AS 'costCode'
  , s.name AS 'storage'
  , iv.url
FROM
  dwh_inventory.internal_volume AS iv
JOIN
  dwh_inventory.storage AS s ON s.id = iv.storageID
JOIN
  dwh_inventory.storage_pool AS p ON p.id = iv.storagePoolId
WHERE
  iv.tier = 'Object'
  AND FLOOR(iv.totalUsedCapacityMB/1024) > 0