Analytical Need
Sometimes we will want to perform calculations in the dashboard that are complex & unintuitive. These calculations will take a heavy toll on our performance. Also, it will enable us to have consistent results for our measures (prevents the designer from constantly defining the measure in the dashboard, resulting in possible human errors).
That is why we need to prepare this data in our cube in advance.
For example: New Customers Analysis, Inventory Ratio per Product
Modeling Challenge
We will prepare a new table in the cube that will perform most of the logic. This will enable us to get clean & simple data into the dashboard.
Solution
Create a custom table out of the fact table.
Example 1: How many new customers have arrived each month/quarter/year.
In order to calculate this in the dashboard we will need to perform a multi-pass aggregation (the more records we have, the heavier this will be on performance).
We will create a table in the cube that holds the minimum date per customer ID. This way we will be able to aggregate the number of customers over time.
SQL:
SELECT s.CustomerID, min(s.OrderDate) AS FirstOrderdate
FROM [SalesOrderHeader] s
GROUP BY s.CustomerID
Note: when we connect the derived fact to the existing fact (as in this case), we have to make sure that we do not have a many to many connection
Uniqueness test SQL:
SELECT x.CustomerID, count(*) FROM
(SELECT s.CustomerID, min(s.OrderDate) AS FirstOrderdate
FROM [SalesOrderHeader] s GROUP BY s.CustomerID ) AS x
GROUP BY x.CustomerID HAVING count(*) > 1
Image 1. Derived Fact Uniqueness Test
Dashboard Example:
Image 2. New Customers Over Time - Based on the derived fact
Example 2 : Calculate the inventory ratio per product.
Image 3. Inventory Schema
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 UnitOnOrder by Quantity.
SQL:
SELECT [Products].ProductID,
tofloat(sum(UnitsOnOrder))/tofloat(sum(Quantity)) AS InventoryRatio
FROM [Products] JOIN [Order Details]
ON [Products].ProductID=[Order Details].ProductID
GROUP BY [Products].ProductID
HAVING tofloat(sum(UnitsOnOrder))/tofloat(sum(Quantity))>0
The result table will supply the required values:
Image 4. Inventory Ratio per Product
Now connect the custom table to the rest of the tables:
Image 5. Result Schema with Invetnory Ratio
We can also add the “InventoryRatio” measure to the “Products” table using lookup() function by “ProductID”.
Attachments (for example 1)
-