You can use flex.bi advanced settings to add measures, properties or dimensions for existing cubes for HansaWorld application and edit existing cube import structure for other applications.

Additional custom fields

If you would like to import in flex.bi additional fields from a custom field that is not supported by default, then you can add a definition of how this custom field should be imported. Also, if some default custom field settings should be changed, then it can be done with adding necessary custom field configuration parameters.

1. Find the field name

First, you need to find out the register and REST API code for the field. You can find codes for registers in the HanasWorld source File import documentation page. Then you can use the code to find code for REST API field in HansaWorld Module Technics → Reports → Import/Export format

You can see in the example screenshot below the register code (1.) and codes for REST API fields (2.).

2. Configure custom field settings

Register code and register REST API code

[register_code.register_rest_api_code]

Where register_code is register code and register_rest_api_code is the register REST API code. This will start the block of configuration parameters for the custom field. 

Name for the custom field

A name for the field describing the logic in the most understandable for you way e.g.

name = "Price"

Cube Name

The cube name where the custom field will be available. You can find the full cube names in Analyze section in your flex.bi.

cube_name = "HansaWorld Invoices"

Dimension name in flex.bi for the register

Name of the dimension to which this field should be related to e.g. Invoices, Transactions, Quotations etc.

dimension_name = "Invoice"

Dimension level name

The name of the level within the specified dimension, where the field should be added e.g.

level_name = "Invoice Item"

Dimension item level names for registers that do not have dimensions: "Transaction Item", "Simulation Item".

Data type of this field

One of the supported data types - integer, value, string e.g.

data_type = "string"

By default, the string maximum length is 255 characters. If there is a need to import longer string values, then use the additional limit setting to specify the maximum length.
Other available types are text, integerdecimal, datedatetime.

If you use the decimal data type, then by default the precision (maximum number of digits) is 15 and the scale (digits after the decimal point) is 2. You can change these defaults with the additional precision and scale settings.

Additional options

If you would like to import custom field as additional dimension then add

dimension = true

If you would like to import custom field as a measure then add

measure = true

If you would like to do operation with the incoming data before fields are imported you can use custom javascript code

javascript_code ='''
  if (doc.rows) {
    doc.rows.forEach(function(row){
    row.negative_rowGP = String(Number(row.rowGP) * -1);
  })
}

'''

If you would like to share the added measure to another cube then specify the cube name with the cubes_for_measure_sharing parameter:

cubes_for_measure_sharing = ["HansaWorld Invoices"]

If you are importing HansaWorld's data with files then the number of the field also needs to be defined. You can find what number is each field in your HansaWorld's "Technics → Reports → Export/import format" by specifying the register's code that you are importing. 

file_field = 4

If an REST API field contains a full stop characters (“.“) or the same REST API should be used twice you can use rest_api_field to define the REST API field name instead.

rest_api_field = 'T.NAME'

Example of custom dimension definition

[ORVc.LangCode]
name = "Language code"
cube_name = "HansaWorld Sales Orders"
data_type = "string"
dimension_name = "Sales Order"
level_name = "Sales Order"
dimension = true

Example of custom measure definition with javascript

[QtVc.negative_rowGP]
name = "Quotation negative GP"
cube_name = "HansaWorld Quotations"
data_type = "decimal"
dimension_name = "Quotation"
level_name = "Quotation Item"
measure = true
javascript_code ='''
if (doc.rows) {
  doc.rows.forEach(function(row){
    row.negative_rowGP = String(Number(row.rowGP) * -1);
  })
}
'''

If the data need to be linked to another date field then use custom_date_measure_group to define the name for the measure group and also the prefix for the duplicated measures.

custom_date_measure_group = 'Planned'

Example for both record and item level below.

[SHVc.Planned_Send_date]
rest_api_field = 'PlanSendDate'
name = 'Planned Send Date'
dimension_name = 'Delivery'
cube_name = 'HansaWorld Deliveries'
custom_date_measure_group = 'Planned'

[SHVc.Plan_Send_DateRow]
name = 'Item Planned send Date'
dimension_name = 'Delivery'
level_name = 'Delivery Item'
cube_name = 'HansaWorld Deliveries'
custom_date_measure_group = 'Planned'
javascript_code ='''
  if (doc.rows && doc.PlanSendDate) {
    doc.rows.forEach(function(row){
      row.Plan_Send_DateRow = doc.PlanSendDate;
    })
  }
'''

Add additional custom field definitions as needed in advanced settings text area field and then click Save.

3. Import the custom field

Finally, Edit the HansaWorld source application again and in "Cube properties" section select your new defined custom field for import.

Save the application settings and start the import. After the import is finished start using your property, measure or dimension in your reports.

In case of any questions please contact support@flex.bi for more information. 

Building a cube from advanced settings for custom fields

Currently Tilde and Horizon application cube structure is built using custom field definitions and it can be modified in the application's Advances settings section.

The predefined custom fields are loaded from a file on the server, that can be found here:

.../config/advanced_settings/{application_type}_custom_fields.toml

The following are custom field parameters used especially for defining cube structure:

Parameter NameExampleDescription
default_custom_fielddefault_custom_field = trueThis parameter specifies that this field is loaded from the default custom fields file and is required for building the standard cube structure. A custom field with this parameter enabled can't be de-selected for import. Also, if this parameter is set, then the custom field definition is not validated.
dimension_in_separate_tabledimension_in_separate_table = trueThis parameter specifies that the particular dimension will be created in a separate table. This is a mandatory parameter for new dimension creation. This parameter can be used together with the following additional parameters, to define dimension structure and binding:  key_field, name_field, source_id_field. Or with the parameter property, to add a property to the dimension.
key_fieldkey_field = trueThis parameter specifies that this field will be used as the key field for binding the dimension to the measures and as the first part of dimension member's name ("key_field - name_field").
name_fieldname_field = trueThis parameter specifies that this field will be used as the second part of dimension member's name ("key_field - name_field").
source_id_fieldsource_id_field = trueThis parameter specifies that this field should be used for binding the dimension to the measures instead of the key field. For example, when importing data from a relational database, this could be the primary key of the table that is used for dimension creation.
for_custom_dimensionfor_custom_dimension = 'Customer'

