OCI query for historical data

[gaurav_verma]

Hello, 

I am looking to build SQL query to see Cluster's Used, Unused and Allocated capacity from past 7 weeks. One pointer for each week. 

I am able to generate for current date from view "report_storage_summary_aux" but dont know how to use fact tables. 

SELECT storage_name, storage_pool_used_capacity 

FROM report_storage_summary_aux

WHERE storage_name LIKE 'Cluster1' AND storage_pool_used_capacity <> 0  OR

storage_name LIKE 'Cluster2' AND storage_pool_used_capacity <> 0 

Thanks

Gaurav

[gaurav_verma · Accepted solution]

For others visibility. This query works for me. 

SELECT DISTINCT STORAGE.name, STORAGE.dataCenter AS DC, STORAGE.family, DATEDIM.fullDate AS Date,sum(STORAGE_FACT.CapacityMB) AS CapacityMB, sum(STORAGE_FACT.usedCapacityMB) AS usedCapacityMB 
FROM storage_dimension STORAGE, date_dimension DATEDIM, storage_and_storage_pool_capacity_fact STORAGE_FACT
WHERE 
STORAGE_FACT.storageTk=STORAGE.tk AND 
STORAGE.dataCenter IN('DC1','DC2','DC3') AND 
DATEDIM.tk=STORAGE_FACT.dateTk AND 
DATEDIM.fullDate>(DATE_ADD(CURDATE(),INTERVAL -30 DAY)) AND
GROUP BY STORAGE.name, DATEDIM.fullDate

Thanks