Syntax

Within the individual reference articles for each statement, the syntax is described using the following conventions:

• bold for keywords

• italics for arguments

• Square brackets for optional keywords and arguments [likethis]

• Vertical pipe for alternative keyword options just|exactly as shown

• Ellipses for a variable length list of arguments: Column1 ... ColumnN

Refer to the core concepts page for more information regarding datasets, fully qualified column names and related information.

Reference

The following statements (in alphabetical order) are supported by Transcript:

Overview

The aggregate statement is used to reduce the number of rows in a DSET while preserving required information within them

Syntax

aggregate[dset.id][notime|daily] [offsetoffset][nudge] [default_functionfunction] colname function [... colname function]

Details

The aggregate statement is a powerful tool for reducing the number of rows in a DSET. Aggregation is based on the concept of matching rows. Any two rows that match may be merged into a single row which selectively retains information from both of the original rows. Any further rows that match may also be merged into the same result row.

A quick introduction

A match is determined by comparing all the columns which have a function of match associated with them (further information regarding this can be found below). If all the column values match, then the rows are merged.

Merging involves examining all the columns in the data that were not used in the matching process. For each of those columns, it applies a function to the values in the two rows and updates the result row with the computed result of that function. For a full list of functions, please refer to the table further down in this article.

To illustrate this consider the following two row dataset:

id,colour,location,quantity
1234,blue,europe,4.5
1234,green,europe,5.5

If we don't care about the colour value in the above records, we can combine them together. We do care about the quantity however, so we'll add the two values together to get the final result.

The statement to do this is:

aggregate notime id match location match quantity sum

• id match means that the values in the id columns must be the same

• location match means that the values in the location columns must be the same

• quantity sum means that the resulting value should be the sum of the two existing values

• by default, a function of first is applied to the columns, such that the original row retains its value

Applying these rules to the above example we get the following single result record:

id,colour,location,quantity,EXIVITY_AGGR_COUNT
1234,blue,europe,10,2

Parameters

The aggregate statement accepts a range of parameters as summarised in the table below:

If two records are deemed suitable for merging then the function determines the resulting value in each column. The available functions are as follows:

Non time-sensitive aggregation

When the notime parameter is specified, the aggregation process treats any columns flagged as start and end times in the data as data columns, not timestamp columns.

In this case when comparing two rows to see if they can be merged, the aggregation function simply checks to see if all the columns with a function of match are the same, and if they are the two rows are merged into one by applying the appropriate function to each column in turn.

De-duplication

The following illustrates the aggregate statement being used to remove duplicate rows from a DSET:

# The first column in the DSET being aggregated
# is called subscription_id

aggregate notime default_function match subscription_id match

The analysis of the statement above is as follows:

• notime - we are not interested in timestamps

• default_function match - by default every column has to match before records can be aggregated

• subscription_id match - this is effectively redundant as the default_function is match but needs to be present because at least one pair of colname function parameters is required by the aggregate statement

The resulting DSET will have no duplicate data rows, as each group of rows whose column values were the same were collapsed into a single record.

Row reduction while preserving data

The example shown at the top of this article used the sum function to add up the two quantity values, resulting in the same total at the expense of being able to say which source record contributed which value to that total.

The sum function can therefore accurately reflect the values in a number of source rows, albeit with the above limitation. By using a function of sum, max or min, various columns can be processed by aggregate in a meaningful manner, depending on the specific use case.

Time-sensitive aggregation

When aggregating, columns containing start time and end time values in UNIX epoch format can be specified. Each record in the DSET therefore has start and end time markers defining the period of time that the usage in the record represents. As well as taking the start times and end times into account, time-sensitive aggregation can perform additinal manipulations on these start and end times.

A quick example

Consider the following CSV file called aggregate_test.csv:

startUsageTime,endUsageTime,id,subscription_id,service,quantity
2017-11-03:00.00.00,2017-11-03:02.00.00,ID_1234,SUB_abcd,Large VM,2
2017-11-03:00.00.00,2017-11-03:03.00.00,ID_1234,SUB_abcd,Large VM,2
2017-11-03:00.00.00,2017-11-03:06.00.00,ID_3456,SUB_efgh,Medium VM,2
2017-11-03:00.00.00,2017-11-03:04.00.00,ID_1234,SUB_abcd,Large VM,2
2017-11-03:00.00.00,2017-11-03:05.00.00,ID_1234,SUB_abcd,Large VM,2
2017-11-03:00.00.00,2017-11-03:06.00.00,ID_1234,SUB_abcd,Large VM,2
2017-11-03:00.00.00,2017-11-03:07.00.00,ID_1234,SUB_abcd,Large VM,2
2017-11-03:00.00.00,2017-11-03:02.00.00,ID_3456,SUB_efgh,Large VM,2
2017-11-03:00.00.00,2017-11-03:03.00.00,ID_3456,SUB_efgh,Medium VM,2
2017-11-03:00.00.00,2017-11-03:04.00.00,ID_3456,SUB_efgh,Large VM,2
2017-11-03:00.00.00,2017-11-03:05.00.00,ID_3456,SUB_efgh,Large VM,2
2017-11-03:00.00.00,2017-11-03:07.00.00,ID_3456,SUB_efgh,Large VM,2
2017-11-03:00.00.00,2017-11-03:06.00.00,ID_3456,SUB_efgh,Medium VM,2

It is possible to aggregate these into 3 output records with adjusted timestamps using the following Transcript task:

import system/extracted/aggregate_test.csv source aggr alias test

var template = YYYY.MM.DD.hh.mm.ss
timestamp START_TIME using startUsageTime template ${template}
timestamp END_TIME using endUsageTime template ${template}
timecolumns START_TIME END_TIME
delete columns startUsageTime endUsageTime

aggregate aggr.test daily nudge default_function first id match subscription_id match service match quantity sum

timerender START_TIME as FRIENDLY_START
timerender END_TIME as FRIENDLY_END

Resulting in:

id,subscription_id,service,quantity,START_TIME,END_TIME,EXIVITY_AGGR_COUNT,FRIENDLY_START,FRIENDLY_END
ID_1234,SUB_abcd,Large VM,12,1509667200,1509692399,6,20171103 00:00:00,20171103 06:59:59
ID_3456,SUB_efgh,Medium VM,6,1509667200,1509688799,3,20171103 00:00:00,20171103 05:59:59
ID_3456,SUB_efgh,Large VM,8,1509667200,1509692399,4,20171103 00:00:00,20171103 06:59:59

As can be seen, for each unique combination of the values in the id,subscription-id and service columns, the start and end times have been adjusted as described above and the quantity column contains the sum of all the values in the original rows.

Further notes

The daily parameter to aggregate means that the START_TIME and END_TIME columns are now recognised as containing timestamps. When aggregating with the daily option, timestamps within the current dataDate are combined to result in an output record which has the earliest start time and the latest end time seen within the day.

Optionally, following daily an offset may be specified as follows:

aggregate aggr.test daily offset 2 id match subscription_id match quantity sum

In this case the start and end timestamps are adjusted by the number of hours specified after the word offset before aggregation is performed. This permits processing of data which has timestamps with timezone information in them, and which may start at 22:00:00 of the first day and end at 21:59:59 of the second day, as an offset can be applied to realign the records with the appropriate number of hours to compensate.

The nudge parameter shaves 1 second off end times before aggregating in order to avoid conflicts where hourly records start and end on 00:00:00 9the last second of the current hour is the same as the first second of the next hour)

Overview

The calculate statement is used to perform arithmetic operations using literal and column values.

Syntax

calculate columnResultColas source operation source

where source is either of columncolName or valueliteral_value

and operation is one of the characters + - * / % for addition, subtraction, multiplication, division and modulo respectively.

Examples: calculate column ResultCol as column Amount * value 1.2 calculate column Net as column total - column cogs calculate column constant_7 as value 3.5 + value 3.5

Details

The ResultCol parameter is the name of the column that will hold the results. This column may or may not exist (if necessary it will be created automatically).

Both of the two source parameters can specify a literal value, or the name of a column containing the value to use when performing the calculation.

• A literal value is specified using valueN where N is the literal number required

• A column name is specified using columncolName where ColName is the name of the column containing the values required

Additional notes:

• Any blank or non-numeric values in a source column will be treated as 0

• An attempt to divide by zero will result in 0

• When performing a modulo operation, the two source values are rounded to the nearest integer first

Examples

• Add 1.5 to the values in the Rate column:

