Facts: Many to Many
The standard facts associates each fact row with a single member of the base hierarchy. This solution is fine for most cases, however there are cases where we want to associate the same row to multiple members. In this scenario, parents and ancestors should count only once the same row even though it might be part of multiple children/descendants. This is a many-to-many relation. Before starting the explanation you might have a look how to solve a many-to-many relation using multiple hierarchies in a same dimension in this link.
Let's start with an example taking our cube. For this example, a sales deal is no longer the job of a single individual but the work of a team. As we do not want to count twice the same deal in the employees dimensions we need to create a many-to-many relation.
For this new scenario we will have two new tables, 'Sales M2M' & 'Facts M2M'. The first one relates a sales transaction with a list of employees for building the many-to-many relation. The second one is a new fact table based on sales transactions instead of employees
sales_id, employee_id 1 , 2 1 , 3 2 , 3
date ,sales_id, license_id, amount 2010 Jan ,1 , 1 ,100 2011 Feb ,2 , 1 ,300
After building the [Sales Transaction] dimension we have to create a many-to-many cube. In icCube it's like creating a normal cube (you can even publish it as a standard cube). The responsibility of this cube is to return for each sales a list of employee. In our example, for each sales deal the team members.
Now we can create our cube and associate the employees dimension using the many-to-many. You'll see in the combo how a new option is available after creating the many-to-many relation cube
Eventually, we can deploy the cube and run the following query to see the result. You can see how the sales amount for [John] is not the sum of [Emily] and [Paul]:
Both the schema and the data of all fact examples are available for download here.
Next chapter: Facts: Many to Many using Arrays.