Facts: Many to Many (OBSOLETE)

Since version 6.1 this feature is obsolete, there is a new way creating a many to many structure based on a bridge table, please use a bridges instead.

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.

OLAP Fact creation

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

OLAP Fact creation

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]:

OLAP Fact creation

Both the schema and the data of all fact examples are available for download here.

Next chapter: Facts: Many to Many using Bridges.