Need advice on modelling dimensions based on multiple key-value pairs

[gleb]

Hello!
I'm using BI Server 4.5 with Saiku plugin 2.3 installed to explore data gathered by a web traffic tracking application.
It is the first time I'm trying OLAP, and it works rather well.

However, I'm having trouble in modelling schema to analyze some part of my data, and I hope that you can guide me in the right direction.

My fact table consists of clicks. I've created a few measures and dimensions, and I'm already able to run some of the reports.

The problem is, that there can be some additional variables, that are connected to each click - basically, they are just key-value pairs.
In SQL it is a table which consists of 3 columns: click_id - FK to a click object, name(varchar) - name of the additional property, and value(varchar) - value of the additional property.

So each click has some (not more than 5-10) key-value pairs connected to it with arbitrary names.

How can I model this with OLAP?

The only option I see is to create a separate reporting database, where each of the possible property names would be a separate table in a star-schema, and a separate dimension in OLAP schema. Is this the way to go or are there some better possibilities?

Thanks!

[lucboudreau]

You should use level properties. ( http://mondrian.pentaho.com/document...ber_properties )

But first, you will need to modify your schema so that the hierarchy is based on a view which joins your dimension table to your properties tables. You can do this using your RDBMS, using a materialized view, or try modifying your schema so that Mondrian builds the view as part of the queries it issues. So for example, instead of:

<Table name=(...) />

you use:

<View alias="joined_table"> <SQL dialect="generic"> <![CDATA[select * from table1 join on table2 where (table1.column = table2.column)]]> </SQL> </View>