I'm not totally sure when FabricPool data first appeared in the OCI 7.3.x DWH. I was under the impression it would need 7.3.11, but this is not so (the below works in 7.3.10). So did 7.3 Service Pack 9 enable collecting (and subsequent reporting) of object tiering data? I think so (essentially an ONTAP datasource patch). All I can say for sure is that you'll definitely be able to see 'objectStoreUsedSpace' in data acquired from operational OCI servers running 7.3.10 with SP9.
The below simple MySQL query will extract the NetApp Internal Volume Identifier, Snapshot Used by the Internal Volume, and objectStoreUsedSpace / internal volume space tiered to FabricPool, from the OCI DataWarehouse database.
iv.identifier AS 'Internal Volume',
FLOOR(iv.snapshotUsedCapacityMB / 1024) AS 'Snapshot Used GB',
ed.fieldValue AS 'Tiered GB'
The above is just to demonstrate how to get the 'objectStoreUsedSpace' data. The data comes from dwh_inventory.extended_data. In this situation we are only tiering snapshots to FabricPool, so just interested in getting 'Snapshot Used GB' from internal_volume, to compare with how many GB is tiered.
What Else is in dwh_inventory.extended_data?
Whilst I'm on the subject of dwh_inventory.extended_data, I might as well show what other fields are available (in 7.3.10 SP9).
The below comes from the output of -
SELECT DISTINCT objectType,fieldName FROM dwh_inventory.extended_data WHERE objectType != 'SWITCH';
- skipping SWITCH because it's just line cards.
objectType | fieldName
STORAGE_NODE | managementIpAddresses
STORAGE_POOL | isCompactionSavingsEn
STORAGE_POOL | isEncrypted
VOLUME | isEncrypted
VOLUME | storageGroups
DISK | isEncrypted
INTERNAL_VOLUME | comment
INTERNAL_VOLUME | isEncrypted
INTERNAL_VOLUME | objectStoreTieringPol
INTERNAL_VOLUME | junctionPath
INTERNAL_VOLUME | qosLimitIOPS
INTERNAL_VOLUME | qosLimitRaw
INTERNAL_VOLUME | qosPolicy
INTERNAL_VOLUME | objectStoreUsedSpace
Extending this to Get Total Tiered Data for Aggregate
I was asked to see if I could get ActiveIQs FabricPool 'Total Tiered Data' from the OCI DWH data. You have to remember that it's always going to be a little bit out. ActiveIQ normally downloads on a Sunday, the dwh_inventory data is only as old as the last complete ETL. Also, important note is that the units in ActiveIQ are actually TB. Anyway, this below seemed to come to a reasonable accurate calculation.We take the sum of 'objectStoreUsedSpace' for all internal volumes on an aggregate and then multiply them by the compression factor (not totally sure why this works):
sp.name AS 'Aggregate',
(sp.totalAllocatedCapacityMB / (1024 * 1024)) AS 'Aggr Allocated TB',
(sp.dataUsedCapacityMB / (1024 * 1024)) AS 'Aggr Used TB',
spcf.dedupeRatio AS 'Aggr Dedupe Ratio',
spcf.compressionRatio AS 'Aggr Compression Ratio',
spcf.compactionRatio AS 'Aggr Compaction Ratio',
SUM(ed.fieldValue) / 1024 AS 'Tiered TB',
SUM(ed.fieldValue) * spcf.compressionRatio / 1024 AS 'Tiered TB * Compression'
FROM dwh_inventory.extended_data AS ed
JOIN dwh_inventory.internal_volume AS iv ON iv.id = ed.objectid
JOIN dwh_inventory.storage_pool AS sp ON sp.id = iv.storagePoolId
JOIN dwh_capacity.storage_pool_dimension AS spd ON spd.id = sp.id
JOIN dwh_capacity.storage_and_storage_pool_capacity_fact AS spcf ON spd.tk = spcf.storagePoolTk
JOIN dwh_capacity.date_dimension AS dd ON dd.tk = spcf.dateTk
WHERE ed.objectType = 'INTERNAL_VOLUME'
AND ed.fieldName = 'objectStoreUsedSpace'
AND dd.latest = 1
GROUP BY sp.id;