# services

{% hint style="warning" %}
This article assumes a knowledge of services, their rates and related concepts as documented in [Services in Exivity](https://olddocs.exivity.io/2.3.1/getting-started/concepts/services) and in the article on the [service](https://olddocs.exivity.io/2.3.1/diving-deeper/transform/language/service) statement
{% endhint %}

## Overview

The `services` statement is used to create or modify multiple services based on the data in a [daily RDF](https://olddocs.exivity.io/2.3.1/getting-started/concepts/rdfs).

## Syntax

 **`services`***`{ param1 = value [ ... paramN = value] }`*

Example:

```
services {
    usages_col = ServiceName
    effective_date = 20160101
    set_cogs_using = cogs_prices
    #set_fixed_cogs_using = cogs_prices
    description_col = service_description
    unit_label_col = units_description
    set_min_commit_using = minimum_commit
    category_col = service_group
    interval_col = charging_interval
    model_col = proration
    set_rate_using = rate
    set_fixed_price_using = fixed_prices
}
```

{% hint style="info" %}
Parameters may be specified in any order. The '=' between parameters and their values is optional and may be omitted, but if present it must be surrounded by white-space
{% endhint %}

## Details

### Summary

The `services` statement is used to create or modify multiple services from the data in a [daily RDF](https://olddocs.exivity.io/2.3.1/getting-started/concepts/rdfs). The parameters supplied to the statement map columns in the usage data to attributes of the created services.

### How column names are used

For many of the parameters to the `services` statement there are two ways of using a column name:

1. The values in the column are extracted from the usage data and those values are embedded as literals into the service definition.
2. The column name itself is used in the service definition such that the reporting engine dynamically determines the values to use for any given day when generating report data

When creating or updating services using the first method, Transcript will create a new rate revision every time the rate information changes. For data sources such as Microsoft Azure where the rates can change daily this will result in a lot of rate revisions in the [global database](https://olddocs.exivity.io/2.3.1/getting-started/concepts/datasets#the-global-rdf).

Using the second method requires only a single rate revision which identifies by name the column(s) containing rate and/or COGS information. When a report is run, the charge engine then obtains the correct rate information for any given day from the data in those named columns.

### Parameter table

The parameters supported by the `services` statement are summarised in the following table. The `Type` column in the table indicates the way the column name is used as described in *How column names are used* above. Additional information about each parameter can be found below the summary table itself.

| Parameter                       | Type  | Meaning                                                                                                                                   |
| ------------------------------- | ----- | ----------------------------------------------------------------------------------------------------------------------------------------- |
| *usages\_col*                   | 2     | The name of the column from which units of consumption are derived                                                                        |
| *service\_type*                 | *n/a* | Determines how the *usages\_col* and *consumption\_col* values are used when interrogating the usage data to get the units of consumption |
| *consumption\_col*              | 2     | The name of the column containing units of consumption (AUTOMATIC services only - see below for more details)                             |
| *instance\_col*                 | 2     | Values in this column are used to distinguish between service instances                                                                   |
| *description\_col*              | 1     | Values in this column determine the service description for each service definition                                                       |
| *category* or *group*           | *n/a* | A specific category string to use for all service definitions                                                                             |
| *category\_col* or *group\_col* | 1     | Values in this column determine the service category for each service definition                                                          |
| *interval*                      | *n/a* | A specific charging interval to use for all service defintions                                                                            |
| *interval\_col*                 | 1     | Values in this column determine the charging interval for each service definition                                                         |
| *model*                         | *n/a* | A specific proration setting to use for all service definitions                                                                           |
| *model\_col*                    | 1     | Values in this column determine the proration setting for each service definition                                                         |
| *unit\_label*                   | *n/a* | A specific unit label to use for all service definitions                                                                                  |
| *unit\_label\_col*              | 1     | Values in this column determine the unit label for each service definition                                                                |
| *rate\_col*                     | 2     | Set the column name from which Edify will determine rate per unit at report time                                                          |
| *set\_rate\_using*              | 1     | Values in this column determine the rate per unit for each service definition                                                             |
| *fixed\_price\_col*             | 2     | Set the column name from which Edify will determine the fixed price per charging interval for each service definition                     |
| *set\_fixed\_price\_using*      | 1     | Values in this column determine the fixed\_price per charging interval for each service definition                                        |
| *cogs\_col*                     | 2     | Set the column name from which Edify will determine the COGS rate per unit for each service definition                                    |
| *set\_cogs\_using*              | 1     | Values in this column determine the COGS rate per unit for each service definition                                                        |
| *fixed\_cogs\_col*              | 2     | Set the column name from which Edify will determine the fixed COGS price per charging interval for each service defnition                 |
| *set\_fixed\_cogs\_using*       | 1     | Values in this column determine the fixed COGS price per charging interval for each service definition                                    |
| *set\_min\_commit\_using*       | 1     | Values in this column determine the minimum commit for each service definition                                                            |
| *effective\_date\_col*          | 1     | Values in this column determine the effective date of the rate revision created for each service definition                               |
| *effective\_date*               | *n/a* | A specific effective date to use in the rate revision created for each service definition                                                 |

### Parameter details

#### usages\_col

The `usages_col` parameter is the name of a column containing service keys. A service will be created for each distinct value in this column, and these values will be used as the service keys.

#### service\_type

In order to calculate the charges associated with a service it is necessary to know the number of units of that service that were consumed. Exivity supports two methods of retrieving the units of consumption from usage data and the *service\_type* determines which of these is applied.

Any given service may use one or other of these methods, which are as follows:

* Manual services: `service_type = MANUAL`
* Automatic services: `service_type = AUTOMATIC`

**Manual services**

Manual services require that the units of consumption for each service named in the `usages_col` column are stored in separate columns whose names correlate to the service keys themselves.

To illustrate this, consider the following fragment of usage data:

```
service_name,Small VM,Large VM
Small VM,1,0
Small VM,4,0
Large VM,0,6
Large VM,0,4
```

In this case, the `service_name` column contains a list of service keys and for each of those service keys there is a corresponding column containing the units of consumption for that service. Thus in the above example we can see that there are two services, "Small VM" and "Large VM" and that the units of consumption for each of these services are in the columns of the same name.

The more manual services that are represented in the data, the more columns are required.

**Automatic services**

Automatic services require that the units of consumption for each service named in the `usages_col` column are stored in the column named by the `consumption_col` paramater. To represent the same information as that shown in the example above, the following would be used:

```
service_name,quantity
Small VM,1
Small VM,4
Large VM,6
Large VM,4
```

It can be seen that any number of automatic services, along with their consumption figures, can be represented using only two columns of data.

### consumption\_col

The `consumption_col` parameter is only required when creating automatic services and determines the column containing the units of consumption for each service as described above.

#### instance\_col

It is not enough to know only the units of consumption for each service, as this merely provides the total consumption for each service across the entire usage. In the examples above, for example, the "Large VM" service has 10 units of consumption but using that information alone there is no way to know if this represents one instance of a VM used 10 times, 10 instances of VMs used once each, or something in between.

The `instance_col` parameter is therefore required to tell the difference. Typically this will be a unique identifier which groups the units of consumption into 'buckets'. In the case of a VM this may be a VM ID which remains constant throughout the life of a VM in the cloud.

To illustrate this, we can supplement the example usage fragment used previously with additional information to use as the `instance_col` as follows:

```
vmid,service_name,quantity
444,Small VM,1
444,Small VM,4
555,Large VM,6
666,Large VM,4
```

By specifying `instance_col = vmid` we can now see that the usage represents:

* 5 instances of a single Small VM with an ID of 444
* 6 instances of a Large VM with an ID of 555
* 4 instances of a Large VM with an ID of 666

#### description\_col

If specified, the `description_col` denotes a column containing a friendly description for reports to identify the service with.

Typically in cloud usage data, services are identified using unique IDs (referred to as *keys* in Exivity) which are often non-meaningful to human eyes, so Exivity supports a 'friendly' description for each service for display purposes when generating a report.

For example `description_col = description` may be used in conjunction with the following data to map the `service_id` to a friendly name:

```
vmid,service_id,quantity,description
444,ABC123,1,Small VM
444,ABC123,4,Small VM
555,DEF789,6,Large VM
666,DEF789,4,Large VM
```

It is not mandatory to provide a `description_col` parameter, but if one is not supplied then the description will be set to a duplicate of the service key (as derived via the `usages_col` parameter).

{% hint style="info" %}
In the example above, it can be seen that there are multiple rows in the data for the same service key (vmid). When using `description_col`, the first row for each distinct value in the `usages_col` will be used to set the description.
{% endhint %}

#### category

By default Exivity will group services on reports according to their category. Using categories ensures that charges for services that fall within the same category wil appear in a contiguous block.

The `category` parameter specifies the category to which all services created will be assigned, thus specifying `category = "Virtual Machines"` might be appropriate for the example data used so far in this article.

{% hint style="info" %}
If no category is specified, the services created will be assigned to a category called *Default*.
{% endhint %}

#### category\_col

Usage data normally contains information about a range of services of different types such as *Virtual Machines*, *Storage*, *Networking* and so on. By referencing a column in the usage data which identifies the correct category for each service, multiple categories will be created and each service assigned to the correct category by the `services` statement.

To illustrate this, let us extend the sample data as follows:

```
instance_id,service_name,quantity,description,category
444,Small VM,1,Bronze Computing Service,Virtual Machines
444,Small VM,4,Bronze Computing Service,Virtual Machines
555,Large VM,6,Gold Computing Service,Virtual Machines
666,Large VM,4,Gold Computing Service,Vortual Machines
999,SSD Storage,50,Fast Storage,Storage
```

By specifying `category_col = category` each service will now be associated with the correct category.

#### interval

The `interval` parameter is used to specify a literal interval for all the services created by the `services` statement.

The `interval` parameter may be any of:

* individually
* daily
* monthly

If the `interval` parameter is not specified, then a default interval of *monthly* will be used.

#### interval\_col

In the event that different services in the `usages_col` require different charge intervals, a column name containing the interval to use may be specified using the `interval_col` column as follows:

```
instance_id,service_name,quantity,description,category,interval
444,Small VM,1,Bronze Computing Service,Virtual Machines,monthly
444,Small VM,4,Bronze Computing Service,Virtual Machines,monthly
555,Large VM,6,Gold Computing Service,Virtual Machines,monthly
666,Large VM,4,Gold Computing Service,Vortual Machines,monthly
999,SSD Storage,50,Fast Storage,Storage,daily
```

By specifying `interval_col = interval` each service in the above usage data will be assigned the correct charge interval.

#### model

The `model` parameter is used to enable proration for monthly services. Either of *unprorated* or *prorated* may be specified.

If no model is specified, then a value of *unprorated* will be used by default.

#### model\_col

In the event that different services in the `consumptions_col` require different proration settings, the `model_col` parameter can be used to specify which column contains the proration setting for each service.

```
instance_id,service_name,quantity,description,category,interval,model
444,Small VM,1,Bronze Computing Service,Virtual Machines,monthly,prorated
444,Small VM,4,Bronze Computing Service,Virtual Machines,monthly,prorated
555,Large VM,6,Gold Computing Service,Virtual Machines,monthly,prorated
666,Large VM,4,Gold Computing Service,Vortual Machines,monthly,prorated
999,SSD Storage,50,Fast Storage,Storage,daily,unprorated
```

By specifying `model_col = model`, each service in the above usage data will be assigned the correct proration model.

#### unit\_label

The `unit_label` parameter is used by reports to provide a meaningful description of the units of consumption associated with a service. A virtual machine may have a unit label of `Virtual Machines`, but storage-related services may have a unit label of `Gb` for example.

If the `unit_label` parameter is not specified then a default lavel of `Units` will be used.

{% hint style="warning" %}
The unit label may be up to 63 characters in length. Longer values will be truncated.
{% endhint %}

#### unit\_label\_col

In cases where the services contained in the `usages_col` column collectively require more than one unit label, the `unit_label_col` parameter can be used to identify a column in the usage data which contains an appropriate label for each service.

For example `unit_label_col = label` can be used to associate an appropriate label using the data below:

```
instance_id,service_name,quantity,description,category,interval,model,label
444,Small VM,1,Bronze Computing Service,Virtual Machines,monthly,prorated,Virtual Machines
444,Small VM,4,Bronze Computing Service,Virtual Machines,monthly,prorated,Virtual Machines
555,Large VM,6,Gold Computing Service,Virtual Machines,monthly,prorated, Virtual Machines
666,Large VM,4,Gold Computing Service,Vortual Machines,monthly,prorated, Virtual Machines
999,SSD Storage,50,Fast Storage,Storage,daily,unprorated,Gb
```

{% hint style="info" %}
The parameters `rate_col`, `set_rate_using`, `fixed_price_col`, `set_fixed_price_using`, `cogs_col`, `set_cogs_using`, `fixed_cogs` and `fixed_cogs_using` (all of which are detailed below) collectively determine the types of charge that will be associated with the service definitions created by the `services` statement.
{% endhint %}

```
A service definition must have at least one charge type and may have up to three (as potentially a *rate*, a *fixed rate* and either of *cogs* or *fixed cogs* may be used)
```

#### rate\_col

The `rate_col` parameter is used to determine the column in the usage data which contains the unit rates for the service definitions created by the `services` statement.

As each service definition is created, an initial rate revision is also created which contains the column named by the `rate_col` parameter. When a report is run, for each day in the reporting range the unit rate for that day will be determined by whatever value is in the column named by the `rate_col` parameter in the usage data.

This means that only a single rate revision is required, even if the actual value in the `rate_col` column is different from day to day.

#### set\_rate\_using

The `set_rate_using` parameter is also used to determine the unit rate for each service. This differs from the `rate_col` parameter in that the values in the column named by `set_rate_using` are consulted when the service is created, and the literal values in that column are used to populate the initial rate revision.

This means that the unit cost is hard-coded into the rate revision and will apply indefinitely, or until such time as a new rate revision takes effect (see `effective_date` for more details)

{% hint style="info" %}
Either of `rate_col` or `set_rate_using` (but not both) may be used in a single `services` statement
{% endhint %}

#### fixed\_price\_col

The `fixed_price_col` parameter is used to determine the column in the usage data which contains the fixed price associated with the service definitions created by the `services` statement.

As each service definition is created, an initial rate revision is also created which contains the column named by the `fixed_price_col` parameter. When a report is run, for each day in the reporting range the fixed price for that day will be determined by whatever value is in the column named by the `fixed_price_col` parameter in the usage data.

If a monthly service has different fixed prices for different days in the month, then whichever results in the highest charge will be used.

This means that only a single rate revision is required, even if the actual value in the `fixed_price_col` column is different from day to day.

#### set\_fixed\_price\_using

The `set_fixed_price_using` parameter is also used to determine the fixed price for each service. This differs from the `fixed_price_col` parameter in that the values in the column named by `set_fixed_price_using` are consulted when the service is created, and the literal values in that column are used to populate the initial rate revision.

This means that the fixed price is hard-coded into the rate revision and will apply indefinitely, or until such time as a new rate revision takes effect (see `effective_date` for more details)

{% hint style="info" %}
Either of `fixed_price_col` or `set_fixed_price_using` (but not both) may be used in a single `services` statement
{% endhint %}

#### cogs\_col

The `cogs_col` parameter is used to determine the column in the usage data which contains the COGS rate associated with the service definitions created by the `services` statement.

As each service definition is created, an initial rate revision is also created which contains the column named by the `cogs_col` parameter. When a report is run, for each day in the reporting range the COGS rate for that day will be determined by whatever value is in the column named by the `cogs_col` parameter in the usage data.

If a monthly service has different COGS rates for different days in the month, then whichever results in the highest charge will be used.

This means that only a single rate revision is required, even if the actual value in the `cogs_col` column is different from day to day.

#### set\_cogs\_using

The `set_cogs_using` parameter is also used to determine the COGS rate for each service. This differs from the `cogs_col` parameter in that the values in the column named by `set_cogs_using` are consulted when the service is created, and the literal values in that column are used to populate the initial rate revision.

This means that the COGS rate is hard-coded into the rate revision and will apply indefinitely, or until such time as a new rate revision takes effect (see `effective_date` for more details)

{% hint style="info" %}
Either of `cogs_col` or `set_cogs_using` (but not both) may be used in a single `services` statement
{% endhint %}

#### fixed\_cogs\_col

The `fixed_cogs_col` parameter is used to determine the column in the usage data which contains the fixed COGS price associated with the service definitions created by the `services` statement.

As each service definition is created, an initial rate revision is also created which contains the column named by the `fixed_cogs_col` parameter. When a report is run, for each day in the reporting range the fixed COGS price for that day will be determined by whatever value is in the column named by the `fixed_cogs_col` parameter in the usage data.

If a monthly service has different fixed COGS prices for different days in the month, then whichever results in the highest charge will be used.

This means that only a single rate revision is required, even if the actual value in the `fixed_cogs_col` column is different from day to day.

#### set\_fixed\_cogs\_using

The `set_fixed_cogs_using` parameter is also used to determine the fixed COGS price for each service. This differs from the `fixed_cogs_col` parameter in that the values in the column named by `set_fixed_cogs_using` are consulted when the service is created, and the literal values in that column are used to populate the initial rate revision.

This means that the fixed COGS price is hard-coded into the rate revision and will apply indefinitely, or until such time as a new rate revision takes effect (see `effective_date` for more details)

{% hint style="info" %}
Either of `fixed_cogs_col` or `set_fixed_cogs_using` (but not both) may be used in a single `services` statement
{% endhint %}

#### set\_min\_commit\_using

The `set_min_commit_using` parameter is used to set the minimum commit value in the initial rate revision for each service.

The values in the column identified by `set_min_commit_using` are extracted from the usage data and used as numeric literals in the revision.

#### effective\_date

When creating the initial rate revision for a service, the value specified by the `effective_date` parameter is interpreted as a `yyyyMMdd` value to determine the date from which the revision should be applied.

If the `effective_date` parameter is omitted then the current [data date](https://olddocs.exivity.io/2.3.1/getting-started/concepts/datadate) will be used by default.

{% hint style="info" %}
When using `effective_date`, the value will be used to set the initial rate revision date for all the service definitions created by the `services` statement. If different services require different effective dates then the `effective_date_col` parameter may be used to determine the effective date for each service from a column in the usage data.
{% endhint %}

#### effective\_date\_col

If there is a column in the usage data containing yyyyMMdd values representing the desired effective date for the initial revision of each service, The `effective_date_col` parameter may be used to extract the values from this column and set the effective date for each service accordingly.

{% hint style="info" %}
Either of `effective_date` or `effective_date_col` may be specified in a single `services` statement, but not both
{% endhint %}

## Examples

```
services {
    usages_col = ServiceName
    effective_date = 20180101
    set_cogs_using = cogs_prices
    description_col = service_description
    unit_label_col = units_description
    set_min_commit_using = minimum_commit
    category_col = service_group
    interval_col = charging_interval
    model_col = proration
    set_rate_using = rate
    set_fixed_price_using = fixed_prices
}
```