[Mondrian] Design question: 1 or 2 cubes in 1 or 2 tables

Julian Hyde jhyde at pentaho.com
Tue Jan 21 16:03:24 EST 2014


This is a big topic. The developers list isn't the right place for it.

The short answer is that I think you should use 2 tables in one virtual cube (in mondrian 4 there no virtual cubes, but there a cube can have multiple measure groups, to achieve the same effect).

The long answer has been asked so many times that I recommend that you read the book: http://www.manning.com/back/.

Julian


On Jan 21, 2014, at 2:31 AM, Harald Stowasser <mondrian at stowasser.tv> wrote:

> Hello,
> 
> I'm working on a System to analyze clicks and views in some big websites
> (4.000.000.000 PIs).
> 
> The cube for the views is very easy. (Shorted):
> 
> <Cube name="views">
>  <DimensionUsage source="website" />
>  <DimensionUsage source="banner" />
>  <DimensionUsage source="contentunit" />
>  <DimensionUsage source="period" />
>  <Measure name="views" aggregator="sum" />
> </Cube>
> 
> However the clicks are very similar. But there are some dimensions more:
> 
> <Cube name="clicks">
>  <DimensionUsage source="website" />
>  <DimensionUsage source="banner" />
>  <DimensionUsage source="contentunit" />
>  <DimensionUsage source="period" />
>  <DimensionUsage source="visibility" />
>  <DimensionUsage source="viewtime" />
>  <DimensionUsage source="landingpage" />
>  <Measure name="clicks" aggregator="sum" />
> </Cube>
> 
> 
> 
> Maybe you can give me a hint about the best practice to store those datas.
> 
> a, I could put clicks and views together in one cube. Then they are
> separate Measures like siblings.
> 
> b, I could make 2 cubes like I did in the examples above.
> 
> c, If I implement option b, should I even store the 2 cubes in 2
> separate Tables?
> 
> 
> 
> Some more Illustration:
> 
> In the most reports we display clicks and views together. But I feel a
> litle bit unpleasant to store them in one Record. Because the viewtime,
> landingpage and visibility are not relevant for the views.
> 
> bsp (Shorted):
> select landingpage_id as l_id
>     , website_id     as w_id
>     , banner_id      as b_id
>     , contentunit_id as c_id
>     , period_id      as w_id
>     , clicks, views
>     , visibility     as v
>     , viewtime       as vt
>   from report
> 
> | l_id | w_id | b_id | c_id | p_id | clicks | views | v | vt  |
> +------+------+------+------+------+--------+-------+---+-----+
> |    2 | 120  |   20 | 5864 | 1219 |     10 | 22124 | 1 |  20 |
> |    2 | 120  |   20 | 5864 | 1219 |      2 |     0 | 1 |  50 |
> |    4 | 120  |   20 | 5864 | 1219 |      1 |     0 | 1 |  50 |
> |    5 | 120  |   20 | 5864 | 1219 |      5 |     0 | 1 |  50 |
> |    2 | 120  |   20 | 5864 | 1219 |      9 |     0 | 1 |  80 |
> |    3 | 120  |   20 | 5864 | 1219 |     88 |     0 | 1 |  80 |
> |    9 | 120  |   20 | 5864 | 1219 |    132 |     0 | 1 |  80 |
> |    2 | 120  |   20 | 5864 | 1219 |    244 |     0 | 1 | 100 |
> |   12 | 120  |   20 | 5864 | 1219 |    210 |     0 | 1 | 100 |
> |   22 | 120  |   20 | 5864 | 1219 |    222 |     0 | 1 | 100 |
> ...
> 
> 
> 
> Thank you for any hint you can provide in this situation,
> 
>   Harald Stowasser.
> 
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian



More information about the Mondrian mailing list