calculate column Rate as column Rate + value 1.5

• Multiply the values in the Rate column by those in the Quantity column

• Store the result in a new column called Charge

calculate column Charge as column Rate * column Quantity

Overview

The append statement is used to append one to the end of another.

Syntax

appendsource_dset.idtodestination_dset.id

Details

If the source DSET has any column names not present in the destination DSET then additional columns are automatically created in the destination DSET. These additional columns will contain blank values by default.

If one or more column names are present in both DSETs then the columns copied from the source DSET may be re-ordered into the same order as that used by the destination DSET.

At the end of the operation, the destination DSET will contain all the data from both DSETs, and the source DSET is unchanged.

Example

Given the following DSETs:

DSET ID: example.data

DSET ID: example2.data

The statement append example2.data to example.data will result in the following destination DSET (example.data):

Transcript executes user-definable scripts (termed tasks) in order to produce one or more (RDFs) from one or more input Dataset files in CSV format. These RDFs are later used by the reporting engine to generate results.

Overview

Transcript tasks are located in system/config/transcript/ and are ASCII files which can be created with any editor. Both UNIX and Windows end-of-line formats are supported.

Statements

Each statement in a Transcript task must be contained on a single line. Statements consist of a keyword indicating the action to perform, followed by zero or more parameters, separated by white-space, required by the statement. Documentation for all the possible statements can be found in the Transcript language .

Quotes and escapes

By default a space, tab or newline will mark the end of a word in a Transcript task. To include white-space in a parameter (for example to reference a column name with a space in it) then this can be done by enclosing it in double quotes or escaping it by preceding it with \.

Examples: create columns from "Meter Name" using Quantity create columns from Meter\ Name using Quantity

The following table summarises the behaviour of quotes and escapes:

Comments

Comments in a Transcript task start with a # character that is either of:

• the first character of a line in the Transcript task

• the first character in a word

Comments always end at the end of the line they are started on.

Variables

Transcript statements may contain variables. Variables have a name and a value. When a variable name is encountered during execution of the task, the name is replaced with the value of the variable with that name.

Variable names ...

• may be used multiple times in a single statement

• are case sensitive - ${dataDate} is different to ${datadate}

• may not be nested

• may be embedded within surrounding text - xxx${dataDate}yyy

• may be used within quotes: import "${baseDir}\to_import\AzureJuly${dataDate}.ccr" source AzureJuly

• may appear as words of their own in a transcript statement - create column Date value ${dataDate}

Regular Expression variables

A regular expression variable is a special type of variable used to match the name of a column in a DSET. It is enclosed by ${/ and /} and the text within this enclosure can take either of the following two forms:

1. ${/expression/}
2. ${/dset.id/expression/}

   • If the text preceding the / character is not a valid DSET ID then the entire text of the variable between the ${/ and /} enclosure is treated as a regular expression and will be applied to the default DSET

Once the DSET ID and the expression have been established by the above, the expression is tested against each column name in the DSET and the first matching column name is returned. If no match is found, then an error is logged and the transcript task will fail.

The expression does not have to match the entire column name. Assuming no subgroup is specified, as long as a match is made then the variable will be expanded to the whole column name.

Examples:

Importing Data

1. The Dataset (in CSV format) is read from disk

5. An index is constructed, which facilitates high speed manipulation of the data in the DSET

6. The DSET is added to the list of DSETs available for use by subsequent statements in the Transcript task

Once these actions have been completed, a DSET can be identified through the unique combination of source.alias. This permits Transcript statements to specify which DSET to operate on.

Exporting Data

Data can be exported in one of two ways during the execution of a Transcript task:

Export on demand

Many Transcript statements change the data in the DSET in some way. Columns may be created, renamed or deleted and rows may be added and removed for example.

Finishing

When developing your , it is possible to view intermediate results by utilizing the preview functionality in the Transformer editor. To use the Transformer Preview, place your cursor at a line number in your script uptill where the Transformer should execute. In the example below, the cursor is placed at line numer 27:

Make sure to select a Preview Date for which data is available in the system. Then execute the preview by clicking the Update and Preview button.

By default, the preview will load the first 1000 records for each DSET being imported. In case you want to increase / decrease this amount, you can do so by adjusting the value in the Output Limit field:

Overview

The capitalise (the spelling capitalize is also supported) statement is used to modify the name of a column and/or the values in a column such that the first character is a capital letter and the remaining characters are lower case.

Syntax

capitalise values|heading [and values|heading] in column|columnsColName1 [... ColNameN]

Details

The heading and values keywords refer to the name of an existing column and the values in each row for that column respectively.

Only the first character in the column name or value is converted to upper case. If this character is not a letter, then the statement will have no effect on it. For example applying the statement:

will have no effect on the column name as the first character is an underscore. However, applying the same statement to a column called _VMName would result in a new name of _vmname as after attempting to make the first character a capital (which in the case of the underscore has no effect), the remaining characters are converted to lower case.

Any number of column names may be specified, and any of these may or may not be fully qualified. When applied to values in a column, blank values are ignored.

Examples

The 'Data Pipelines' menu allows an admin of the Exivity solution to manage Transcript 'Transformer' scripts. Transcript has its own language reference, which is fully covered in a separate chapter of this documentation.

As described in the , you are free to use your editor of choice to create and modify Transformers. However, the GUI also comes with a built-in Transformers-editor.

Creating Transformers

To create a new Transformer for Transcript, follow these steps:

1. From the menu on the left, select "Data Pipelines" > 'Transformer'

2. To create a new Transformer to normalise and enrich USE Extractor consumption and lookup data, click the 'Create Transformer' button

3. When your Exivity instance has access to the Internet, it will pull in the latest set of Transformer Templates from our account. These templates are then presented to you, and you can pick one from the list to start Extracting. If you don't have access to the internet, you can download them directly from . You are also free to start creating your own Extractor from scratch.

4. Provide a meaningful name for your Transformer. When we create a Transformer for a consolidated bill of various IT resources we would, for example, name it: 'IT Services Consumption'.

5. When you're done creating your Transformer, click the 'Insert' at the bottom of the screen.

Edit and Delete Transformers

When you want to change or delete an existing Transformer, first select one from the list of Transformer that you want to change:

1. When you've selected your Transformer from the "Data Pipelines" > 'Transformers' list, you can change the Transformer script in the editor

2. In this example, we're adding a 'services' statement using auto completion, to simplify the creation of services

3. In case you want to save your changes, click the 'Save' button at the bottom of the 'Editor' screen. To delete this Transformer, you can do so by clicking the 'Remove' button, after which you'll receive an confirmation pop-up where you'll have to click 'OK'.

Run and Schedule Transformers

To test your Transformer, you can execute or schedule it directly from the Glass interface:

1. After you have selected the Transformer that you would like to run, click to the 'Run' tab next to the 'Editor' tab

2. Manual execution of a Transformer can only be done for a single day. Provide the date you want to run this transformer for in dd-MM-yyyy format. You can also use the date picker, by clicking on the down facing arrow, on the right side of the date field

3. When you've provided the required date, click 'Run Now' to execute the Transformer. After the Transformer has completed running, you will receive some success or failed message, after which you might need to make additional changes to your Transformer. For further investigations or troubleshooting, consult the "Log Viewer" found under the administration dropdown menu top right of the screen.

4. Once you're happy with your output, you can schedule the Transformer via the 'Schedule' tab, which is located next to the 'Run' tab at the top of the screen

5. Transformer can be scheduled to run once a day at a specific time. Also you should provide a date, which is provided by using an offset value. For example, if you want to execute this Transformer against yesterdays date with every schedule run, you should provide a value there of -1

6. When you're done with the schedule configuration, you may click the 'Schedule' button. In case you want to change or remove this schedule afterwards, click the 'Unschedule' button.

Overview

The copy statement is used to copy rows from one DSET to another

Syntax

copy rows todset.id

move rows todset.id

Details

Both copy and move must be used within the body of a where statement. Only rows that match the expression will be copied (or moved).

• The DSET from which rows will be copied or moved is automatically determined from the expression used by the where statement.

• The DSET to which rows will be copied or moved is determined by the dset.id parameter

The destination DSET may or may not exist. If it does not exist then it will be created. If it does exist then the following logic is applied:

• If the destination DSET has more columns than the source DSET then the new rows in the destination DSET will have blank values in the rightmost columns

