Using the OCI DWH to Calculate Storage Efficiency Figures

The following post details an SQL query that you can use to get Storage Efficiency figures per aggregate (storage pool) for your NetApp ONTAP systems (both Clustered ONTAP and 7-Mode), from the NetApp OnCommand Insight Data Warehouse.

These figures should be pretty close to what you see in ActiveIQ - they won't be perfect but close. You need to remember:

  1. We have compaction figure at an aggregate level - potentially could be different compaction ratio for different internal volumes.
  2. We have deduplication and compression ratio for the whole internal volume and not just the dataUsed bit (data used not including snapshot) - potentially could have different ratios for the dataUsed bit and the snapshot bit.
Image: NetApp Active IQ > Capacity and Efficiency > Storage Efficiency > Node > Savings without Snapshot Backups

The SQL Query

`Logical Used MB`/`Physical Used MB` AS 'Storage Efficiency'
  WHEN s.`microcodeVersion` like '%7-Mode' THEN '7-Mode'
  WHEN s.`microcodeVersion` like '%ONTAP' THEN 'ONTAP'
 END AS 'Mode',
 spd.storageName AS 'Storage', AS 'Aggregate',
 COUNT(*) AS 'Vol Count',
 SUM(i.dataUsedCapacityMB) AS 'DataUsed Minus Snapshots MB',
 FLOOR(SUM(i.dataUsedCapacityMB) * COALESCE(spcf.compactionRatio,1)) AS 'Physical Used MB',
   WHEN i.`dedupeRatio` IS NOT NULL AND i.`compressionRatio` IS NOT NULL
      WHEN s.`microcodeVersion` like '%7-Mode'
       THEN (i.`dataUsedCapacityMB` * 1/(i.`dedupeRatio` * i.`compressionRatio`))
      ELSE (i.`dataUsedCapacityMB` * 1/(i.`dedupeRatio` + i.`compressionRatio` -1))
   WHEN i.`dedupeRatio` IS NOT NULL THEN (i.`dataUsedCapacityMB` * 1/(i.`dedupeRatio`))
   WHEN i.`compressionRatio` IS NOT NULL THEN (i.`dataUsedCapacityMB` * 1/(i.`compressionRatio`))
   ELSE i.`dataUsedCapacityMB`
  END)) AS 'Logical Used MB'
 FROM dwh_capacity.internal_volume_capacity_fact AS i
 JOIN dwh_capacity.date_dimension AS dd ON = i.dateTk
 JOIN dwh_capacity.storage_pool_dimension AS spd ON = i.storagePoolTk
 JOIN dwh_capacity.storage_and_storage_pool_capacity_fact AS spcf ON i.storagePoolTk = spcf.storagePoolTK AND i.dateTk = spcf.dateTk
 JOIN dwh_capacity.storage_dimension AS s ON = spcf.storageTk
 WHERE dd.latest = 1
 AND s.manufacturer = 'NetApp'
 GROUP BY i.storagePoolTk
) AS t0
WHERE `Vol Count` > 1
AND `Aggregate` NOT LIKE '%_ROOT'