fbpx

Facts: Many to Many using Arrays

Since version 4.6 it's possible to define in all data source tables an array as the type for a table column. The main use of this new type is for defining direct many-to-many relations.

How can you define a direct many-to-many relation ?

The first point is creating a data table with a column of arrays (you have to use ';' in the array if the ',' is used for column separation)

    year       , amount
    [2006;2007], 100
    [2008;2007], 1600
    2008       , 200
    2007       , 400
    2006       , 800
    

The two first entries define an array of integers. We will need to add to the year column definition the flag to tell icCube the column is an array of primitive types.

After this we can create our dimensions and facts the same we do for other cases. When associating our fact table with the dimension - year -, the icCube engine will automatically create a many-to-many relation for all members - years - of the array.

Eventually, we can deploy the cube and run the following query to see the result.

    SELECT
        [Measures].members on 0,
        [Year].members on 1
    FROM [Cube]
    

You can see, how the amount 100 is on both 2006 and 2007 years, 1600 on both 2007 and 2008, but both are only aggregated once for the [All] member. Counts is counting the rows for each MDX tuple.

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

Next chapter: Facts: Multiple Levels shows how to associate data to no terminal members.