Hey Don,
If your team is going to be doing things with the OCI DWH, they probably will want to be able to look at our schema documentation:
https://localhost/dwh/faces/docs/documentation.jsp
Change localhost to the IP/hostname of your DWH server.
The Java client is not necessarily showing information that will match the OCI DWH.
#1 - The Java client is near real time, the DWH will show you how the world looked at the time of the ETL
#2. The Java client may be doing some math.
That isn't to say that you might not be able to get to where you want to be.
Because I am simple, I am going to try to answer your questions for the dwh_inventory mart. The inventory mart gets wiped out every night, and recreated - therefore, you can do no historical trending on it. To do trending, you will want to use the storage and storage pool mart in dwh_capacity.
You are on the right path for the first 2.
Over committed capacity is a tricky topic:
In OCI, you are generally going to have a storage pool - a volume or internal volume *has* to sit on a storage pool.
But:
You can perform thin provisioning at the volume level - Pure storage is such a platform - thin provisioned block volumes living on a storage pool.
You can perform thin provisioning at the internal volume level - Ontap, Celerra / VNX for File
You can even do both with Ontap - thin LUNs (OCI volumes) sitting on thin FlexVols (OCI internal volumes) sitting on a storage pool.
So, you may want to determine internally how your organization wants to look at over commitment risk.
100% block only environments with OCI likely have no internal volumes whatsover - so the natural behavior is to look at summing volume sizes by what storage pool they sit on, and calculate overcommitment risk.
Ontap environments tend to sum the capacity of internal volumes and compare that to the storage pool numbers.
It is also possible in SQL to do some very complex things - like determining storage pool by storage pool, what platform you are on, and using either the sum of the volumes or the sum of internal volumes depending on the platform/vendor/manufacturer of the array that has the storage pool
For storage pool free capacity, I think totalallocatedcapacity - totalusedcapacity is what you are looking for, but right now I don't have the cleanest environment to look at