AX2659

About data conversions

You can use data conversions to take the data in a table and convert it using a multiplication factor. For example, data conversions can be used to perform currency conversions.

Data conversions are set up as follows:

  • You create a conversion table that holds the multiplication factors for the data conversion. This table must be set up in a specific manner in order to enable the conversion. For example, this table must have:

    • Two key columns to specify the "from" and "to" conversion. For example, you may be converting USD to Euro.
    • One key column to specify the conversion type. For example, if you are setting up currency conversions, you may calculate some values using an average monthly rate, and some values using an end-of-month rate.
    • One key column to specify the scenario. For example, if you are forecasting using currency conversions, you might have a "best case" and "worst case" scenario for the forecasted rates.
    • Any number of columns to hold the conversion rates. For example, if you are converting the data using a monthly rate, you would have 12 columns, one for each month.

    For more information on the conversion table requirements, see Conversion table requirements.

  • The conversion table and the data table must each have a defined sequence to map the data columns to the appropriate multiplication factors. For example, the data in column M1 of the data table is multiplied by the rate in column M1 of the conversion table. The sequences must each have the same number of columns that will correspond directly.
  • You enable the conversion by setting up the required fields on the Conversion tab for the data table. As part of this process, you point the data table to the relevant conversion table, and specify other conversion properties such as the default "from" value.

Once data conversions have been set up for a table, you can query the converted data by specifying a conversion target on a GetData function or on an Axiom query. For more information, see Querying data using data conversions.

When the data is queried, the data is converted from the "from" value to the "to" value, using the relevant multiplication factor in the conversion table. The multiplication factor for any particular query is determined as follows:

  • The "from" value is obtained from the conversion configuration for the data table being queried. The "from" value is either a fixed value defined in the configuration, or looked up from a specified grouping column.
  • The "to" value is specified within the GetData function or in the Axiom query settings, as the conversion target. The conversion target can be a fixed value, or looked up from a specified grouping column.
  • The "type" value is obtained from the conversion configuration for the data table being queried. The type value is either a fixed value defined in the configuration, or looked up from a specified grouping column. The type can also be overridden on a per Axiom query basis (but not for GetData functions).
  • The "scenario" value is obtained from the conversion configuration for the data table being queried. The scenario value is a fixed value defined in the configuration. The scenario can also be overridden on a per Axiom query basis (but not for GetData functions).
  • The rate for the conversion is obtained based on the column being queried. If the column is the 5th column in the data table sequence, then the conversion rate is defined in the 5th column of the conversion table sequence.

Data conversions and calculated fields

Data conversions are applied to calculated fields, as long as the calculated field is based on the applicable sequence in the data table. To arrive at the converted calculated field value, the data conversion is first applied to the relevant columns for the calculation, and then the calculation is applied.

For example, if you have a calculated field for YTD and the current period is 3, then the relevant columns for the calculation are M1, M2, and M3. Axiom first applies the data conversion to these columns, and then performs the YTD calculation.

Security and conversion tables

Users can query data using data conversions without needing to have security access to the conversion table. Only those users who need to edit the conversion table directly must have security access to the conversion table.

For example, you may create a table type named Conversions and assign all conversion tables to that table type. End users can have no access to this table type, and they will still be able to query converted data. However, users who need to be able to edit the conversion tables must be granted full or filtered access.

Calculations for data conversions

The only calculation available for data conversions is straight multiplication. The data in the data table is multiplied by the rate in the conversion table, and the result is the converted data. No other calculation can be used.

Drilling and data conversions

Data conversion is applied to drill-down data as follows:

  • For Axiom queries, the data conversion settings for the original query are applied to the drill-down data.
  • For GetData rows, if all GetData functions in the row use the same conversion target, then that target is applied to the drill-down data. If the GetData functions use different conversion targets, or if some of the GetData functions do not use data conversion, then data conversion is not applied to the drill-down data.

Data conversion is not applied to drill-through data. Data is presented as it exists in the target detail table.