How to Add a Data Field in Data Source

To add a data field in a Data Source:

  1. Click a Data Source's label to display its details.

  2. On the details page, click the Add Field button to add a new data field.
    You can also use the Add Fields option. For details see How to Upload CSV Data into Analytics.  

  3. Select the Function of the field.
    Not all fields must have a function, they can be left blank. The options are:

    • Alt-Key – This field is used when a source represents data that is to be optionally matched to Datamart rows using different fields.
      Example: The source ‘Customer Discount’ specifies a % discount to be applied to an invoice amount, either for a given customer or a customer group. A customer discount row may have either a Customer ID or a Customer Group ID field value, but not both. If these fields are defined as alt-keys, Pricefx will attempt to match rows using the key with the lowest rank first. If unsuccessful, the second, third etc. key will be attempted.

    • Level – This field is used when a source represents data on different, hierarchical levels.
      Example: The source ‘Product Discount’ specifies a discount of the list price to be applied for a given product; or in absence of such detail, for all products in a given product family; or a product line, etc. On Datamart refresh, using Level key fields, the system will first find rows with all level fields populated (and matching Datamart rows). Then it will try all rows where the lowest level (the one with the highest rank) matches, and so on.

    • Distribution Key – A single column that is used as a key for the Distributed Datamart or Distributed Data Source. When selecting the distribution column, it is important to consider certain limitations and desired characteristics to ensure optimal performance and efficiency.

      • Values in this column cannot be NULL.

      • A Primary Key on a distributed table requires the distribution column to be part of the unique constraint definition.

      • Distribution Key should have high cardinality and an even distribution of values.

      • Distribution Key is a column often used in joins and filter clauses. Usage of the Distribution Key improves query performance.
        Example: Usually, either the product ID or the customer ID field will be selected as the distribution key.
        For Datamarts there are other function fields available:

  4. Key – This field is the primary key (marks if the line is unique). The source must have at least one key field, though more can be selected. Any non-numeric field can be part of the (composite) key. This field is important for updates of the data lines.
     Make sure you select here a field whose data do not contain leading spaces or national special characters (for details see Encoding).
     Using a time dimension field from Datamart as a key column in the related Data Source is not supported. Instead, compute the field in an Analytics logic via a Data Load and run another Data Load to join the related data.

  5. Percent Basis – Field which is used as a percentage basis. It must be a money field (data type). All other money fields are calculated as a percent of this field.

    • Per Unit Basis – Field which is used to calculate the "per unit". It must be a quantity field (data type).

    • Pricing Date – Field which is used as a date. It must be a date field (data type).

  6. Select the Data Type:

    • Number – Floating point number.

    • Quantity – Marks a field which contains the amount of products sold. The amount in this field will be taken into account for all fields marked "Per Unit" or "Extended". It makes sense to have just one Quantity field.

    • Text – String

    • Date – Data is formatted as a date.

    • Money – Data is formatted as money: contains a number which will be combined with the Currency field. Can be converted to a different currency.

    • Currency – Data is formatted as currency and are used for the conversion to other currencies. Only one Currency field is allowed. For details on currencies, see How to Set up Currencies.

    • UOM – Unit of measure: data is a unit. These can be converted into another unit.

    • LOB – Data is a long string of text (up to 4,000 characters). Data of this type cannot be used for grouping.

    • DateTime – Data is formatted as date and time. It is used in detailed time series.

    • Boolean – Data indicating false or true.

  7. (Optional) Define the number format applied to the field (e.g., #,##0.00). Available only for numeric fields.

  8. Set Label and Name. The label is the usual representation of a field in the user interface. Field names are used in calculated field expressions (in a Datamart definition) and formula logic, as well as any integration configuration (Data Loads, exports…). Generally, a label change is cosmetic while a name change would require corresponding modifications wherever the field is used. A name must not contain any spaces or special characters (only [a..z], [A..Z] and [0..9] are allowed).
    Avoid having a field with the name id, Id or ID; otherwise the Data Source cannot be deployed.

  9. Select Measure indicating whether the data is a sum or a value per unit. This field is only necessary for data types Money and Number.

    • Per Unit – (Raw) data are available per unit. Such, the values are multiplied by the quantity to form a sum.

    • Extended – (Raw) data are the sum. Such, the values can be divided by the amount in order to calculate the value of each unit.

    • Fixed – Data and numbers are not multiplied by the quantity nor divided.

  10. Define if the field can be used as a dimension in analytical charts and tables (only Text, Date, Currency, Integer, UOM and DateTime data types can be used as dimensions; for details on Integer click here).

  11. (Optional) You can hide this field from the drop-down lists with variables (for axis, group-by, additional measure, generic and dimension filters) in charts and rollups (but not Dashboards). Remember that if you later change the field visibility, you must re-deploy the Data Source (or Datamart).

  12. Click Add. Repeat the process until you have defined or changed all fields.

  13. Click Deploy to save and activate the Data Source (saving the Data Source as a draft is not possible). As soon as the Data Source is deployed, the Data Feed and Data Load are automatically generated.

The changes and additions are not available in Analytics unless you successfully activate the Data Source.

Found an issue in documentation? Write to us.

 
Pricefx version 13.1