Analytical Need
In some situations, we will have a transaction / fact table, that is having two or more of its columns referencing to another dimension table, such as order having pre-sale and sales reps involved in it or package that is assembled from several parts.
We will have to link between the tables in order to be able to analyse the sales according to the Employee dimension table.
Image 1. Original Tables
Modeling Challenge
These type of tables cannot be linked by a regular link since a field from 1 table can only be linked to one field in an another table. Whereas in this scenario, we need to link one field to two fields or more. Duplicating the Key field in the dimension table or separating it to additional link tables won't help here, and will result in displaying partial data.
Image 2. Incorrect Solutions
Solution
We will link the tables by using a “key” table that will associate each OrderID with the relevant Employees. For each combination of Order and Employee, we will add “Type” field, that represents the Employee type (Presale or Sales Rep):
Image 3. New Relationship/Key table
Implementation steps:
-
Create a Custom SQL Expression in the ElastiCube with the following Syntax:
SELECT Distinct
[orders].orderid,
[orders].[presale employee id] AS [Employee ID],
'Presale' AS [Employee Type]
FROM [orders]
UNION
SELECT Distinct
[orders].orderid,
[orders].[sales rep employee id] AS [Employee ID],
'SalesRep' AS [Employee Type]
FROM [orders]
-
Link the Orders table to the key table by the OrderID field, and the Employees table by Employee ID
Image 4. Result Schema
We will now be able to select an employee from the employees table and query the orders he/she was involved in. We can also filter the employees by the employee type field we added to the key table and analyzes the orders by presale and sales reps.
Attachments
-