From Cost Reports to Cost Intelligence: Automating FinOps Insights in OCI – Part 2

In Part 1, we explained how the overall solution works using Oracle Cloud services to turn raw OCI FOCUS cost data into insights and detect anomalies. In this post, we shift to implementation. We will focus on the first step – copying FOCUS cost report data from OCI Object Storage into Autonomous Data Warehouse (ADW) using the cloud native DBMS_CLOUD PIPELINE.

Prerequisites

Before you begin, make sure:

• You have an Autonomous Data Warehouse (ADW) instance running in your tenancy.
• The FOCUS cost reports are regularly exported to Object Storage. (Refer to this blogpost).
• You have created a table in ADW to store cost data.

CREATE TABLE oci_cost_data (
  AvailabilityZone VARCHAR2(100),
  BilledCost NUMBER,
  BillingAccountId NUMBER,
  BillingAccountName NUMBER,
  BillingCurrency VARCHAR2(20),
  BillingPeriodEnd VARCHAR2(100),
  BillingPeriodStart VARCHAR2(100),
  ChargeCategory VARCHAR2(20),
  ChargeDescription VARCHAR2(500),
  ChargeFrequency VARCHAR2(50),
  ChargePeriodEnd TIMESTAMP(6) WITH TIME ZONE,
  ChargePeriodStart TIMESTAMP(6) WITH TIME ZONE,
  ChargeSubcategory NUMBER,
  CommitmentDiscountCategory NUMBER,
  CommitmentDiscountId NUMBER,
  CommitmentDiscountName NUMBER,
  CommitmentDiscountType NUMBER,
  EffectiveCost NUMBER,
  InvoiceIssuer VARCHAR2(20),
  ListCost NUMBER,
  ListUnitPrice NUMBER,
  PricingCategory NUMBER,
  PricingQuantity NUMBER,
  PricingUnit VARCHAR2(100),
  Provider VARCHAR2(20),
  Publisher VARCHAR2(20),
  Region VARCHAR2(50),
  ResourceId VARCHAR2(500),
  ResourceName NUMBER,
  ResourceType VARCHAR2(100),
  ServiceCategory VARCHAR2(100),
  ServiceName VARCHAR2(100),
  SkuId VARCHAR2(20),
  SkuPriceId NUMBER,
  SubAccountId VARCHAR2(255),
  SubAccountName VARCHAR2(20),
  Tags VARCHAR2(4000),
  UsageQuantity NUMBER,
  UsageUnit VARCHAR2(100),
  oci_ReferenceNumber VARCHAR2(500),
  oci_CompartmentId VARCHAR2(255),
  oci_CompartmentName VARCHAR2(100),
  oci_OverageFlag VARCHAR2(20),
  oci_UnitPriceOverage NUMBER,
  oci_BilledQuantityOverage NUMBER,
  oci_CostOverage NUMBER,
  oci_AttributedUsage NUMBER,
  oci_AttributedCost NUMBER,
  oci_BackReferenceNumber NUMBER
);

Step 1: Create Credential to Access Object Storage

Start by creating (or using) a service account that has at least read access to the Object Storage bucket where your FOCUS cost reports are stored. This account will be used by ADW to pull the data securely.

Once the account is ready, generate an OCI Auth Token for it and store the credentials inside ADW using the following PL/SQL block

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'MY_OCI_CRED',
    username        => 'service.account@mycompany.com', --service account email
    password        => 'HxxxxxxC' --auth token
  );
END;

This credential will be used by the pipeline in the following steps to authenticate and read the files.

Step 2: Create a New Pipeline

This sets up a new data load pipeline from Object Storage to your oci_cost_data table.

BEGIN
  DBMS_CLOUD_PIPELINE.CREATE_PIPELINE(
    pipeline_name => 'LOAD_COST_DATA_PIPELINE',
    pipeline_type => 'LOAD',
    description   => 'Load cost from object store into a table'
  );
END;

Step 3: Set Pipeline Attributes

This step configures your pipeline: where to find the report, how to parse it, and where to load the data.

