Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

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

  1. #1
    Join Date
    Aug 2012
    Posts
    1

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

    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!

  2. #2
    Join Date
    Mar 2007
    Posts
    142

    Default

    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>
    Luc Boudreau
    aka. Luc le Magnifique
    aka. Monsieur Oui Oui

    Lead Engineer, Pentaho Corporation
    Web: http://devdonkey.blogspot.com
    Twitter: luclemagnifique
    IRC: Monsieur_Oui_Oui@freenode

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.