Second use: Physical table column formulas

Below is an example of a physical table column formula:

[QUANTITYORDERED]*[PRICEEACH]

The references in this example specifically refer to the database column, not derived the physical column definitions. All operators and functions may be used in the definition of the physical table column. For this formula to be recognized, the isExact property of the physical table column must be set to true. The referenced physical column must be explicitly defined in the metadata model.

Multi-table expressions: Formulas can use any business column in the model.

It is possible to define formulas that use business columns from anywhere in the business model. For example, suppose there are two business tables:

Orders (fact table), ID=BT_ORDER_FACT
Product (dimension), ID=BT_PRODUCT

Suppose you want to calculate the turnover based on:

Orders (fact table), ID=BT_ORDER_FACT
Product (dimension), ID=BT_PRODUCT
The number of products sold, from the Orders table, ID=BC_FACT_ORDER_NRPRODUCTS
The price of the product, from the Product table, ID=BC_DIM_PRODUCT_PRICE

To get there, you must define a new business column, say in the Orders business table (although you could take Product too):

Table: Orders (BT_ORDER_FACT)
ID = BC_FACT_ORDER_TURNOVER
Name = Turnover
Formula = [BT_ORDER_FACT.BC_FACT_ORDER_NRPRODUCTS]
Exact = Yes
Aggregation Rule = SUM

The SQL generator is now going to replace the business columns by their respective SQL variants. As such, you must make sure that the business columns are resolving correctly. In this specific case, this means you want the two columns to be non-aggregated. If you now select the single business column BT_FACT_ORDER_TURNOVER, below is the SQL that is generated:

SELECT 
                SUM( BT_ORDER_FACT.NRPRODUCTS * BT_PRODUCT.PRICE ) AS COL0 
FROM
                FACT_ORDER BT_ORDER_FACT ,DIM_PRODUCT BT_PRODUCT 
WHERE           ( BT_ORDER_FACT.PRODUCT_TK =
                BT_PRODUCT.PRODUCT_TK )

Now, suppose you want to generate the multiplication of the two sums (different use-case). You define the formula as "[BT_ORDER_FACT.BC_FACT_ORDER_NRPRODUCTS] * [BT_PRODUCT.BC_DIM_PRODUCT_PRICE]" (without the SUM) and specify an aggregation for the two business columns in use. The generated SQL will be as follows:

SELECT 
                SUM( BT_ORDER_FACT.NRPRODUCTS ) * SUM( BT_PRODUCT.PRICE ) AS COL0 
FROM
                FACT_ORDER BT_ORDER_FACT ,DIM_PRODUCT BT_PRODUCT 
WHERE          ( BT_ORDER_FACT.PRODUCT_TK =
                BT_PRODUCT.PRODUCT_TK )

It is possible to create two versions of the used business columns, one aggregated (exposed to the users) and one non-aggregated (hidden from the users) for example.

The SQL generator works recursively. That means that it is possible to create a formula that calculates 7% (taxes for example) of the turnover:

ID = BC_FACT_ORDER_TURNOVER_TAXES
Name = Turnover Taxes
Formula = [BT_ORDER_FACT.BC_FACT_ORDER_TURNOVER] * 7 / 100
Exact = Yes

If you add that column to the selection, you get one extra column as shown below:

(  SUM( BT_ORDER_FACT.NRPRODUCTS  *  BT_PRODUCT.PRICE )  * 7 / 100) AS COL1