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

Harald Stowasser mondrian at stowasser.tv
Tue Jan 21 05:31:10 EST 2014


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.



More information about the Mondrian mailing list