Derived Facts are additional facts that we calculate while importing or delivering the data. For instance:
Amount = Quantity*UnitPrice
Profit = Amount-Cost
Inventory Ratio =Sum(Quantity Sold) / Sum(Quantity Ordered)
Average Price = Sum(Quantity*UnitPrice) / Sum(Quantity)
We need to decide whether to calculate the derived facts “on demand”, meaning in the Dashboard, or calculating them in advance in the ElastiCube. Take into consideration that calculating 'On Demand' Derived Facts in the Dashboard can enable more dynamic filtration, while calculating them in the Elasticube stage will save query time when retrieving the data and enforce calculation consistency, especially with non-trivial facts, due to the fact that the author/designer end users will receive consistent results for measures, instead of having to create the complex measures individually, by their own understanding.
In the following schema we can create a derived fact to calculate the inventory ratio per product.
We’ll need to create a custom SQL Expression which joins the “Order Details” table with the “Products” table and returns the division result of “Quantity” and “UnitOnOrder”, with the following Syntax:
tofloat(sum(UnitsOnOrder))/tofloat(sum(Quantity)) AS InventoryRatio
FROM [Products] JOIN [Order Details] ON [Products].ProductID=[Order Details].ProductID
GROUP BY [Products].ProductID
The result table will give us the required values:
The only thing that has left is to connect the custom table to the rest of the tables:
**You can also add the “InventoryRatio” measure to the “Products” table using lookup() function by “ProductID”.