If you join to dwh_capacity.storage_dimension, there is a tier field available.
If you want to stay within dwh_inventory, do something like this to get your tier annotation:
select s.id, av.valueIdentifier from dwh_inventory.storage s join dwh_inventory.object_to_annotation o2a on o2a.objectId=s.id join dwh_inventory.annotation_value av on av.id = o2a.annotationValueId and av.annotationType='Tier' Note that this will carefully select the tier at the storage array level. If you have multiple tiers on a single storage array, or if you're using service levels, you will want to do this differently.
If you're not already using it, you will want to take a look at the Database Schema page which is within the Documentation interface on the DWH web portal. I usually have at least one eye on this when working in MySQL.
If you're in dwh_capacity, you can probably use something like this to combine storage with volume:
select * from storage_dimension join volume_dimension on volume_dimension.storageIdentifier=storage_dimension.identifier and storage_dimension.latest=1;
If your annotation is published, it will be a field in the volume table.
If working in dwh_inventory, the join is simpler:
select * from dwh_inventory.storage join dwh_inventory.volume on volume.storageId=storage.id;
But you'll have to join in the annotation_value and object_to_annotaion tables as in the earlier example with tiers.