How to use a Snowflake-Schema in Mondrian?

Question

I have a fact table that has a RESOURCE_ID that links to the resource table. A resource has a role, that it self is a resource.

.--FACTTABLE--.  
| IDENTIFIER  |    .--RESOURCE---.
| RESOURCE_ID |----| RESOURCE_ID |    .--RESOURCE---.
                   | ROLE_ID     |----| RESOURCE_ID |
                                      | TITLE       |

Now i want to create a dimension ROLE that contains the attribute TITLE.

How to do this? Example with Mondrian 4 Schema would be appreciated.

I know there is a <Link> for the <PhysicalSchema> and the <ForeignKeyLink> for the <DimensionLinks> but i don't know how to use them properly.

Answer 1

Okay i found out how to do this.

---

Solve cycles

Since role and resource are both from the table RESOURCE, Mondrian couldn't handle that and came into a cycle.

I solved this with an alias for the RESOURCE table:

<Table name="resource" schema="public" keyColumn="id" alias="role"/>

Link tables

To let Mondrian know which resource belongs to which role, you need to link both tables.

Therefor it's important that the resource table has a key:

<Table name="resource" schema="public" keyColumn="id"/>

Now you can link the resource to it's role:

<Link target='resource' source='role' foreignKeyColumn='role_id'/>

Define the dimension

The confusing thing at first, is that the role dimension needs the table resource. That's because the fact table only knows the RESOURCE_ID and not the ROLE_ID. But since every resource is linked to it's role, you can use the Attributes to define the role specific fields.

<Dimension name="Role" table="resource" key="ID">
    <Attributes>
        <Attribute name="ID" keyColumn="id" hasHierarchy="false"/>
        <Attribute name="Title" table="role" keyColumn="title"/>
    </Attributes>
</Dimension>

Use the dimension

To use the dimension we have to use the dimension in the cubes dimensions and link the RESOURCE_ID of the fact table to the role dimension.

Useage:

<Dimension source="Role"/>

DimensionLinks:

<ForeignKeyLink dimension="Role" foreignKeyColumn="resource_id"/>