# OVHcloud API: Billing Data Analysis Configuration

This guide provides step-by-step instructions on configuring multiple endpoints for the OVHcloud Billing API. You'll learn how to retrieve billing data and integrate it into your applications for accurate and streamlined financial tracking.

![billing-guide](pics/billing-guide-1.png)

---

- [Overview](#overview)
- [Prerequisites](#prerequisites)
- [Step-by-Step Billing Endpoint Configuration](#step-by-step-billing-endpoint-configuration)
  - [1. Initialize API Connection](#_1-initialize-api-connection)
  - [2. Define the billing endpoints](#_2-define-the-billing-endpoints)
  - [3. Metadata extraction](#_3-metadata-extraction)
  - [4. Import your data in the lakehouse](#_4-import-your-data-in-the-lakehouse)
  - [5. Add a tag on services](#_5-add-a-tag-on-services)
  - [6. Create an aggregation table](#_6-create-an-aggregation-table)
  - [7. Prepare the queries and build the Dashboard](#_7-prepare-the-queries-and-build-the-dashboard)
- [Conclusion](#conclusion)

---

## Overview

The OVHcloud API supports multiple endpoint configurations, allowing you to fetch and integrate data from interconnected resources. This guide provides a comprehensive walkthrough for configuring and utilizing the OVHcloud Billing API endpoints. By following these steps, you'll be able to programmatically retrieve, process, and analyze your billing information efficiently.

---

## Prerequisites

Before beginning, ensure you have:

- An active OVHcloud account with billing access
- API credentials, specifically:
  - Application Key (App Key)
  - Application Secret (App Secret)
  - Consumer Key
- Access to the OVHcloud API Developer Portal
- Basic understanding of API endpoint interactions

---

## Step-by-Step Billing Endpoint Configuration

## 1. Initialize API Connection

Configure your OVHcloud API connection by following the authentication setup process detailed in the OVHcloud API Connector Documentation. This involves obtaining and securely inputting your Application Key, Application Secret, and Consumer Key through the connector's user interface. For comprehensive instructions on acquiring and setting up these credentials, consult the [API Connection Details](/en/product/connectors/sources/connectors/OVHcloud-API/index.md) section in the connector documentation.


![billing-guide](pics/billing-guide-1.png)

![billing-guide](pics/billing-guide-2.png)
---


## 2. Define the billing endpoints

### 2.1. Retrieve Account Bills IDs

- Endpoint: `/me/bill`
- Purpose: Fetch a list of all bills IDs for your account

Steps:
1. Select "Add New Endpoint" in the connector
2. Enter `/me/bill` as the endpoint path
3. Configure query parameters (e.g., filtering by year) - for this example we have used key:`date.from` and value:`2023`, to get the records from the year 2023.
4. Make sure that endpoint has a unique name `bills_ids_from_2023`
5. Click "Confirm" and "Save"

![billing-guide](pics/billing-guide-endpoints-1.png)
![billing-guide](pics/billing-guide-endpoints-2.png)

?> We can include an additional step to confirm whether the connected endpoint is correct. While this step is not mandatory, it can be helpful the first time to get an idea of the data you are looking for.

#### Validate Endpoint Data

1. Navigate to the Analyzer tab
2. Extract metadata for the new endpoint
3. Verify bill records are correctly retrieved

### 2.2. Retrieve Account Bills Details IDs

- Endpoint: `/me/bill/{billId}/details`
- Purpose: Fetch the list of IDs for each invoice line details

Steps:
1. Click "Add Endpoint"
2. Enter `/me/bill/{billId}/details` as the endpoint path
3. Configure multiple endpoint selection
4. Link to the previous bills endpoint
5. Select `{billId}` as the path parameter
6. Make sure that endpoint has a unique name `bills_details_ids_from_2023`
7. Save the configuration

![billing-guide](pics/billing-guide-endpoints-3.png)
![billing-guide](pics/billing-guide-endpoints-4.png)
![billing-guide](pics/billing-guide-endpoints-5.png)

?> Use unique endpoint names to simplify source management. This will be useful for step 4. Import your data in the lakehouse.

!> **Note**: Currently, only one path parameter can be dynamically selected. Manual entry might be required for complex endpoints with multiple path parameters.

Returning to the main modal by clicking on the Settings tab, you'll notice that the path parameter and endpoint fields are now non-editable. This is because their values are being derived from another endpoint. If you need to make changes, simply go back to the multiple endpoints section and select 'No multiple endpoint' from the endpoint selection dropdown.

### 2.3. Retrieve Account Bills Details

- Endpoint: `/me/bill/{billId}/details/{detailsID}`
- Purpose: Fetch the details of each invoice line

Steps:
1. Click "Add Endpoint"
2. Enter `/me/bill/{billId}/details/{detailsID}` as the endpoint path
3. Configure multiple endpoint selection
4. Link to the previous bills details ID endpoint
5. Select `{billId}` and `{detailsID}` as the path parameter
6. Make sure that endpoint has a unique name `bills_details_from_2023`
7. Save the configuration

![billing-guide](pics/billing-guide-endpoints-6.png)
![billing-guide](pics/billing-guide-endpoints-7.png)

### 2.4. Retrieve services IDs

- Endpoint: `/services`
- Purpose: Fetch the IDs of all the services of your accounts

Steps:
1. Click "Add Endpoint"
2. Enter `/services` as the endpoint path
3. Make sure that endpoint has a unique name `services_ids`
4. Save the configuration

![billing-guide](pics/billing-guide-endpoints-8.png)

### 2.5. Retrieve services information

- Endpoint: `/services/{serviceId}`
- Purpose: Fetch information about your services to enrich the data in the pipeline

Steps:
1. Click "Add Endpoint"
2. Enter `/services/{serviceId}` as the endpoint path
3. Configure multiple endpoint selection
4. Link to the previous service IDs endpoint
5. Select `{serviceId}` as the path parameter
6. Make sure that endpoint has a unique name `services`
7. Save the configuration

![billing-guide](pics/billing-guide-endpoints-9.png)
![billing-guide](pics/billing-guide-endpoints-10.png)

The configuration of the endpoints is now over, you should see the following:

![billing-guide](pics/billing-guide-endpoints-11.png)

---

## 3. Metadata extraction

With the endpoints now established, navigate to the analyzer tool within the Connectors. Once there, select the endpoints you have recently configured. Allow some time for the system to initiate and complete the processing jobs associated with these endpoints. 

!> The duration of this process may vary depending on the complexity of the tasks and the volume of data being processed.

![billing-guide](pics/billing-guide-analyze-1.png)
![billing-guide](pics/billing-guide-analyze-2.png)
![billing-guide](pics/billing-guide-analyze-3.png)
![billing-guide](pics/billing-guide-analyze-4.png)
![billing-guide](pics/billing-guide-analyze-5.png)
![billing-guide](pics/billing-guide-analyze-6.png)

## 3.1. Reformat data

The data extracted from the sources can be modified to match the exact format we want, or to bucketize it through the blueprints. This is what we are going to do for 2 endpoints:
- `bills_details_from_2023`
- `services`

### 3.1.1. Reformat bills details

The field `periodstart` from the API is a value with the format YYYY-MM-DD, with the DD that can be any day of the month, depending on the day you purchased the service. We are going to use a blueprint to override this day to be set on the first of each month.

![billing-guide](pics/billing-guide-analyze-4.png)

Click on top right of the data analysis on `New Rule`, then select `Date format`.

![billing-guide](pics/billing-guide-blueprint-1.png)

You are now on the configuration of the blueprint, please fill as the following:
- Attribute: `periodstart`
- Current Date Format: `%Y-%m-%d` 
- Replace by: `%Y-%m-01`
- Condition: `Is not null`

![billing-guide](pics/billing-guide-blueprint-2.png)

Click on confirm, you can now see the `periodstart` has 1 blueprint. 

![billing-guide](pics/billing-guide-blueprint-3.png)

### 3.1.2. Reformat services

Many datetime fields from the `services` endpoint contains the timezone information. We want to remove this timezone information from the datetime, so we will reformat all the dates with the `Date Format` blue print, as seen previously.

Go to the `services` endpoint, click `New rule`, select `Date format`.

![billing-guide](pics/billing-guide-analyze-6.png)
![billing-guide](pics/billing-guide-blueprint-1.png)

You are now on the configuration of the blueprint, please fill as the following:
- Attribute: `billing_next_billingdate`
- Current Date Format: `%Y-%m-%dT%H:%M:%S%z` 
- Replace by: `%Y-%m-%dT%H:%M:%S`
- Condition: `All`

![billing-guide](pics/billing-guide-blueprint-4.png)

You can repeat the operation for the following attributes:
- `billing_expirationdate`
- `billing_lifecycle_current_terminationdate`
- `billing_lifecycle_current_creationdate`
- `billing_renew_current_nextdate`

---

## 4. Import your data in the lakehouse

With the data sources now configured, we can proceed to construct the tables. You can go into the `Lakehouse Manager`, and the `Tables` section. Click on the `+` and `Create from a Connectors source`.

The first table will be populated using the `bill_details_from_2023` data source. You can rename the table after clicking on next for `raw_bills_details`, and then click on create.

![billing-guide](pics/billing-guide-lakehouse-1.png)
![billing-guide](pics/billing-guide-lakehouse-2.png)

You can now also import the services into a dedicated table:

![billing-guide](pics/billing-guide-lakehouse-3.png)
![billing-guide](pics/billing-guide-lakehouse-4.png)

---

## 5. Add a tag on services

In order to categorize the expenses, we want to categorize the services with tags, that can represent either your technical environments (e.g. production, dev) or your company's departments or even your customers.

In order to extract the first list of services to categorize, you can go into the `Explorer` section inside the `Lakehouse Manager`.

In the Data, select the `domain`, click on `select` and replace by `select_distinct` and then click on `RUN`.

You should have a result like the following:

![billing-guide](pics/billing-guide-tags-1.png)

You can now click on `Download` and `As a CSV`. You will now have a first CSV file with all the resources to tag. You can open it, rename the column `select_distinct_domain` by `domain`, and then add a second column `tag` and for each domain you want to tag, add the value that you want. You should end up with a CSV file like the following:

![billing-guide](pics/billing-guide-tags-2.png)

It is now time to add a new datasource in the `Connectors` with this CSV file, and create a table in the lakehouse out of it. You can go into `Connectors` -> `sources` -> `New Source` -> `File Upload`. You can name your new source `Domains Tagged` and Add the CSV file and hit the `create` button.

![billing-guide](pics/billing-guide-tags-3.png)
![billing-guide](pics/billing-guide-tags-4.png)

You can new go to the analyzer to check the data is well evaluated, and also add a blueprint on the tags, so we will replace the empty values by `unknown`. Click on `new rule`, `Replace By`. Fill the form as follow:

- Attribute: `tag`
- Replace by: `unknown`
- Condition: `Equal`
- Equal: leave empty

Confirm the blue print.

![billing-guide](pics/billing-guide-tags-5.png)
![billing-guide](pics/billing-guide-tags-6.png)
![billing-guide](pics/billing-guide-tags-7.png)

You can now return to the `Lakehouse Manager`and `Tables` section, and create a table from this new source. Be careful to name the table `domains_tags`.

![billing-guide](pics/billing-guide-tags-8.png)
![billing-guide](pics/billing-guide-tags-9.png)

---

## 6. Create an aggregation table

Now we have a lot of information about our services, theirs bills and also tags. We are going to build an aggregation table to link all the information together.

Inside the `Lakehouse Manager`and `Tables` section, click on the `+` and `Create a new empty table`. Name it: `enriched_bills_details`.

![billing-guide](pics/billing-guide-aggregation-1.png)

You should now have the following tables in your lakehouse:
- `services`
- `domains_tags`
- `raw_bills_details`
- `enriched_bills_details`

![billing-guide](pics/billing-guide-aggregation-2.png)


We are going to drag some fields from the tables we created from sources, and drop them into the `enriched_bills_details`. The fields to drag and drop are:
- From `raws_bills_details`:
  - `domain`
  - `periodstart`
  - `totalprice_value`
- From `domains_tags`:
  - `tag`
- From `services`:
  - `resource_displayname`
  - `resource_product_name`
  - `resource_product_description`

Your `enriched_bills_details` table should now look like:

![billing-guide](pics/billing-guide-aggregation-3.png)

You can now click on `Build` to build this new table.
Now we have the structure of this new table, we are going to create a job to extract the data from the previous 3 tables and load it into this new table. We need to go now inside the `Data Processing Engine` and `Actions` section. Click on `New Action` and select `Aggregate`.

![billing-guide](pics/billing-guide-aggregation-4.png)

We are now going to map fields from different table into the `enriched_bills_details`, for that, you need first to select the sources and destination:
- Sources:
  - `raws_bills_details`
  - `services`
  - `domains_tags`
- Destination: `enriched_bills_details`

For the sources, we need to explain how the data from the 3 tables are linked together through the SQL JOIN syntax. We are going to add then the following joins:
- Between `raws_bills_details` and `services`: 
```sql
raws_bills_details.domain = services.resource_name
```
- Between `raws_bills_details` and `domains_tags`: 
```sql
raws_bills_details.domain = domains_tags.domain
```

![billing-guide](pics/billing-guide-aggregation-5.png)

To finish the aggregate, we need to configure how to select and group attributes of the tables into enriched_bills_details. Map the attributes as shown in the screenshot below.

![billing-guide](pics/billing-guide-aggregation-6.png)

You can now create the aggregate, and run the action.

---


## 7. Prepare the queries and build the Dashboard

Now we have all the data ready to be exploited, we can go into the `Analytics Manager` and `Queries`.
Let's create some queries.

### Cost evolution vs previous month

For the query:
- Data: `totalprice_value` sum FROM `enriched_bills_details`
- Scale: `periodstart`
- Order by: `periodstart desc`

For the visualization:
- As a metric
- Customization: enable all the toggle buttons

![billing-guide](pics/billing-guide-charts-1.png)

### Cost repartition by tag

For the query:
- Data: `totalprice_value` sum FROM `enriched_bills_details`
- Scale: `tag`

For the visualization:
- As a pie chart
- Customization:
  - Show category
  - Show % of total
  - Position: outside
  - Hide legend

![billing-guide](pics/billing-guide-charts-2.png)

### Cost repartition by product

For the query:
- data: `totalprice_value` sum FROM `enriched_bills_details`
- scale: `resource_displayname`

For the visualization:
- As a pie chart
- Customization:
  - Show category
  - Show % of total
  - Position: outside
  - Hide legend

![billing-guide](pics/billing-guide-charts-3.png)

### Cost repartition by product type

For the query:
- Data: `totalprice_value` sum FROM `enriched_bills_details`
- Scale: `resource_productdescription`

For the visualization:
- As a pie chart
- Customization:
  - Show category
  - Show % of total
  - Position: outside
  - Hide legend

![billing-guide](pics/billing-guide-charts-4.png)

### Cost per month and tag

For the query:
- Data: `totalprice_value` sum FROM `enriched_bills_details`
- Scale: `periodstart` `resource_product_description` `tag`
- Filter on: `periodstart` `Not null`
- Order by: `periodstart` `asc`

For the visualization:
- As bar charts
- Customization:
  - Display stacked
  - Hide legend
  - X-axis: regroup by periodstart

![billing-guide](pics/billing-guide-charts-5.png)

### Create the Dashboard

You can now go into the dashboard section, create a new one and put all your charts into this dashboard by clicking the + button.

On top of the dashboard, you can also add filters to ease the naviation into the data. Consider adding the following filters:
- `periodstart`
- `tag`
- `resource_product_description` 

![billing-guide](pics/billing-guide-charts-6.png)

---
## Conclusion

By following these steps, you can effectively configure and utilize the OVHcloud Billing API endpoint to retrieve, process, and analyze billing information programmatically.

---
##  Need help? 🆘

> If you are logging-in with an OVHcloud account, you can create a ticket to raise an incident or if you need support at the [OVHcloud Help Centre](https://help.ovhcloud.com/csm/fr-home?id=csm_index). Additionally, you can ask for support by reaching out to us on the Data Platform Channel within the [Discord Server](https://discord.com/channels/850031577277792286/1163465539981672559). There is a step-by-step guide in the [support](/en/support/index.md).