How to express facts belonging to multiple dimensions?

[mtassinari]

Hi,

I'm not sure if the title is worded properly, sorry for my english.

I'm trying to use JasperServer OLAP functionality, to see if it can be integrated in our web application, and I am facing the following problem: our software, among other things, manages "tasks" (things to be done) which can be linked to "organization unit" or UO; a task can belong to zero or more UOs.

Also, UOs form a tree which descends from a static root, the database keeps parent-child relationship.

Considering the above, how should my cube be modeled, if a fact belongs to more than one UO?

I thought about two possibilities:

1. save a single row in the facts table, in which I save the UOs ids as a comma separated string: but, in this case, how do I express the fact that there isn't really a join field but it should perform a FIND_IN_SET() insted?
2. save a row for each UO the task belongs to: but, in this case, wouldn't it count the task multiple times when computing the total, and also summing the task's cost multiple times?

Here is my tentative Dimension definition:

<Dimension foreignKey="uo_ids" name="Unità Organizzative">    <Hierarchy hasAll="true" allMemberName="Tutte le Unità Organizzative" primaryKey="uo_id">    <Table name="dim_uo"/>    <Level name="Unità Organizzativa" column="uo_id" nameColumn="uo" parentColumn="parent_id" type="String" uniqueMembers="false"/>    </Hierarchy></Dimension>

How can I express that, instead of trying to match directly uo_id = uo_ids, it should perform FIND_IN_SET(uo_id, uo_ids)?