• If the destination DSET has fewer columns than the source DSET then the destination DSET will be extended with enough new columns to accomodate the new rows. In this case, existing rows in the destination DSET will have blank values in the rightmost columns

If the destination DSET is extended to accomodate the source rows then the new (rightmost) columns will have the same names as the equivalent columns in the source DSET. In the event that this would cause a naming conflict with existing columns in the destination DSET, one or more new columns in the destination DSET will heve a suffix added to their name to ensure uniqueness. This suffix takes the form _N where N is a number starting at 2.

To illustrate this, if the source DSET has columns called subscription,name,address,hostname and the destination DSET has a single column called name then the resulting extended destination DSET would have columns called name,subscription,name_2,address,hostname.

Example

# Move all rows in usage.data where hostname is "test server"
# to a new DSET called test.servers

where ([usage.data.hostname] == "test server") {
    move rows to test.servers
}

Overview

The convert statement is used to convert values in a column from base-10 to base-16 or vice-versa.

Syntax

convertcolNameto decimal|hex from decimal|hex

Details

When converting values in a column, the following considerations apply:

• Values in the column are replaced with the converted values

• The colName argument must reference an existing column, and may optionally be fully qualified (else the column is assumed to be in the default DSET)

• If any values in the column are not valid numbers, they will be treated as 0

• Blank values are ignored

• The convert statement may be used in the body of a where statement

• If a value in colName contains a partially correct value such as 123xyz then it will be treated as a number up to the first invalid character, in this case resulting in a value of 123.

• The hex digits in the original value can be either upper or lower case

• The hex digits from A-F will be rendered in upper case in the converted output

• The convert statement only supports integer values (floating points will be treated as floored to the nearest integer)

Example

convert decimal_count from decimal to hex
convert unique_id from hexadecimal to dec

Overview

The default statement is used to explicitly define the default DSET to use when specifying a column name as an argument in subsequent statements.

Syntax

default dsetsource.alias

Details

Given that multiple DSETS can be loaded at once, it is necessary to specify which DSET to operate on when performing actions such as creating a new column with create. A column name in a Transcript statement is assumed as belonging to the default DSET unless it is a fully qualified column name.

If there is no default statement in the Transcript, then the first CSV file imported via the import statement will automatically be designated as the default DSET.

The default statement can be used multiple times throughout a Transcript, in which case the default DSET will be whichever was specified by the last default statement executed.

Lastly, when executing a finish statement, unless otherwise specified, the default DSET will be used to populate the reporting database created as a result.

Examples

Set custom.datafile as the default DSET:

default dset custom.datafile

Overview

The correlate statement is used to enrich the default DSET by adding new columns to it, and/or updating existing columns with useful values. The new column names are derived from other DSETs and the values in those columns are set using a lookup function based on the value in a key column shared between the DSETs.

Syntax

correlateColName1 [ ... ColNameN]usingKeyColumn[assumingassumeDSET][defaultDefaultValue]

Details

The ColName1 ... ColNameN arguments are column names that will be copied from their original DSETs and merged into the default DSET.

Column names must be fully qualified, unless the assuming parameter is used, in which case any column names that are not fully-qualified will be assumed to belong to the DSET specified by assumeDSET.

Source and Destination columns

Source columns are those from which a cell is to be copied when the KeyColumn matches. Destination columns are columns in the default DSET into which a cell will be copied. Destination column names are derived from the names of the source columns as follows:

• The source column is the argument in its original form, for example: Azure.usage.MeterName

• The destination column is the same argument, but with the DSET ID replaced with that of the default DSET. For example if the default DSET is Custom.Services then the destination column for the above would be Custom.Services.MeterName.

If a destination column name doesn't exist in the default DSET then a new column with that name will automatically be created.

The Key Column

The KeyColumn argument is a column name which must not be fully qualified and which must exist in the default DSET and all of the DSETs referenced by the ColNameN arguments.

Default values

The DefaultValue argument, if present, specifies the value to write into the destination column if there is no match for the KeyColumn. If the DefaultValue argument is not specified then any rows where there is no match will result in a blank cell in the destination column.

For each row in the default DSET, the source DSET is searched for a matching KeyColumn value, and if a match is found then the value in the source column is used to update the default DSET. The row of the first match found in the source DSET will be used.

Overwriting

When matching the KeyColumn values, the logic in the following table is evaluated against every row in the destination DSET.

✘ means no or disabled, ✔ means yes or enabled

Examples

Given two Datasets as follows, where the default DSET is MyData.Owners:

Dataset 'MyData.Owners'

owner,id
John,100
Tim,110
Fokke,120
Joost,130
Jon,140

Dataset 'Custom.Services'

service,description,id
Small_VM,Webserver,130
Medium_VM,App_Server,100
Large_VM,DB_Server,110
Medium_VM,Test_Server,120

The statement: correlate service description using id assuming Custom.Services

Will enrich the MyData.Owners Dataset such that it contains:

owner,id,service,description
John,100,Medium_VM,App_Server
Tim,110,Large_VM,DB_Server
Fokke,120,Medium_VM,Test_Server
Joost,130,Small_VM,Web_Server
Jon,140,,

The statement: correlate service description using id assuming Custom.Services default unknown

Will produce:

owner,id,service,description
John,100,Medium_VM,App_Server
Tim,110,Large_VM,DB_Server
Fokke,120,Medium_VM,Test_Server
Joost,130,Small_VM,Web_Server
Jon,140,unknown,unknown

Overview

The create statement is used to add one more more new columns to an existing DSET.

Syntax

create columnNewColumnName[valueValue]

create columns fromColumnName[usingValueColumnName]

create mergedcolumnNewColumn[separatorsep]from [stringliteral] Column [/regex/] [Column [/regex/]|stringliteral]

Details

Explicit single column creation

Syntax

create columnNewColumnName[valueValue]

Details

This statement is used to create a new column called NewColumnName. The NewColumnName argument may be a fully qualified column name, in which case the new column will be created in the DSET specified as part of that name.

Note: If no default DSET has been explicitly defined using the default dset statement then the DSET created by the first use or import statement in the Transcript task is automatically set as the default DSET.

A column called NewColumnName must not already exist in the DSET. If NewColumnName contains dots then they will be converted into underscores.

The new column will be created with no values in any cells, unless the optional value *Value* portion of the statement is present, in which case all the cells in the new column will be set to Value.

Examples

Create a new empty column called Cost in the default DSET: create column Cost

Create a new column called Cost with a value of 1.0 in every row of the default DSET: create column Cost value 1.0

Create a new column called Cost with a value of 1.0 in every row of the DSET custom.charges: create column custom.charges.Cost value 1.0

Automated single/multiple column creation

Syntax

create columns fromColumnName[usingValueColumnName]

Details

This statement is used to create multiple columns in a single operation. As is the case for create columns above, if the using ValueColumnName portion of the statement is not present, then all newly created columns will have no values in any cells.

Given this example dataset:

The statement create columns from ServiceName using Count will create the result shown below:

The names of the new columns to create are derived from the contents of the cells in the column called ColumnName, and the values (if opted for) are derived from the contents of the cells in the column called ValueColumnName. Duplicates are ignored. If all the cells in ColumnName have the same contents, then only a single new column will be created. To illustrate this, consider the following:

SubscriptionID,ServiceName,Quantity
FE67,StorageGB,30
1377,Small_VM,2
EDED,Medium_VM,8
8E1B,Large_VM,1
99AA,Small_VM,99

When applied to the data above, the statement create columns from ServiceName will produce the following result (note that only a single column called Small_VM is created, and that empty cells are represented with a separator character, which in the case of the below is a comma):

SubscriptionID,ServiceName,Quantity,StorageGB,Small_VM,Medium_VM,Large_VM
FE67,StorageGB,30,,,,
1377,Small_VM,2,,,,
EDED,Medium_VM,8,,,,
8E1B,Large_VM,1,,,,
99AA,Small_VM,99,,,,

If opting to set the values in the new columns, then for each row the value in ValueColumnName will be copied into the column whose name matches ColumnName. When applied to the same original data, the statement create columns from ServiceName using Quantity will produce the following result:

SubscriptionID,ServiceName,Quantity,StorageGB,Small_VM,Medium_VM,Large_VM
FE67,StorageGB,30,30,,,
1377,Small_VM,2,,2,,
EDED,Medium_VM,8,,,8,
8E1B,Large_VM,1,,,,1
99AA,Small_VM,99,,99,,

