OCI --> ETL ..-> OCDWH --> Historical data for performace

[mmodi]

I was wondering if someone can help acccess historical performance data from OnCommand DataWarehouse.

We hace access to following data marts and can't seem to access any performance data past one day using the table - "internal_volume_daily_performance_fact".

I am looking to trend IOPS/MB/Latency for the past daily stats over a year.

mysql> show databases;
+-------------------------+
| Database |
+-------------------------+
| information_schema |
| dwh_capacity |
| dwh_capacity_efficiency |
| dwh_fs_util |
| dwh_inventory |
| dwh_performance |
| dwh_ports |
| dwh_reports |
| dwh_sa |
| test |
| tmp |
+-------------------------+

mysql> use dwh_performance;
Database changed
mysql> show tables;
+--------------------------------------------+
| Tables_in_dwh_performance |
+--------------------------------------------+
| application_dimension |
| application_group_bridge |
| application_group_dimension |
| application_volume_hourly_performance_fact |
| business_entity_dimension |
| capacity_volume_fact_current |
| connected_device_dimension |
| date_dimension |
| disk_daily_performance_fact |
| disk_dimension |
| disk_hourly_performance_fact |
| fabric_dimension |
| host_dimension |
| host_group_bridge |
| host_group_dimension |
| host_vm_daily_performance_fact |
| host_vm_hourly_performance_fact |
| host_volume_hourly_performance_fact |
| internal_volume_daily_performance_fact |
| internal_volume_dimension |
| internal_volume_hourly_performance_fact |
| port_dimension |
| service_level_dimension |
| storage_dimension |
| storage_pool_dimension |
| switch_dimension |
| switch_performance_for_host_hourly_fact |
| switch_performance_for_port_hourly_fact |
| switch_performance_for_storage_hourly_fact |
| switch_performance_for_tape_hourly_fact |
| tape_dimension |
| tier_dimension |
| time_dimension |
| vm_daily_performance_fact |
| vm_dimension |
| vm_hourly_performance_fact |
| volume_daily_performance_fact |
| volume_dimension |
| volume_hourly_performance_fact |
+--------------------------------------------+

Further, VMware Virtual Machine, ESX and ESXi VM capacity and Performance is available via table in mysql.  Its not currently available via the drag and drop datamarts.

  Data Warehouse VM/ESX “Hourly” metrics available up to two weeks, 13 months for “Daily” metrics.

How do I access the 13 months for “Daily” metrics?

Thanks

Modi

[moechnig]

dwh_inventory is only going to contain a day's worth of data, for the most recent ETL.  You will want to look at the performance mart for this. 

Retention periods for various DWH data are documented in the DWH Administration Guide.  For 7.0.1:  https://library.netapp.com/ecmdocs/ECMP1154892/html/GUID-168DA633-1E68-43E9-B54D-B43F07B17D3B.html  For other versions, consult the corresponding manual. 

[mmodi]

Thanks mate, can you please help determine the performance data mart and fact table that retains daily summaries for volumes and internal volumes for 13 months?

The following table doesn't seem to contain daily for more than a day and is not the inventory data mart -

SELECT * FROM dwh_performance.internal_volume_daily_performance_fact

========= Documentation exceprt - 

Each day when the ETL is processed, the daily averages for the preceding day are calculated and populated within the Data Warehouse.

The daily average is a summary of the 24 data points for the previous day.

The performance data marts retain daily summaries for volumes and internal volumes for 13 months.

Thanks

Modi

[ostiguy · Accepted solution]

You need to look at using the dateTk and timeTk to join the Date and Time dimensions

select 
distinct dd.fullDate 
from 
dwh_performance.internal_volume_daily_performance_fact intvolfact
LEFT JOIN
dwh_performance.date_dimension dd ON
intvolfact.dateTk = dd.tk
;

That should show you the unique fullDate values for which you have internal volume daily performance facts.

Edit: cleanup to make SQL legible. This example is SQL based because I thought that was the approach being taken. You should be able to do something similar in the OCI DWH drag and drop reporting tools - I don't have the object names memorized as I only dabble in reporting

[mmodi]

Excellent, is it possible to list that SQL query as an example?

For end-users like me, I think there should be a very easy drag/drop report studio level way to derive a report for daily stats for past 13 months.

Thanks

Modi