Datamart (DM) is a data table, with data coming generally from one or more Data Sources (it is possible to make a Datamart without a Data Source). This data set can then be enriched:

with data from other joined data sources (through related matching columns); Datamart is then a combination of joined data sources
or with data from 99584273 on the fly (not stored in DB)
or with columns, whose values are calculated by a logic (via a Calculation Data Load; these are stored in DB).

Datamart is a physical table but it does not store a copy of all attribute values (for the default Normalization settings with value "High"). Only keys and calculated columns are stored, the rest of the data is joined dynamically when a query is run.
An expression cannot be used as a key for joining tables; for details see this article.

Available options:

Source – Allows you to choose a Data Source from which the data should be pulled.
Normalization – You can decide whether you prefer quicker Datamart queries or refreshes. This option allows you to copy more fields to the DM table to speed up queries (SELECTs) at the expense of DM refresh performance or vice versa.
- Low – All primary DS (Data Source) fields and all numeric and dimension secondary DS fields are copied to the DM. This may result in quicker queries but (potentially much) slower DM refreshes, but it highly depends on the specifics of the schema. For example, where a secondary DS has a large number of rows, this option will likely speed up query time. But if all secondary Data Sources are small, it may even slow down queries. In other words, some experimentation may be needed to properly evaluate the benefits.
- Medium – All primary DS fields are copied to the DM. This way we avoid the join, at query time, of the DM and primary DS tables, which typical have the same (large) number of rows. This option provides the best balance between query and load performance.
- High – All DM fields sourced from Data Sources are joined together at query time. This yields the fastest DM refresh times, but in larger DMs (depending on the DB sizing, from a few million to over 10 million rows), query times will start to suffer.
Label – Allows you to set a Datamart label.
Base Currency – Allows you to set a base currency for this Datamart. (This currency is then shown as the first one in currency drop-down menus across the application.)
Base UOM – Allows you to set a base UOM for this Datamart.

It is not possible to modify field names (of fields coming from a Data Source) in a Datamart. The change has to be done at the Data Source level.

See also:

Calculated Fields

Calculated fields are added directly in the Datamart definition, in the 'Fields' tab (as opposed to fields sourced from Data Sources). In the expression, you can combine the existing Datamart fields with the simple arithmetic operators (+, -, *, /) and make use of the functions mentioned in the table below.

Calculated fields are evaluated at the query time, i.e. are not persisted in the Analytics DB. For this reason, no reloading or refreshing of Datamart data is required after adding or modifying calculated field expressions (Datamart deployment is still required).

Mainly for null-handling or defaulting, a small set of functions can be used as well:

Datamart Calculated Field Function	Comment	SQL Equivalent
TOSTRING(arg)		CAST(arg AS VARCHAR)
TODATE(arg)	Pricefx implementation only supports the CAST from a DateTime field to a Date. In other words, you can only convert a date with a timestamp to a date without a timestamp.	CAST(arg AS DATE)
ISNULL(arg)		(arg IS NULL)
IF(condition, valueIfTrue, valueIfFalse)	Examples: IF(SomeDiscount>0,100,0) IF(SomeDiscount=0,0,100) IF(Country="Germany",100,0) IF(Country!="Germany",0,100) IF(Country!="Germany","A","B") (only text fields will accept that the expression returns Strings)	CASE condition WHEN TRUE THEN valueIfTrue ELSE valueIfFalse END
IFNULL(arg1,arg2)	IFNULL(arg1, arg2) is equivalent to IF(ISNULL(arg1), arg2, arg1), i.e. if arg1 is NULL, arg2 is returned, otherwise it returns arg1.	COALESCE(arg1,arg2)
NULLIF(arg1,arg2)	The NULLIF(arg1, arg2) function compares arg1 and arg2 and returns NULL if they are equal. Otherwise, arg1 is returned.	NULLIF(arg1,arg2)

Some more functions are also in Datamart Query Expressions (only the first table General Functions applies to calculated fields – on row level values, there are no aggregations, hence no statistical functions apply).

More complex use cases can be handled with the help of formula logic in Analytics calculations.

Per Unit Basis & Percent Basis

Note the difference in behavior between Datamart and Data Source. In Datamart, all money columns that are defined as 'per unit' are already multiplied by Quantity. The purpose of 'auto-extending' in a Datamart query is that you do not need to care whether a money amount in the source DS is per-unit or not.

Comments:

Cost
- In DS – Is marked as a 'per unit'" measure. This means that the amount of money (number) coming from Data Feed is meant as a 'per unit' amount.
- In DM – Is already multiplied by Quantity (so by default it shows the total amount).
  - If the user wants to see the 'per unit' cost (e.g. in a chart), the system will divide the cost (stored in Datamart) by the Quantity column and return it.
  - If the user wants to see the cost as a percentage, the system will divide the Cost by the Invoice Price column (because it is marked as the 'percent' basis).
Invoice Price
- In DS – Is marked as an 'extended' measure. This means that the amount of money (number) coming from Data Feed is meant as the total amount (for all quantity).

Pricefx Classic Documentation

Datamarts

Per Unit Basis & Percent Basis