In the previous post of this series, we ingested OCI FOCUS cost reports into Autonomous Database. Now, we focus on making cost data interactive and accessible using Oracle Analytics Cloud (OAC).
Why Use OAC for Cost Intelligence Dashboards?
OAC provides several key advantages over traditional cloud billing dashboards:
-
Rich visualizations: Create interactive charts, tables and maps tailored for FinOps.
-
Drill-down capability: Analyze costs by compartment, service, and tag—without writing SQL.
-
Built-in ML insights: Add trend lines, build reports using Natural Language like
Which service had the highest cost last quarter? -
Zero infrastructure overhead: OAC is a fully managed platform.
Enhancing Reports with Compartment Filters and Budget Data
Why Compartment Hierarchy Matters
By default, OCI cost reports only include usage for the specified compartment. They do not include child compartments unless explicitly queried. However, many organizations use compartment hierarchies to segment environments (e.g., Dev, Test, Prod) or teams (Finance, Engineering).
Adding compartment hierarchy data into your analytics model allows you to:
-
Filter and roll up costs across a compartment and its children.
-
Enable FinOps teams to analyze spend by business unit or function.
-
Reduce navigation effort—everything is available in a single dashboard.
Why Budget Data Should Be Included
Budgets in OCI are tracked separately. Analysts trying to identify compartments exceeding their budgets must switch between cost views and budget views.
By joining budget data with cost data:
-
You provide a single-pane-of-glass view.
-
You can create “over budget” flags and visual alerts.
You can learn how to create a function in the OCI Functions service in this guide. You can also use Terraform to build the function—see the details in this reference.
Create Python functions to fetch the latest compartment hierarchy and budget data and execute them to get your reporting data.
Compartment Hierarchy Script
import io
import json
import oci
import os
import csv
import tempfile
from fdk import response
def handler(ctx, data: io.BytesIO = None):
try:
print("Function started", flush=True)
signer = oci.auth.signers.get_resource_principals_signer()
identity_client = oci.identity.IdentityClient(config={}, signer=signer)
object_storage_client = oci.object_storage.ObjectStorageClient(config={}, signer=signer)
# Get tenancy ID from RP context
tenancy_id = "your_tenancy_ocid_here" # Replace with your tenancy OCID
tenancy = identity_client.get_tenancy(tenancy_id).data
root_name = tenancy.name
# Upload to Object Storage
namespace = object_storage_client.get_namespace().data
bucket_name = "your_bucket_name_here" # Replace with your bucket name
object_name = f"compartments_{tenancy.name}.csv"
print(f"Tenancy name: {root_name}", flush=True)
# Fetch all compartments
compartments = []
next_page = None
while True:
resp = identity_client.list_compartments(
compartment_id=tenancy_id,
compartment_id_in_subtree=True,
access_level="ANY",
lifecycle_state=oci.identity.models.Compartment.LIFECYCLE_STATE_ACTIVE,
page=next_page
)
compartments.extend(resp.data)
if resp.has_next_page:
next_page = resp.next_page
else:
break
print(f"Fetched {len(compartments)} compartments", flush=True)
compartment_lookup = {c.id: c for c in compartments}
compartment_lookup[tenancy_id] = tenancy
def build_full_path(compartment_id):
if compartment_id == tenancy_id:
return f"/{root_name}"
parent_id = compartment_lookup[compartment_id].compartment_id
return f"{build_full_path(parent_id)}/{compartment_lookup[compartment_id].name}"
def get_all_descendants(parent_id):
desc = []
for c in compartments:
if c.compartment_id == parent_id:
desc.append((c.name, c.id))
desc.extend(get_all_descendants(c.id))
return desc
csv_data = []
for comp_id, comp_obj in compartment_lookup.items():
comp_name = comp_obj.name
parent_ocid = getattr(comp_obj, 'compartment_id', "N/A")
full_path = build_full_path(comp_id)
csv_data.append({
"Compartment Name": comp_name,
"Compartment OCID": comp_id,
"Parent Compartment OCID": parent_ocid,
"Compartment Path": full_path,
"Descendant Name": comp_name,
"Descendant OCID": comp_id
})
for desc_name, desc_id in get_all_descendants(comp_id):
csv_data.append({
"Compartment Name": comp_name,
"Compartment OCID": comp_id,
"Parent Compartment OCID": parent_ocid,
"Compartment Path": full_path,
"Descendant Name": desc_name,
"Descendant OCID": desc_id
})
print(f"Prepared {len(csv_data)} CSV rows", flush=True)
# Write CSV to temp file
with tempfile.NamedTemporaryFile(delete=False, mode='w', newline='', encoding='utf-8') as tmp_file:
writer = csv.DictWriter(tmp_file, fieldnames=[
"Compartment Name", "Compartment OCID", "Parent Compartment OCID",
"Compartment Path", "Descendant Name", "Descendant OCID"
])
writer.writeheader()
writer.writerows(csv_data)
tmp_file_path = tmp_file.name
print(f"CSV written to temp file {tmp_file_path}", flush=True)
with open(tmp_file_path, 'rb') as file_data:
object_storage_client.put_object(
namespace_name=namespace,
bucket_name=bucket_name,
object_name=object_name,
put_object_body=file_data
)
print(f"Uploaded to Object Storage: {bucket_name}/{object_name}", flush=True)
# Cleanup
os.remove(tmp_file_path)
print("Temp file removed", flush=True)
return response.Response(
ctx,
response_data=json.dumps({
"message": "Compartment hierarchy uploaded to Object Storage",
"bucket": bucket_name,
"object": object_name
}),
headers={"Content-Type": "application/json"}
)
except Exception as e:
print(f"Error: {str(e)}", flush=True)
return response.Response(
ctx,
response_data=json.dumps({"error": str(e)}),
status_code=500,
headers={"Content-Type": "application/json"}
)
Budget Data Script
import oci
import csv
import io
import os
from fdk import response
# --- Object Storage Configuration ---
NAMESPACE = "your_namespace_here" # Replace with your Object Storage namespace
BUCKET_NAME = "your_bucket_name_here" # Replace with your Object Storage bucket name
OBJECT_NAME = "oci_compartment_budgets.csv"
TENANCY_OCID = "your_tenancy_ocid_here" # Replace with your tenancy OCID
def get_all_compartments(identity_client, tenancy_ocid):
compartments = []
try:
root_compartment = identity_client.get_compartment(tenancy_ocid).data
compartments.append(root_compartment)
list_compartments_response = oci.pagination.list_call_get_all_results(
identity_client.list_compartments,
compartment_id=tenancy_ocid,
compartment_id_in_subtree=True,
access_level="ACCESSIBLE",
lifecycle_state="ACTIVE"
)
for compartment in list_compartments_response.data:
if compartment.lifecycle_state == "ACTIVE":
compartments.append(compartment)
except oci.exceptions.ServiceError as e:
print(f"Error listing compartments: {e}", flush=True)
return []
return compartments
def handler(ctx, data: io.BytesIO = None):
try:
signer = oci.auth.signers.get_resource_principals_signer()
identity_client = oci.identity.IdentityClient(config={}, signer=signer)
budget_client = oci.budget.BudgetClient(config={}, signer=signer)
object_storage_client = oci.object_storage.ObjectStorageClient(config={}, signer=signer)
except Exception as e:
print(f"Error initializing OCI clients: {e}", flush=True)
return response.Response(ctx, response_data="Initialization failure", status_code=500)
print("Fetching compartments...", flush=True)
all_compartments = get_all_compartments(identity_client, TENANCY_OCID)
if not all_compartments:
print("No compartments found or error occurred.", flush=True)
return response.Response(ctx, response_data="No compartments found", status_code=200)
budget_data = []
print("Fetching budgets...", flush=True)
for compartment in all_compartments:
try:
list_budgets_response = oci.pagination.list_call_get_all_results(
budget_client.list_budgets,
compartment_id=compartment.id
)
for budget in list_budgets_response.data:
budget_data.append({
"Compartment Name": compartment.name,
"Compartment OCID": compartment.id,
"Budget Name": budget.display_name,
"Budget OCID": budget.id,
"Amount": budget.amount,
"Reset Period": budget.reset_period,
"Target Type": budget.target_type,
"Targets": ", ".join(budget.targets) if budget.targets else "N/A",
"Current Spend": budget.actual_spend,
"Forecasted Spend": budget.forecasted_spend,
"Lifecycle State": budget.lifecycle_state,
"Time Created": budget.time_created.strftime('%Y-%m-%d %H:%M:%S') if budget.time_created else "N/A"
})
except oci.exceptions.ServiceError as e:
print(f"Warning: Could not retrieve budgets for compartment {compartment.name}: {e}", flush=True)
if not budget_data:
print("No budget data found.", flush=True)
return response.Response(ctx, response_data="No budget data found", status_code=200)
# Create CSV
csv_buffer = io.StringIO()
writer = csv.DictWriter(csv_buffer, fieldnames=budget_data[0].keys())
writer.writeheader()
writer.writerows(budget_data)
csv_content = csv_buffer.getvalue()
# Upload to Object Storage
try:
object_storage_client.put_object(
namespace_name=NAMESPACE,
bucket_name=BUCKET_NAME,
object_name=OBJECT_NAME,
put_object_body=csv_content
)
print(f"Uploaded budget report to {BUCKET_NAME}/{OBJECT_NAME}", flush=True)
return response.Response(ctx, response_data="Budget report uploaded successfully.", status_code=200)
except oci.exceptions.ServiceError as e:
print(f"Error uploading to Object Storage: {e}", flush=True)
return response.Response(ctx, response_data="Failed to upload CSV", status_code=500)
Identify Top Cost-Consuming Services with OAC and ADW
One of the most common questions asked by FinOps analysts is: “What are the top consuming services in the last few months?” While you can filter and explore this data through existing Analytics Clouddashboards, this blog introduces a custom view that provides a three month breakdown of service level costs, helping you identify trends and anomalies more easily. This view aggregates OCI cost data and returns the top 25 highest cost services over the past three months. It helps quickly highlight cost spikes and consistent heavy usage patterns. You can deploy this view in your Autonomous Data Warehouse environment set up in Part 2 of this blog series.
CREATE OR REPLACE VIEW TOP_25_EXPENSIVE_SERVICES_V AS
WITH DATE_RANGES AS (
SELECT
ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -2) AS MONTH_1_START,
LEAST(LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -2)), SYSDATE) AS MONTH_1_END,
ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1) AS MONTH_2_START,
LEAST(LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)), SYSDATE) AS MONTH_2_END,
SYSDATE AS MONTH_3_END
FROM dual
),
RAW_COST AS (
SELECT
CHARGEDESCRIPTION,
ChargePeriodStart,
EffectiveCost
FROM
OCI_COST_DATA
),
COST_AGGREGATED AS (
SELECT
rc.CHARGEDESCRIPTION,
SUM(CASE
WHEN rc.ChargePeriodStart BETWEEN dr.MONTH_1_START AND dr.MONTH_1_END
THEN rc.EffectiveCost ELSE 0 END) AS COST_MONTH_1,
SUM(CASE
WHEN rc.ChargePeriodStart BETWEEN dr.MONTH_2_START AND dr.MONTH_2_END
THEN rc.EffectiveCost ELSE 0 END) AS COST_MONTH_2,
SUM(CASE
WHEN rc.ChargePeriodStart BETWEEN TRUNC(SYSDATE, 'MM') AND dr.MONTH_3_END
THEN rc.EffectiveCost ELSE 0 END) AS COST_CURRENT_MONTH
FROM
RAW_COST rc
CROSS JOIN DATE_RANGES dr
GROUP BY
rc.CHARGEDESCRIPTION
),
RANKED_SERVICES AS (
SELECT
ca.*,
RANK() OVER (ORDER BY COST_MONTH_1 + COST_MONTH_2 + COST_CURRENT_MONTH DESC) AS RANK_TOTAL
FROM
COST_AGGREGATED ca
)
SELECT
CHARGEDESCRIPTION,
COST_MONTH_1,
COST_MONTH_2,
COST_CURRENT_MONTH
FROM
RANKED_SERVICES
WHERE
RANK_TOTAL <= 25;
Import and Set Up OAC Dashboards
Importing the DVA file from GitHub gives you baseline OCI cost dashboards. These are fully customizable—you can add charts, change visuals, and use OAC’s AI to generate new insights with natural language.
Step-by-Step: Set Up Database Credentials in OAC for Imported DVA
Step 1: Create OAC Instance – Guide
Step 2: Import the .dva File – Guide
Step 3: Configure the Database Connection in OAC
-
In OAC, navigate to Data → Connections.
-
Locate each connection used by the project
-
For the database connection, click Inspect, enter the client credentials (Wallet) and a username and password that have the right permissions for the Autonomous Data Warehouse, then click Save.
-
To connect to Object Storage, generate an API key in the OAC connections section and add the public key to the service account. Make sure the service account can access the object storage files created earlier. After you add the key, it can take up to 2 minutes for the changes to update in IAM. Wait before saving the connection in OAC
-
Step 4: Loading Data Sets
-
In OAC, navigate to Data → Datasets.
-
For each dataset, click Reload Data, then click Run Now to load fresh data.
Conclusion
Now it is time to run your cost reports and start analyzing the data.You should see your imported workbooks under Home → Catalog. Open the reports, adjust the filters as needed, and load your reports to begin your analysis.