BEGIN
  DBMS_CLOUD_PIPELINE.SET_ATTRIBUTE(
    pipeline_name => 'LOAD_COST_DATA_PIPELINE', - Optionally change this name to your organization requirements
    attributes    => JSON_OBJECT(
      'credential_name' VALUE 'MY_OCI_CRED', - Make sure this is the value used Step 1
      'location'        VALUE 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace/b/bucketname/o/prefeix/', --make sure this value is updated with your Object Storage location
      'table_name'      VALUE 'OCI_COST_DATA', --update this value if you are using different table name
      'format'          VALUE '{
		 "type": "csv",
         "delimiter": ",",
         "skipheaders": 1,
		 "ignoreblanklines" : true,
		 "blankasnull" : true,
		 "trimspaces" : "lrtrim",
		 "ignoremissingcolumns": "true",
		 "dateformat": "Auto",
		 "rejectlimit":10000000,
		 "timestampformat": "YYYY-MM-DD\"T\"HH24:MI\"Z\"",
		 "timestamptzformat": "YYYY-MM-DD\"T\"HH24:MI\"Z\"",		 
          "columnpath": [
           "$.AvailabilityZone",
			"$.BilledCost",
			"$.BillingAccountId",
			"$.BillingAccountName",
			"$.BillingCurrency",
			"$.BillingPeriodEnd",
			"$.BillingPeriodStart",
			"$.ChargeCategory",
			"$.ChargeDescription",
			"$.ChargeFrequency",
			"$.ChargePeriodEnd",
			"$.ChargePeriodStart",
			"$.ChargeSubcategory",
			"$.CommitmentDiscountCategory",
			"$.CommitmentDiscountId",
			"$.CommitmentDiscountName",
			"$.CommitmentDiscountType",
			"$.EffectiveCost",
			"$.InvoiceIssuer",
			"$.ListCost",
			"$.ListUnitPrice",
			"$.PricingCategory",
			"$.PricingQuantity",
			"$.PricingUnit",
			"$.Provider",
			"$.Publisher",
			"$.Region",
			"$.ResourceId",
			"$.ResourceName",
			"$.ResourceType",
			"$.ServiceCategory",
			"$.ServiceName",
			"$.SkuId",
			"$.SkuPriceId",
			"$.SubAccountId",
			"$.SubAccountName",
			"$.Tags",
			"$.UsageQuantity",
			"$.UsageUnit",
			"$.oci_ReferenceNumber",
			"$.oci_CompartmentId",
			"$.oci_CompartmentName",
			"$.oci_OverageFlag",
			"$.oci_UnitPriceOverage",
			"$.oci_BilledQuantityOverage",
			"$.oci_CostOverage",
			"$.oci_AttributedUsage",
			"$.oci_AttributedCost",
			"$.oci_BackReferenceNumber"
         ]
      }',
      'priority' VALUE 'HIGH',
      'interval' VALUE '20'  -- every 20 minutes
    )
  );
END;

Check the inline comments and make sure column order in columnpath matches the table definition. This ensures smooth parsing.

Step 4: Start the Pipeline

Once configured, start the pipeline. It will check for new files every 20 minutes (as per the interval) and load them into your table.

BEGIN
  DBMS_CLOUD_PIPELINE.START_PIPELINE(pipeline_name => 'LOAD_COST_DATA_PIPELINE');
END;

What Happens Next?

Once the pipeline starts, ADW will:
•    Monitor the specified Object Storage path
•    Automatically detect and parse new CSVs
•    Load valid rows into the oci_cost_data table
•    Log any rejects or errors to internal logs

Summary

In this part, we set up an end-to-end pipeline that loads raw cost data into ADW without needing any external processing layer. The entire solution runs inside Oracle Cloud, simplifying both governance and automation.
In Part 3, we will define the budget schema, ingest additional metadata, and build interactive dashboards using Oracle Analytics Cloud. This will allow real-time correlation between budgets and actual cloud spend. Stay tuned as we move from data ingestion to visual insights.