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?