Dating Custom Objects in the OCI DWH

Key information of how dates work in the NetApp OnCommand Insight Data Warehouse is available at these two links:

How historical data is retained in Data Warehouse
Data retention, ETL, and time periods

Ignoring Performance Marts, the default retention is as thus:

Capacity marts: All (except individual volumes) : Daily for 13 months
Capacity marts: All (except individual volumes) : Monthly representative for 14 months and beyond
Inventory marts: Individual volumes: Current state for 1 day (or until next ETL)

After 13 months (which is configurable), Data Warehouse retains only one record per month instead of one record per day for capacity, performance, and resource data in the following fact tables:

- Chargeback fact table (dwh_capacity.chargeback_fact)

- File System Utilization fact table (dwh_fs_util.fs_util_fact)

- Host fact table (dwh_sa.sa_host_fact)

- Internal Volume Capacity fact table (dwh_capacity.internal_volume_capacity_fact)

- Ports fact table (dwh_ports.ports_fact)

- Qtree Capacity fact table (dwh_capacity.qtree_capacity_fact)

- Storage and Storage Pool Capacity fact table (dwh_capacity.storage_and_storage_pool_capacity_fact)

- Volume Capacity fact table (dwh_capacity.vm_capacity_fact)

- Storage Node Hourly Performance (storage_node_hourly_performance_fact) and Storage Node Daily Performance (storage_node_daily_performance_fact) fact tables

Your custom objects need to be dated daily (at best) and tie to TK in the date_dimension view (create a entry per row of your custom table with dateTK).

Note: The date_dimension view exists in the following schemas:

dwh_capacity, dwh_capacity_efficiency, dwh_performance

Also note that the date_dimension views are exactly the same in dwh_capacity, dwh_capacity_efficiency, dwh_performance - as you would expect.

You can confirm this with the following MySQL queries:

select count(*) from dwh_capacity.date_dimension;
select count(*) from dwh_capacity_efficiency.date_dimension;
select count(*) from dwh_performance.date_dimension;

The counts will all be the same.