This parameter specifies that this field from the measures register or table will be used for binding the measures to the particular dimension. This parameter should be used together with the following parameters, to specify the particular use of the custom field:   key_field, name_field, source_id_field.

import_sort_dateimport_sort_date = trueThis parameter specifies that this field  will be used for limiting data requests from the particular register or table. For instance, it will be used to get data starting from a particular start date, to rewrite data for a specified refresh period and also for incremental import of only those data lines that have been changed since the last import run.
skip_requestskip_request = true

This parameter specifies that the REST API request should be skipped for this field. This can be useful when the field has been already requested in another custom field or the field is calculated with Javascript.

bind_field_namebind_field_name =  'status_code'

This parameter specifies what custom field's register code should be used for binding measures to another dimension. With this parameter the same field can be referenced multiple times. 

In the example below, a Customer dimension with two hierarchy levels Customer type and Customer is defined. This dimension contains both key and name fields for both levels and is linked  to the measures (Transaction dimension) using customer_code key field. In this example, customer_name is defined both for Customer and Transaction dimension import. This allows for Customer dimension member creation from the Transaction record in case a matching customer code is not found in the Customer dimension.

[TdmGramatSL.customer_code]
default_custom_field = true
name = 'Transaction Customer Code'
dimension_name = 'Transaction'
cube_name = 'Horizon Financials'
for_custom_dimension = 'Customer'
bind_field_name = 'customer_code'
rest_api_field = 'D.K.KODS'

[TDdmKlBaseSar.customer_type_code]
default_custom_field = true
name = 'Customer Type Code'
dimension_name = 'Customer'
level_name = 'Customer Type'
cube_name = 'Horizon Financials'
key_field = true
rest_api_field = 'K.KTIPS'
dimension_in_separate_table = true

[TDdmKlBaseSar.customer_type_name]
default_custom_field = true
skip_request = true
rest_api_field = 'T.NOSAUK'
name = 'Customer Type Name'
dimension_name = 'Customer'
level_name = 'Customer Type'
cube_name = 'Horizon Financials'
name_field = true
dimension_in_separate_table = true
javascript_code = '''
cust_type = doc.K_KTIPS;
switch(cust_type) {
  case "0":
    doc.T_NOSAUK =  "Active";
    break;
  case "1":
    doc.T_NOSAUK =  "Potential";
    break; }
'''

[TDdmKlBaseSar.customer_code]
default_custom_field = true
name = 'Customer Code'
dimension_name = 'Customer'
cube_name = 'Horizon Financials'
rest_api_field = "K.KODS"
key_field = true
dimension_in_separate_table = true

[TDdmKlBaseSar.customer_name]
default_custom_field = true
name = 'Customer Name'
dimension_name = 'Customer'
cube_name = 'Horizon Financials'
name_field = true
rest_api_field = "K.NOSAUK"
dimension_in_separate_table = true
CODE

In the example below, a Currency dimension is created and linked to the measures (Transaction dimension), using a source id field.

[FinancialDocLine.currency_id]
default_custom_field = true
name = 'Transaction Currency ID'
dimension_name = 'Transaction'
cube_name = 'Jumis Financials'
for_custom_dimension = 'Currency'
bind_field_name = 'currency_id'
rest_api_field = 'FinancialDocLine.CurrencyID'

[Currency.currency_code]
default_custom_field = true
name = 'Currency Code'
dimension_name = 'Currency'
cube_name = 'Jumis Financials'
rest_api_field = "CurrencyCode"
key_field = true
dimension_in_separate_table = true

[Currency.currency_name]
default_custom_field = true
name = 'Currency Name'
dimension_name = 'Currency'
cube_name = 'Jumis Financials'
name_field = true
rest_api_field = "Description"
dimension_in_separate_table = true

[Currency.currency_id]
default_custom_field = true
name = 'Currency ID'
dimension_name = 'Currency'
cube_name = 'Jumis Financials'
source_id_field = true
rest_api_field = "CurrencyID"
dimension_in_separate_table = true
CODE

In the example below field FinancialDocLine.status_code has to be used twice so bind_field_name field is used to reference the same field twice. 

[FinancialDocLine.status_code]
rest_api_field = 'FinancialDoc.Disbursement'
default_custom_field = true
name = 'Disbursement Status Code'
dimension_name = 'Disbursement Status'
cube_name = 'Jumis Financials'
dimension_in_separate_table = true
key_field = true

[FinancialDocLine.status_name]
skip_request = true
default_custom_field = true
name = 'Disbursement Status Name'
dimension_name = 'Disbursement Status'
cube_name = 'Jumis Financials'
dimension_in_separate_table = true
name_field = true

[FinancialDocLine.a_disbursement_flag]
rest_api_field = 'FinancialDoc.Disbursement'
default_custom_field = true
name = 'Transaction Document Disbursement Flag'
dimension_name = 'Transaction'
for_custom_dimension = 'Disbursement Status'
cube_name = 'Jumis Financials'
bind_field_name = 'status_code'
javascript_code = '''
if (doc.FinancialDocDisbursement === true) 
  {doc.FinancialDocDisbursement = 'true'}
else 
  {doc.FinancialDocDisbursement = 'false'};
'''
CODE