When using create columns the new columns are always created in the default DSET. This means that when no values are being set, it is possible to specify a different DSET for ColumnName. If the default DSET is Azure.usage, then the statement create columns from custom.data.Services will derive the names of the new columns from the cell contents in the Services column in the custom.data DSET.

This is only possible in the absence of the using ValueColumnName option. When values are to be set, both the ColumnName and ValueColumnName arguments must belong to the default DSET.

Example

The following transcript task will import the datasets Azure.usage and system/extracted/Services.csv, and create new (empty) columns in Azure.usage whose names are taken from the values in the column ServiceDefinitions in Services.csv.

import system/extracted/Services.csv source custom
import usage from Azure
default dset Azure.usage
create columns from custom.Services.ServiceDefinitions

Merging column values to create a new column

Syntax

create mergedcolumnNewColumn[separatorsep]from [stringliteral] Column [/regex/] [ ... Column [/regex/]|stringliteral]

Details

This form of the statement is used to generate a new column containing values derived from those in one or more existing columns (termed source columns). The parameters are as follows:

If a regex is specified then it must contain a subgroup enclosed in parentheses. The portion of the text in the source column matched by this subgroup will be extracted and used in place of the full column value.

If a regex is not specified, then the entire value in the source column will be used.

Options

By default the value extracted from a source column will be blank in the following two cases:

• There is a blank value in a source column

• No match for a regular expression is found in the value of a source column

In such cases the merged result will simply omit the contribution from the source column(s) in question. If all the source columns yield a blank result then the final merged result will also be blank.

This behaviour can be overridden through the use of the option statement. The options associated with the create mergedcolumn statement are as follows:

option merge_blank = some_text_here

This option will use the string some_text_here in place of any blank source column value.

option merge_nomatch = some_text_here

This option will use the string some_text_here if the result of applying the regular expression to a column value returns no matches.

Specifying the literal string <blank> as the merge_blank or merge_nomatch value will reset the option such that the default behaviour is re-activated.

Examples

Given the following dataset:

name,user_id,department
Eddy,123-456-123456,Development
Tim,654-321-654321,Project Management
Joram,555-222-999111,Development
Joost,826-513-284928,Sales and Marketing

The following examples illustrate some uses of the create mergedcolumn statement:

Example 1

# Create a new column called 'key' which combines the 'department'
# with the middle three digits of the 'user_id', separated by :

create mergedcolumn key separator : from department user_id /[0-9]{3}-([0-9]{3})/

# Result:
name,user_id,department,key
Eddy,123-456-123456,Development,Development:456
Tim,654-321-654321,Project Management,Project Management:321
Joram,555-222-999111,Development,Development:222
Joost,826-513-284928,Sales and Marketing,Sales and Marketing:513

Example 2

If no regular expression is specified then the values in the source column will be used in their entirety:

# Create a new column called 'key' which combines the 'department'
# and 'user_id' columns separated by ":", with prefix

create mergedcolumn key separator : from string prefix department user_id

# Result:
name,user_id,department,key
Eddy,123-456-123456,Development,prefix:Development:123-456-123456
Tim,654-321-654321,Project Management,prefix:Project Management:654-321-654321
Joram,555-222-999111,Development,prefix:Development:555-222-999111
Joost,826-513-284928,Sales and Marketing,prefix:Sales and Marketing:826-513-284928

Example 3

Let us add a new row to the sample dataset which has a non-compliant value for the user_id:

name,user_id,department
Eddy,123-456-123456,Development
Tim,654-321-654321,Project Management
John,xxx-xxx-xxxxxx,Pending
Joram,555-222-999111,Development
Joost,826-513-284928,Sales and Marketing

By default a non-matching value will result in a blank component of the merged result:

# Create a new column called 'key' which combines the 'department'
# with the middle three digits of the 'user_id', separated by :

create mergedcolumn key separator : from department user_id /[0-9]{3}-([0-9]{3})/

# Result:
name,user_id,department,key
Eddy,123-456-123456,Development,Development:456
Tim,654-321-654321,Project Management,Project Management:321
John,xxx-xxx-xxxxxx,Pending,Pending
Joram,555-222-999111,Development,Development:222
Joost,826-513-284928,Sales and Marketing,Sales and Marketing:513

In this case, the resulting key for John has no separator characters in it. We can force a default value for the missing user_id portion as follows:

option merge_nomatch = [none]
create mergedcolumn key separator : from department user_id /[0-9]{3}-([0-9]{3})/

# Result:
name,user_id,department,key
Eddy,123-456-123456,Development,Development:456
Tim,654-321-654321,Project Management,Project Management:321
John,xxx-xxx-xxxxxx,Pending,Pending:[none]
Joram,555-222-999111,Development,Development:222
Joost,826-513-284928,Sales and Marketing,Sales and Marketing:513

Overview

The if statement is used to conditionally execute one or more statements

Syntax

if (conditional expression) {
    <statements ...>
} [else {
    <statements ...>
}]

Conditional Expressions

A conditional expression (hereafter referred to as simple an expression) is evaluated to provide a TRUE or FALSE result which in turn determines whether one or more statements are to be executed or not. The following are examples of a valid expression:

(${dataDate} == 20180801)

((${dataDate} >= 20180801) && ([hostname] == "templateVM"))

An expression used by the if statement may contain:

• Numeric and string literals

• Regular expressions

• Variables

• Operators

• Functions

Numeric and string literals

A literal is a specified value, such as 4.5 or "hostname". Literals may be numbers or strings (text).

If a literal is non-quoted then it will be treated as a number if it represents a valid decimal integer or floating point number (in either regular or scientific notation), else it will be treated as a string.

If a literal is quoted then it is always treated as a string, thus 3.1515926 is a number and "3.1415926" is a string.

Regular expressions

Regular expressions must be enclosed within forward slashes (/), and are assumed to be in ECMAScript format.

If present, a regular expression must be used on the right hand side of either an !~ or an =~ operator, and when evaluated it will be applied to the value on the left hand side of an operator, eg:

if (${dataDate} =~ /[0-9]{4}01/) {
    var first_day_of_month = yes
} else {
    var first_day_of_month = no
}

As the forward slash is used as a delimiter for the expression, any literal forward slashes required by the expression should be escaped with a back-slash: \/

Variables

Variables can be used within expressions, in which case they are replaced with their values. Once expanded, these values are treated as literals.

Operators

Operators are evaluated according to the operator precedence rules in the table below (where the highest precedence is evaluated first), unless parentheses are used to override them. Operators with the same precedence are evaluated from left to right.

Functions

A function is used to evaluate one or more arguments and return a result which is then taken into consideration when evaluating the overall truth of the expression.

Function calls start with a the character @ which is followed by the function name and a comma separated list of parenthesised parameters, for example @MIN(1, 2, 3) .

The following functions are supported by the if statement:

Numeric functions

MIN

@MIN(number, number [, number ...])

Return the smallest number from the specified list (requires at least 2 arguments)

Examples:

• @MIN(1,2) returns 1

• @MIN(1,2,-3) returns -3

• @MIN(1,2,"-1") returns -1 - string "-1" is converted to number -1

• @MIN(1,2,3/6) returns 0.5

• @MIN(1,2,"3/6") returns 1 - string "3/6" is converted to number 3, up to first invalid character

• @MIN(1,2,"zzz") returns 0 - string "zzz" is converted to number 0

MAX

@MAX(number, number [, number ...])

Return the largest number from the specified list (requires at least 2 arguments)

Examples:

• @MAX(1,2) returns 2

• @MAX(-1,-2,-3) returns -1

• @MAX(1,2,100/10) returns 10

ROUND

@ROUND(number [, digits])

Returns number rounded to digits decimal places. If the digits argument is not specified then the function will round to the nearest integer.

Examples:

• @ROUND(3.1415,3) returns 3.142

• @ROUND(3.1415,2) returns 3.14

• @ROUND(3.1415926536,6) returns 3.141593

• @ROUND(3.1415) returns 3

• @ROUND(2.71828) returns 3

String functions

CONCAT

@CONCAT(string1, string2 [, stringN ...])

This function will treat all its arguments as strings, concatenate them and return the result.

Examples:

• @CONCAT("the answer ", "is") returns the answer is

• @CONCAT("the answer ", "is", " 42") returns the answer is 42

• @CONCAT("the answer ", "is", " ", 42) returns the answer is 42

SUBSTR

@SUBSTR(string, start [, length])

