Solved: Netapp Volume and Aggregate reporting

wbennett5 · ‎2014-05-12

Trying to run a DWH report to build Netapp Filer Volume and Aggregate reports but not proving to be simple task. The Internal Volume dimension is fine for building a Volume report as long as you do some calculated fields but Aggregate is another matter.

I'm sure there's dB related reason but getting the 'used' data value for a Volume is not simple and when it comes to Aggregates I can't see anyway to get these figures.

I started down the road of trying to use Joins to pull the SQL query from say the Inventory dimension and use with Internal Volume or vice versa but that didn't go so well and I'm not even close to being knowledgable about how these Joins work.

At this point I'll just build separate Volume reports which is easy but the one that still really annoys me is the Aggregate report as there's no way I can tell to build a report say from Inventory dimension that'll have the actually consume data amount of the volume, not what's allocated to data but what's actually used. Appreciate any input or insight...

ketsia · ‎2014-05-16

Hi Wayne,

It looks like you're looking to do an overcommit report with utilization, see below. Note that this combines Storage and Storage Pool Capacity with Internal Volume Capacity:

SELECT dwh_capacity.storage_dimension.name AS 'Storage System Name',

dwh_capacity.storage_pool_dimension.name AS 'Storage Pool Name',

dwh_capacity.storage_and_storage_pool_capacity_fact.rawCapacityMB/1024 AS 'Aggregate Total Raw Space (GB)',

dwh_capacity.storage_and_storage_pool_capacity_fact.capacityMB/1024 AS 'Aggregate Total Space (GB)',

dwh_capacity.storage_and_storage_pool_capacity_fact.usedCapacityMB/1024 AS 'Aggregate Used Space (GB)',

dwh_capacity.storage_and_storage_pool_capacity_fact.usedcapacityMB/dwh_capacity.storage_and_storage_pool_capacity_fact.capacityMB AS 'Aggregate Used Space (%)',

(dwh_capacity.storage_and_storage_pool_capacity_fact.capacityMB - dwh_capacity.storage_and_storage_pool_capacity_fact.usedCapacityMB)/1024 AS 'Aggregate Available Space (GB)',

SUM(dwh_capacity.internal_volume_capacity_fact.allocatedCapacityMB)/1024 AS 'Provisioned Volume Capacity (GB)',

(SUM(dwh_capacity.internal_volume_capacity_fact.allocatedCapacityMB)/dwh_capacity.storage_and_storage_pool_capacity_fact.capacityMB) AS 'Over-Commit Ratio (%)'

FROM dwh_capacity.storage_and_storage_pool_capacity_fact

JOIN dwh_capacity.storage_dimension

ON dwh_capacity.storage_and_storage_pool_capacity_fact.storageTk = dwh_capacity.storage_dimension.tk

AND dwh_capacity.storage_dimension.manufacturer = 'NetApp'

JOIN dwh_capacity.storage_pool_dimension

ON dwh_capacity.storage_and_storage_pool_capacity_fact.storagePoolTk = dwh_capacity.storage_pool_dimension.tk

AND dwh_capacity.storage_pool_dimension.name <> 'N/A'

JOIN dwh_capacity.date_dimension

ON dwh_capacity.storage_and_storage_pool_capacity_fact.dateTk = dwh_capacity.date_dimension.Tk

AND dwh_capacity.date_dimension.latest = 1

JOIN dwh_capacity.internal_volume_capacity_fact

ON dwh_capacity.storage_pool_dimension.tk = dwh_capacity.internal_volume_capacity_fact.storagePoolTk

AND dwh_capacity.date_dimension.tk = dwh_capacity.internal_volume_capacity_fact.dateTk

GROUP BY

dwh_capacity.storage_pool_dimension.tk

Unfortunately, 6.4.x did not come with the capability to aggregate the flexvol capacity in the Storage and Storage Pool Capacity data mart by default. As a result, we had to use the above as a workaround. We've then added this in our 7.0 release so that you can easily drag and drop the data without having to do any SQL.

View solution in original post

ostiguy · ‎2014-05-13

Hey Wayne,

Summing the used capacity of either OCI volumes (LUNs in Ontap), or OCI Internal Volumes (Flexvols in Ontap) will not necessarily sum to the used capacity of a storage pool (Aggregate in Ontap) for NetApp arrays running Ontap, or any other storage platform.

In the world of Ontap, if the flexvol is thick provisioned, the delta between its "used" capacity and capacity is "trapped" in that flevol - so that space should not be considered free/unused at the storage pool level as it is truly not free for you to perform more writes to.

Likewise, some storage platforms have overhead - the CX4 and first generation VNX suffered from this - the allocation unit on their thin provisioning pools was 1 GB, and I believe every thin lun has a 1GB metadata overhead, so again, the used nor capacity values of volumes may not sum to what you would expect at the storage pool level due to this overhead.

Our general recommendation for storage pool use cases is the Storage Pool datamart - this mart additionally has history, so you can not only build point in time reports, but historical trending reports as well

wbennett5 · ‎2014-05-15

Hi ostiguy,

Yeah I guess for some background we've just upgraded from v6.1.2 to v6.4.3 so a LOT of changes which don't show up until you start going into the new Datamarts and where as before Inventory could provide details on Internal Volume and Pool dimensions seems like not the case in latest versions. Understand the change just have to work around... the point about the #'s I understand and it's not so much being able to use the numbers to compare usage or figures in Volumes against Aggregate but more so to have both Aggregate and Volume data presented in a single report. I've looked at the 'join' option to pull data across data marts and think I'll need a few more years with the tool before I can do that easily... probably needs to be a separate thread as to how to best report data across datamarts in a single report.

So would the answer I'm looking for is under current datamarts presenting Volume and Aggregate data in a single report will need to utilise advance cross datamart techniques??

ketsia · ‎2014-05-16