Old OCI Object Data vs New OCI Object Data in dwh_capacity

NetApp's OnCommand Insight...

... pre 7.3.3 datasources used the Cloud schema for Object Storage data.

... 7.3.3 + datasources use the Inventory schema for Object Storage data.

Note: In the below queries I say 'select *' but - in practice - only bring back the data you are interested in.

To get dwh_capacity.chargeback_fact data for Object storage pre 7.3.3 a query like this is possible:

SELECT * FROM dwh_capacity.chargeback_fact AS f
WHERE f.resourceType = 'OBJECT' AND f.dateTk = (SELECT MAX(dateTk) FROM chargeback_fact)

With 7.3.3 + we would need to do:

SELECT * FROM dwh_capacity.chargeback_fact AS f
JOIN dwh_capacity.storage_dimension AS s ON s.tk = f.storageTk
WHERE f.dateTk = (SELECT MAX(dateTk) FROM dwh_capacity.chargeback_fact) AND s.tier = 'OBJECT'

Note: This is assuming you have a custom annotation called tier that you assign to Object storage types.

With business entity dimension data (i.e. services and applications) it comes to this:

SELECT *
FROM dwh_capacity.chargeback_fact AS f
JOIN dwh_capacity.business_entity_dimension AS bed ON f.businessentityTk = bed.tk
JOIN storage_dimension AS s ON s.tk = f.storageTk
WHERE s.tier = 'OBJECT'
AND f.dateTk = (SELECT MAX(dateTk) FROM dwh_capacity.chargeback_fact)

~~~

Which is not massively different to the pre 7.3.3 OCI object data query:

SELECT *
FROM dwh_capacity.chargeback_fact AS f
JOIN dwh_capacity.business_entity_dimension AS bed ON f.businessentityTk = bed.tk
WHERE f.resourceType = 'OBJECT'
AND f.dateTk = (SELECT MAX(dateTk) FROM dwh_capacity.chargeback_fact)

And then if you want to UNION the data together (so the old pre 7.3.3 and new post 7.3.3 datasources can continue to operate side by side):

SELECT *
FROM dwh_capacity.chargeback_fact AS f
JOIN dwh_capacity.business_entity_dimension AS bed ON f.businessentityTk = bed.tk
WHERE f.resourceType = 'OBJECT'
AND f.dateTk = (SELECT MAX(dateTk) FROM dwh_capacity.chargeback_fact)

UNION ALL

SELECT *
FROM dwh_capacity.chargeback_fact AS f
JOIN dwh_capacity.business_entity_dimension AS bed ON f.businessentityTk = bed.tk
JOIN storage_dimension AS s ON s.tk = f.storageTk
WHERE s.tier = 'OBJECT'
AND f.dateTk = (SELECT MAX(dateTk) FROM dwh_capacity.chargeback_fact)

Cheers!