Return a sub-string of string, starting from the character at position start and continuing until the end of the string end until the character at position length, whichever is shorter.

Examples:

• @SUBSTR("abcdef", 1) returns abcdef

• @SUBSTR("abcdef", 3) returns cdef

• @SUBSTR("abcdef", 3, 2) returns cd

• @SUBSTR("abcdef", 3, 64) returns cdef

STRLEN

@STRLEN(string)

Returns the length of its argument in bytes.

Examples:

• @STRLEN("foo") returns 3

• @STRLEN(@CONCAT("ab", "cd")) returns 4

• @STRLEN(1000000) returns 7 (the number 1000000 is treated as a string)

PAD

@PAD(width, value [, pad_char])

This function returns value, left-padded with pad_char (0 by default) up to specified width. If width is less than or equal to the width of value, no padding occurs.

Examples:

• @PAD(5, 123) returns 00123

• @PAD(5, 12345) returns 12345

• @PAD(1, 12345) returns 12345

• @PAD(5, top, Z) returns ZZtop

EXTRACT_BEFORE

@EXTRACT_BEFORE(string, pattern)

This function returns the substring of string that precedes the pattern. If pattern cannot be found in the string, or either string or pattern are empty, result of the function is empty string.

Examples:

• @EXTRACT_BEFORE("abcdef", "d") returns ab

• @EXTRACT_BEFORE("abcbc", "bc") returns a

• @EXTRACT_BEFORE("abcdef", "x") returns empty string

EXTRACT_AFTER

@EXTRACT_AFTER(string, pattern)

This function returns the substring of string that follows the pattern. If pattern cannot be found in the string, or either string or pattern are empty, result of the function is empty string.

Examples:

• @EXTRACT_AFTER("abcdef", "cd") returns ef

• @EXTRACT_AFTER("abcabc", "ab") returns cabc

• @EXTRACT_AFTER("abcdef", "abb") returns empty string

EXTRACT_XXX functions can be combined to extract the middle part of the string, for example @EXTRACT_AFTER(@EXTRACT_BEFORE("abcdef", "ef"), "ab") returns cd.

Date functions

CURDATE

@CURDATE([format])

Returns the current (actual) date in the timezone of the Exivity server. The format may be any valid combination of strftime specifiers. The default format is %Y%m%d which returns a date in yyyyMMdd format.

Examples (assuming run date is 1 July 2019, at 12:34:56):

• @CURDATE() returns 20190701

• @CURDATE(\"%d-%b-%y\") returns 01-Jul-19

• @CURDATE("%H:%M:%S") returns 12:34:56

• @CURDATE("%u") returns 1 (weekday - Monday)

• @CURDATE("%j") returns 182 (day of the year)

DATEADD

@DATEADD(date, days)

Adds a specified number of days to the given date, returning the result as a yyyyMMdd date.

Invalid dates are normalised, where possible (see example below):

Examples:

• @DATEADD(20180101, 31) returns 20180201

• @DATEADD(20180101, 1) returns 20180102

• @DATEADD(20171232, 1) returns 20180102 (the invalid date 20171232 is normalised to 20180101)

• @DATEADD(20180101, 365) returns 20190101

DATEDIFF

@DATEDIFF(end_date, start_date)

Returns the difference in days between two yyyyMMdd dates. A positive result means that date1 is later than date2. A negative result means that date2 is later than date1. A result of 0 means that the two dates are the same.

Invalid dates are normalised, when possible (see example below):

Examples:

• @DATEDIFF(20190101, 20180101) returns 365

• @DATEDIFF(20180201, 20180101) returns 31

• @DATEDIFF(20180102, 20180101) returns 1

• @DATEDIFF(20180101, 20180102) returns -1

• @DATEDIFF(20180101, 20180101) returns 0

• @DATEDIFF(20171232, 20180101) returns 0 (the invalid date 20171232 is normalised to 20180101)

DTADD

@DTADD(datetime, count [, unit])

This function adds count number of units (DAYS by default) to the specified datetime value and return normalised result datetime value in YYYYMMDDhhmmss format.

Datetime can be in any of the following formats:

• YYYYMMDD

• YYYYMMDDhh

• YYYYMMDDhhmm

• YYYYMMDDhhmmss

All missing bits of datetime value assumed zeros.

Supported units are (both singular and plural spellings supported):

• YEAR

• MONTH

• DAY (default)

• HOUR

• MINUTE

• SECOND

Example

• @DTADD(20190701, 2) returns 20190703000000

• @DTADD(20190701, 2, HOURS) returns 20190701020000

• @DTADD(2019070112, 50, DAYS) returns 20190820120000

• @DTADD(20190701123456, 10, MONTH) returns 20200501123456

Transcript-specific functions

if (!@COLUMN_EXISTS("colName")) {
   The column colName does NOT exist
}

FILE_EXISTS

@FILE_EXISTS(filename)

Returns 1 if the file filename exists, else returns 0.

FILE_EMPTY

@FILE_EMPTY(filename)

In strict mode, this function returns 1 if the file filename exists and is empty. If the file does not exist, then this is considered an error.

In permissive mode, a non-existent file is considered equivalent to an existing empty file.

In either case, if the file exists and is not empty, the function returns 0

DSET_EXISTS

@DSET_EXISTS(dset.id)

Returns 1 if the specified DSET exists, else 0

DSET_EMPTY

In strict mode (option mode = strict), this function returns 1 if the specified DSET exists and is empty. If the DSET does not exist, then this is considered an error.

In permissive mode (option mode = permissive), a non-existent DSET is considered equivalent to an existing empty DSET.

In either case, if the DSET exists and is not empty, the function returns 0.

COLUMN_EXISTS

@COLUMN_EXISTS(column_name)

This function returns 1 if the specified column exists, else 0. The column name may be fully-qualified, but if it is not, then it is assumed to be in the default DSET.

DSET_ROWCOUNT

@DSET_ROWCOUNT(dset)

This function returns number of rows within specified DSET.

In permissive mode (option mode = permissive), a non-existent DSET is considered equivalent to an existing empty DSET, and zero is returned.

DSET_COLCOUNT

@DSET_COLCOUNT(dset)

This function returns number of columns within specified DSET.

In permissive mode (option mode = permissive), a non-existent DSET is considered equivalent to an existing empty DSET, and zero is returned.

Overview

The delete statement is used to delete one or more columns or rows from one or more DSETs.

Syntax

delete columns [except]ColName1 [... ColNameN]

delete blankcolumns

delete rows

delete dsetdset_id

Details

Deleting columns

The list of column names following the initial delete columns statement may be used in one of two ways:

1. The columns listed will be deleted

2. All columns except those listed will be deleted

Which method to use is determined by the presence (or otherwise) of the except keyword:

It is not possible to delete a column from a DSET which only contains a single column. When deleting a column, the memory used to index it, along with the memory used to store the contents of its cells is released. This may be useful in situations where memory is limited.

Deleting unwanted columns early in a Transcript task will also increase the the performance of many subsequent operations performed by the task.

The second keyword may be column instead of columns. Either way, the statement behaves identically.

Deleting blank columns

The delete blankcolumns statement will delete columns that only contain blank values from the default DSET.

Deleting rows

Deleting a DSET

Examples

Delete the column temp from the default DSET and the column Interim from the azure.Usage DSET: delete columns temp azure.Usage.Interim

Delete any columns in the default DSET that have no values in any rows: delete blankcolumns

Delete all rows where the VMID is 1234

Delete the DSET azure.rates delete dset azure.rates

Overview

The export statement is used to snapshot the data in a DSET and write it to disk as a Dataset.

Syntax

exportsource.aliasasfilename

Details

The exported file will be created under <base_dir>/exported. The filename parameter may include a path as well as the filename to export, so long as it does not contain the substring ".." and is not an absolute path. If the path contains one or more directories that do not exist then they will be created.

Exporting usage

The source.alias argument is the DSET ID to export (see for more information on DSET IDs).

The export statement can be used at any point during this process to save a CSV (conforming to Dataset format) which snapshots the data in the DSET at that moment in time. This can be used for a number of purposes including:

• Examining the state of data part-way through processing for debugging purposes

• Creating custom exports for subsequent import into 3rd part systems

• Producing output CSV files (which may potentially contain merged data from multiple sources) for subsequent processing with another Transcript

Examples

The following Transcript will import a quoted CSV file, add a ProcessedFlag column to it with a value of 1 in each row, and save it back out again without quotes:

Overview

The import statement is used to read a CSV file (which must conform to standards) from disk in order to create a which can then be processed by subsequent Transcript statements.

Syntax

Import from CSV files

importfilenamefromsource[aliasalias][options { ... }]

importfilenamesourcecustom_source[aliasalias][options { ... }]

Import from CCR files

import filename.ccr source custom_source [aliasalias]

Import from database tables

import ACCOUNT sourcecustom_source[aliasalias]

import USAGE fordatefromdb_namesourcecustom_source[aliasalias]

Details

Options

Data imported from a CSV file can be filtered as it is being read in order to reduce post-import processing time and memory overhead.

The import filters are configured via the options parameter, which if present is imemdiately followed by one or more name = value pairs enclosed within braces, for example:

Multiple options may be specified and each option must be placed on separate line, for example:

The options supported by import are as follows:

To specify a parameter or column name that contains spaces in an expression, use quotes within the square brackets as follows:

File name / pattern

If using a regular expression, the import options are applied to all files matching that expression. All files must have the same structure (after any select/ignore options have been applied).

Database tables

Account data

To import all accounts from Exivity the following statement is used:

import ACCOUNT sourcecustom_source[aliasalias]

This functionality is intended for advanced use cases where it is necessary to correlate information about existing accounts into the new data being processed as part of the Transform step.

Usage data

import USAGE fordatefromdsetsourcecustom_source[aliasalias]

This functionality may be useful in the event that the original data retrieved from an API has been deleted or is otherwise unavailable.

The date must be inyyyyMMddformat. The RDF from which the usage will be imported is:

<basedir>/system/report/<year>/<month>/<day>_<dset>_usage.rdf.

To illustrate this in practice, the statement ...

import USAGE for 20180501 from azure.usage source test alias data

... will load the usage data from ...

<basedir>/system/report/2018/05/01_azure.usage_usage.rdf

... into a DSET calledtest.data.

Source and Alias tags

A file imported from disk contains one or more named columns which are used to create an index for subsequent processing of the data in that file. As multiple files may be imported it is necessary to use namespaces to distinguish between the DSETs created from the files (each imported file is converted into a memory-resident DSET before it can be processed further). This is accomplished through the use of source and alias tags. Each file imported is given a unique source and alias tag, meaning that any column in the data imported from that file can be uniquely identified using a combination of source.alias.column_name.

There are two main variations of the import statement which are as follows:

Automatic source tagging

importfilenamefromsource[aliasalias]

<basedir>/system/extracted/<source>/<yyyy>/<MM>/dd>_.csv

Typically, the <source> portion of that path will reflect the name or identity of the external system that the data was collected from.

By default the alias tag will be set to the filename, minus the `_ portion of that filename and _.csv` extension but an alias can be manually specified using the optional _alias parameter.

As an example, assuming the data date is 20170223, the statement:

import usage from Azure

will create a DSET called Azure.usage from the file /system/extracted/Azure/2017/02/23_usage.csv.

The statement:

import usage from Azure alias custom

will create a DSET called Azure.custom from the file

<basedir>/system/extracted/Azure/2017/02/23_usage.csv.

Manual source tagging

importfilenamesourcecustom_source[aliasalias]

This form of the statement will import filename and the source tag will be set to the value of the custom_source parameter.

By default the alias tag will be set to the filename minus the .csv extension but an alias can be manually specified using the optional alias parameter.

As an example, assuming the data date is 20170223, the statement:

import "system/extracted/Azure/${dataDate}.csv" source Azure alias usage

will create a DSET called Azure.usage fom the file

<basedir>/system/extracted/Azure/20170223.csv

Importing CCR files

When performing an import using manual tagging it is possible to import a Cloud Cruiser CCR file. This is done by specifying the .ccr filename extension as follows:

Full information regarding CCR files is beyond the scope of this article. Cloud Cruiser documentation should be consulted if there is a requirement to know more about them.

In order to create a DSET from a CCR file, Transcript coverts the CCR file to a Dataset in memory as an interim step. The resulting DSET will have blank values in any given row for any heading where no dimension or measure in the CCR file existed that matched that heading. No distinction is made between dimensions and measures in the CCR file, they are imported as columns regardless.

When importing a CCR file, the quote and separator characters are always a qouble quote - " - and a comma - , - respectively. Fields in the CCR file may or may not be quoted. The conversion process will handle this automatically and any quotes at the start and end of a field will be removed.

Quote and Separator options

The options relating to import are quote and separator (or delimiter). By default, these are set to a double quote and a comma respectively.

If defined the quote character will be stripped from any fields beginning and ending with it. Any additional quote characters inside the outer quotes are preserved. Fields that do not have quotes around them will be imported correctly, unless they contain a quote character at only one end of the field.

If no quote character is defined, then quote characters are ignored during import, but any separator characters in the column headings will be converted to underscores.

Embeds

Examples

Create a DSET called Azure.usage from the file /system/extracted/Azure/2017/02/23_usage.csv import usage from Azure

Create a DSET called Azure.custom from the file /system/extracted/Azure/2017/02/23_usage.csv import usage from Azure alias custom

Create a DSET called Azure.usage fom the file /system/extracted/Azure/20170223.csv import "system/extracted/Azure/${dataDate}.csv" source Azure alias usage

Create a DSET called Azure.usage from the file /system/extracted/Azure/20170223.csv import "system/extracted/Azure/${dataDate}.csv" source Azure alias usage

Overview

The finish statement creates a (RDF) from a DSET. The RDF can subsequently be used by the reporting engine.

Syntax

finish[dset.id]

Details

The finish statement is used to create an RDF from a DSET. Only a single DSET can be used to create an RDF, but multiple finish statements may be used within the same task file. If there is no dset.id parameter then the default DSET will be used.

The RDF created by finish will be saved as <BaseDir>\system\report\<yyyy>\<MM>\<dd>_source.alias.rdf

where:

• <yyyy> is the 4-digit year

• <MM> is the 2-digit month

• <dd> is the 2-digit day

• source.alias are the tags which form the DSET ID

Any existing RDF with the same name will be overwritten.

Examples

Create a Reporting Database file for the default DSET: finish

Create a Reporting Database file for the DSET Azure.usage finish Azure.usage

Overview

The lowercase statement is used to modify the name of, and/or the values in, a column such that any upper case characters are replaced with their lower case equivalent.

Syntax

lowercase heading|values [and heading|values] in columnColName1 [... ColNameN]

Details

The heading and values keywords determine the scope of the processing to be applied. The columns named in ColName1 ... ColNameN may or may not be fully qualified. If a column name is not fully qualified then the default DSET will be assumed.

Only upper case characters in a column name or value are converted. Numbers, punctuation and other symbols are ignored. Any blank values in a column are ignored.

Examples

Overview

The include statement is used to combine Transcript task files together

Syntax

includetaskfile

Details

The include statement is used to combine two or more task files into a single script prior to commencement of execution.

Before executing a task file, Transcript analyses the script and processes all the include statements. Wherever an include statement is encountered, the specified taskfile is read from disk and inserted into the script in place of the include statement itself.

Command placement

As with most statements, include must occupy a single line of its own in the script.

Additional notes

The taskfile argument is treated as relative to <basedir>/system/config/transcript/.

Multiple files may be included in a task file and any included file may include other files but a check is done to ensure that no infinite include loops are generated.

Example

Given the following two files in /system/config/transcript/ ...

# FILE 1: myscript.trs
option loglevel = DEBUGX

# import usage data from csp
import "system\extracted\AzureCSP\${dataDate}_csp_usage.csv" source CSP alias Usage
default dset csp.usage
rename column resource_id to meter_id

include import_customers.trs

option overwrite = no
set resource_subcategory to Generic
create column interval value individually
create mergedcolumn service_name separator " - " from resource_name resource_subcategory region
# etc ...

# FILE 2: import_customers.trs
import "system\extracted\AzureCSP\${dataDate}_csp_customers.csv" source CSP alias Customers
rename column CSP.Customers.ID to customer_id
# END FILE 2

Prior to execution the pre-processor will combine these into the single memory-resident Transcript task:

# FILE 1: myscript.trs
option loglevel = DEBUGX

# import usage data from csp
import "system\extracted\AzureCSP\${dataDate}_csp_usage.csv" source CSP alias Usage
default dset csp.usage
rename column resource_id to meter_id

# FILE 2: import_customers.trs
import "system\extracted\AzureCSP\${dataDate}_csp_customers.csv" source CSP alias Customers
rename column CSP.Customers.ID to customer_id
# END FILE 2

option overwrite = no
set resource_subcategory to Generic
create column interval value individually
create mergedcolumn service_name separator " - " from resource_name resource_subcategory region
# etc ...

Overview

The rename statement is used to change the name of an existing column in a DSET, or to change the source and/or alias of a DSET

Syntax

rename columnOldNametoNewName

rename dsetOldSource.OldAliastoNewSource.NewAlias

Details

Renaming a column

When renaming a column, OldName may be a fully qualified column name. If it is not fully qualified then the column OldName in the default DSET will be renamed.

The NewName argument must not be fully qualified.

Renaming a DSET

When renaming a DSET, NewSource.NewAlias must contain exactly one dot and must not be the name of an existing DSET. The rename takes immediate effect, thus any subsequent reference to the renamed DSET in the transcript task must use its new name.

Any pending service definitions referencing the renamed DSET will automatically be updated with the new name.

Examples

Renaming columns:

# Rename the column 'UnitCost' in the default DSET to 'Cost'
rename column UnitCost to Cost

# Rename the column 'UnitCost' in the DSET 'custom.prices' to 'CustomCost'
rename column custom.prices.UnitCost to CustomCost

Renaming a DSET:

# Usage file has yyyyMMdd in filename
import "system/extracted/AzureJuly/${dataDate}.ccr" source AzureJuly

# The resulting DSET will have an ID of 'AzureJuly.yyyyMMdd'
# where yyyyMMdd is the current dataDate

#
# Any additional processing would be done here
#

# Remove the yyyyMMddfrom the DSET ID
rename dset AzureJuly.${dataDate} to Azure.July

# Create 'Azure.July.rdf'
finish

Overview

The service statement is used to create or modify a single service definition during the execution of a Transcript task. The service definition is associated with the data in an existing DSET, and the global database is updated if and when the Transcript task successfully completes.

Syntax

service{ param1 = value [... paramN = value] }

Example:

service {
    key = "A1 VM"
    usage_col = "A1 VM - EU North"
    description = "A1 VM (EU North)"
    category = "Virtual Machines"
    interval = monthly
    model = unprorated
    unit_label = "Instances"
    rate = 0.8
    fixed_price = 10
    cogs = 45
    fixed_cogs = 16
    min_commit = 4
}

Details

Summary

The service statement is used to create a new service definition. Once created, a service definition automatically becomes available to the reporting engine.

Parameter table

The service statement creates a new service using the following parameters:

Parameter details

key

The key parameter must be distinct from that in any other service unless it is used to identify an existing service definition to be overwritten with new values. By default, an attempt to create a service with a key that is a duplicate to an existing service in the global database will result in a warning in the logfile and no further action will be taken.

If the key matches that of a service that has been defined previously in the task file then one of two things can happen depending on the value of the current execution mode:

1. If the mode is set to strict then an error is logged and the task will fail

2. If the mode is set to permissive then a warning is logged and the newest service definition is ignored

The value of the key parameter may be up to 127 characters in length. Longer names will be truncated (and as a result may no longer be unique).

description

The description parameter is freely definable and does not have to be unique. When a report is generated, the description of a service will be used on that report so care should be taken to make the description meaningful.

By default, if description is not specified in a service definition then a copy of the key will be used as the description.

The value of the description parameter may be up to 255 characters in length. Longer descriptions will be truncated.

category / group

The category parameter is used to logically associate a service with other services. All services sharing the same category will be grouped together on reports. Any number of different categories may exist and if the category specified does not exist then it will be automatically created.

If no category is specified then the service will be placed into a category called Default.

The value of the category parameter may be up to 63 characters in length. Longer values will be truncated.

usage_col

In order to calculate a price for a service, the number of units of that service that were consumed needs to be known. The value of the usage_col parameter specifies the column in the usage data which contains this figure.

The usage_col argument is also used to derive the DSET associated with the service as follows:

• If the value of usage_col is a fully qualified column name then the service will be associated with the DSET identified by that name

• If the value of usage_col is not fully qualified then the service will be associated with the default DSET

If the column specified by the usage_col argument is determined not to exist using the above checks, then one of two things can happen depending on the value of the current execution mode:

1. If the mode is set to strict then an error is logged and the task will fail

2. If the mode is set to permissive then a warning is logged and the service definition is ignored

The value of the usage_col parameter may be up to 255 characters in length. Longer values will be truncated.

interval

Services may be charged in different ways. For example some services will invoke a charge whenever they are used whereas others are charged by a time interval, such as per month.

The value of the interval parameter determines how the service should be charged as per the following table:

model

Specifies whether or not to apply proration when calculating the charge for a monthly service.

unit_label

The unit_label is the label used for units of consumption on reports. For example storage-related services may be measured in Gb or Tb, Virtual Machines may be measured in Instances and software usage may be measured in licenses.

The specified unit_label value may be up to 63 characters in length. Longer values will be truncated.

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

account_id

The optional account_id references an entry in the account table in the global database. If specified, the service will be created with a rate revision specific to the account id.

The default rate for the service (which applies to all combinations of report column values not explicitly associated with their own rate) can be defined in any of the following ways:

• By omitting the account_id parameter altogether

• By specifying an account_id of 0

• By specifying an account_id of *

rate

The rate parameter determines the cost per unit of consumption to use when calculating the charge. A rate of 0.0 may be used if there is no charge associated with the service.

This may be used in conjunction with a fixed_price and one of cogs or fixed_cogs.

Either or both of rate or fixed_price may be specified, but at least one of them is required.

fixed_price

The fixed_price is a charge applied to the service per charge interval regardless of the units of consumption.

This may be used in conjunction with a rate and one of cogs or fixed_cogs

Either or both of rate or fixed_price may be specified, but at least one of them is required.

cogs

The optional COGS charge is the price to the provider of the service. Usually, COGS-related charges are not included on reports special permissions are required to see the COGS charges.

For services with a defined cogs value it is possible to generate Profit and Loss reports, the profit/loss being the total charge calculated from the rate and/or fixed_rate values minus the price calculated from the cogs or fixed_cogs values.

fixed_cogs

The optional fixed_cogs value is a fixed price to be factored into COGS-related calculations regardless of the number of units consumed in the charging interval.

min_commit

The optional min_commit parameter specifies a minimum number of units of consumption to include when calculating the charge associated with a service. In cases where the actual consumption is greater than the min_commit value this will have no effect, but where the actual consumption is less than the minimum commit the price will be calculated as if the min_commit units had been consumed.

charge_model

If specified as peak then the charge for monthly service created will be calculated based on the day with the highest charge in the month.

If specified as average then the charge for monthly service created will be calculated as the average unit price (for days that have usage only) multiplied by the average quantity (days with no usage will be treated as if they had a quantity of 0).

If specified as last_day then the charge for monthly service created will be calculated based on the last day of the month.

If specified as day_xxx (where xxx is a number in 1-28 range) then the charge for monthly service created will be calculated based on the specified day of the month.

The Service Definition cache

Once all parameters have been evaluated by the service statement the resulting service definition is added to a cache in memory. This cache is committed to the global database at the successful conclusion of the Transcript task (or discarded in the case of error).

Once a service definition has been added to the cache, no subsequent service definitions with the same key may be added to the cache. In the event of conflict the first definition written to the cache is perserved and the attempt to add a duplicate will result in a warning or an error depending on the value of the current execution mode as described in the key section above.

Rate revisions

As described in Services in Exivity a service may have multiple rate revisions associated with it. The service statement can only create a single rate revision per service per execution of a Transcript task.

A rate revision consists of the rate, fixed_rate, cogs, fixed_cogs and effective_date parameters. Each revision must have a different effective_date which indicates the date from which that service revision is to be used. A rate definition remains in force for all dates on or after the effective_date, or until such time as a rate revision with a later effective_date is defined (at which point that revision comes into effect).

To create multiple revisions, Transcript must be run multiple times using a service statement that has the same key and uses the same rate, fixed_rate, cogs and fixed_cogs values but has a different effective_date each time. For each of the effective_date parameters a new rate revision will be created for the service.

Updating the Global Database

At the successful conclusion of a Transcript task the global database is updated from the memory cache.

Examples

service {
    key = "Azure A1 VM"
    usage_col = "A1 VM - EU North"
    description = "Azure A1 VM (EU North)"
    category = "Virtual Machines"
    interval = monthly
    model = unprorated
    unit_label = "instances"
    min_commit = 2
    rate = 0.8
    fixed_price = 0.2
    cogs = 0.55
}

Overview

The update_service statement is used to update service descriptions and/or unit labels.

Syntax

update_serviceservice_key[descriptionnew_description][labelnew_label]

update_service using filefile key_col[descriptiondescription_col][labellabel_col]

update_service using dsetkey_col[descriptiondescription_col][labellabel_col]

Details

The update_service statement can:

• update a single service using a specific key

• update all services whose keys are present in a DSET or CSV lookup source

Update a single service

update_serviceservice_key[descriptionnew_description][labelnew_label]

The service with a key of service_key is updated with the literal description and label provided.

Update multiple services using a CSV lookup file

update_service using filefile key_col[descriptiondescription_col][labellabel_col]

All services keys that match values in the key_col column in the CSV called file have their descriptions and/or labels updated with the values in the corresponding description_col and label_col columns in file.

Update multiple services using a DSET

update_service using dsetkey_col[descriptiondescription_col][labellabel_col]

The DSET ID is derived from the key_col column as follows:

• If not fully qualified then the default DSET is assumed

In either case, whichever is used, or both, of description_col and label_col must be located in the same DSET as key_col

Examples

Overview

The split statement is used to create and/or update columns by splitting the textual value of an existing column into multiple parts.

Syntax

splitColNameusingsep

splitColNameusingsepretaining first[column_count]

splitColNameusingsepretaining last[column_count]

splitColNameusingsepretainingfirst_column_index[tolast_column_index]

Details

The ColName argument is the name of an existing column whose values are to be split and the sep argument (which must only be a single character in length) is the delimiter by which to split them.

For example a value one:two:three:four, if divided by a sep character of :, would result in the fields one, two, three and four.

New columns are created if necessary to contain the values resulting from the split. These additional columns are named ColName_split1 through ColName_splitN where N is the number of values resulting from the process.

If there is an existing column with a name conflicting with any of the columns that split creates then:

• • If there are blank values in the existing column they will be updated

  • If there are no blank values in the existing column then no changes will be made to it

Keeping only specific fields

If the retaining keyword is specified, split will discard one or more of the resulting columns automatically based on the specification that follows the keyword. The following specifications are supported:

Example

Given an input dataset of the form:

The statement ...

split ID using :

... will result in the dataset:

Using the same original dataset, the statement ...

split ID using : retaining 3 to 5

... will result in the dataset:

Overview

The normalise statement is used to update the values in a numerical column such that they are all positive, negative or inverted.

Syntax

normalise columncolNameas positive

normalise columncolNameas negative

normalise columncolNameas invert

normalise columncolNameas standard

Details

The normalise statement processes each value in the column called colName and applies the following logic based on the last argument shown above as follows:

In order to be considered a number, a value in the colName column must start with any of the characters +, -, . or 0 to 9 and may contain a single . character which is interpreted as a decimal point.

Additionally:

• Any numerical value in colName which starts with a +, . or decimal character is considered positive

• Any numerical value in colName which starts with a - character is considered negative

• When using standard the resulting conventional number will be accurate up to 14 decimal places

The normalise statement ignores the option overwrite setting, as its sole purpose is to modify existing values.

Example

import "system/extracted/csp_usage.csv" source test alias data

# Invert all numerical values in column 'quantity'
normalise column quantity as invert

Overview

The replace statement is used to search for, remove, and optionally replace with new values, substrings of the values in a column.

Syntax

replacesubstringincolName[withreplacement]

Details

The replace statement will remove or replace all occurrences of substring in the values of a column. The parameters are as follows:

The colName argument may or may not be fully qualified, but must reference an existing column.

If the optional replacement string is provided then any occurrences of substring will be substituted with the specified value.

If the replacement string is not provided then all occurrences of substring will be removed from the values in colName.

Example

Given the following sample data:

PayerAccountName,TaxationAddress,ProductCode,ProductName,ItemDescription
John Doe,"123 Big St, Largetown, RH15 0HZ, United Kingdom",AWSDataTransfer,AWS Data Transfer,$0.00 per GB - EU (Germany) data transfer from EU (Ireland)
John Doe,"123 Big St, Largetown, RH15 0HZ, United Kingdom",AmazonS3,Amazon Simple Storage Service,$0.0245 per GB - first 50 TB / month of storage used
John Doe,"123 Big St, Largetown, RH15 0HZ, United Kingdom",AWSDataTransfer,AWS Data Transfer,$0.00 per GB - EU (Germany) data transfer from US West (Northern California)
John Doe,"123 Big St, Largetown, RH15 0HZ, United Kingdom",AWSDataTransfer,AWS Data Transfer,$0.090 per GB - first 10 TB / month data transfer out beyond the global free tier
John Doe,"123 Big St, Largetown, RH15 0HZ, United Kingdom",AWSDataTransfer,AWS Data Transfer,$0.00 per GB - EU (Germany) data transfer from US East (Northern Virginia)

The following script ...

import "system\extracted\example.csv" source test alias data
replace "John Doe" in PayerAccountName with "Finance Dept"
replace "RH15 0HZ, " in TaxationAddress
replace "United Kingdom" in TaxationAddress with UK
replace "AWS" in ProductCode

where ([ProductCode] == "AmazonS3") {
    replace "Amazon" in ProductCode
}

replace "Amazon " in ProductName
replace "AWS " in ProductName
replace "transfer from" in ItemDescription with -

... will produce the following output data.

"PayerAccountName","TaxationAddress","ProductCode","ProductName","ItemDescription"
"Finance Dept","123 Big St, Largetown, UK","DataTransfer","Data Transfer","$0.00 per GB - EU (Germany) data - EU (Ireland)"
"Finance Dept","123 Big St, Largetown, UK","S3","Simple Storage Service","$0.0245 per GB - first 50 TB / month of storage used"
"Finance Dept","123 Big St, Largetown, UK","DataTransfer","Data Transfer","$0.00 per GB - EU (Germany) data - US West (Northern California)"
"Finance Dept","123 Big St, Largetown, UK","DataTransfer","Data Transfer","$0.090 per GB - first 10 TB / month data transfer out beyond the global free tier"
"Finance Dept","123 Big St, Largetown, UK","DataTransfer","Data Transfer","$0.00 per GB - EU (Germany) data - US East (Northern Virginia)"

Overview

The round statement is used to ensure that numeric values in a column are whole multiples of a specified number.

Syntax

roundcolName [direction][to nearestvalue]

Details

The round statement is used to round numbers to the nearest multiple of any integer or floating point number.

The parameters supported by round are as follows:

The simplest form of the statement is round colName. This will use the defaults shown in the table above such that values are rounded to the next highest integer. Alternatively, the statement round colName down will round down to the nearest integer.

If the value argument is provided then the numbers in colName will be rounded up or down to the nearest multiple of value. For example the statement ...

round Quantity up to nearest 5

... will round the numbers in the column Quantity up to the next multiple of 5. Thus any number in Quantity higher than 10 and less than 15 will be rounded to 15.

Additional notes

The round statement observes the following rules:

• Non-numeric values in colName are ignored

• Blank values, or a value of 0 in colName are ignored

• Numbers in colName that are already a whole multiple of value will not be modified

The round statement may be used in the body of a where statement to perform conditional rounding.

Examples

round Quantity                       # Round up to nearest integer
round Quantity down                  # Round down to nearest integer
round Quantity up to nearest 4       # Round up to next multiple of 4
round Quantity down to nearest 2     # Round down to next lowest even number

round Quantity up to nearest 0.5     # ie: 2.25 -> 2.5
round Quantity down to nearest 0.1   # ie: 23.34567 -> 23.3
round Quantity down to nearest 0.01  # ie: 23.34567 -> 23.34

where ( ([quantity] > 5) && ([quantity] <= 100) ) {
    round quantity up to nearest 10       # Force consumption in blocks of 10
}

where ([quantity] > 100) {
       round quantity up to nearest 5     # Better deals for larger consumption
}

Overview

The option statement is used to set global parameters during the execution of a Transcript task.

Syntax

optionoption = setting

option noquote

Details

The option statement can be used multiple times within the same task script and always takes immediate effect. It is therefore possible (for example) to import a CSV file delimited with commas and quoted with single quotes, change the options and then export it with semicolon delimiters and double quotes.

The supported options are as follows: