Histrogram in MDX with icCube -

how possible dynamic histrogram using mdx ?

for example, our schema based on web visits, we've number sessions , number of click-outs. have number of session 1 click-out taking account might depend on other dimensions (country, hour, entry-page...).

to solve going work 2 different concepts. first create new hierarchy , afterwards use mdx+.

first we've create new dimension, [histrogram]. new dimension contain defintion of buckets 2 member properties : start-bucket , end-bucket. pseudo table looks like

name start-bucket end-bucket 0-1       0           1 1-2       1           2 2-3       2           3 ... 10++     10   2147483647 

this hierarchy not linked facts , defines each member 2 properties defining bucket.

let's put use in mdx.

let's assume we've dimension, [sessions], , measure, [click-outs]. first we're going use oo features of iccube , create vector each session calculates number of [click-outs]

-> vector( [sessions], [click-outs], excludeempty )

vector has function, hist(start,end), need , counting occurencies between start , end (excluded).

vector( [sessions], [click-outs], excludeempty )->hist(0,1) 

putting our newly created hierarchy allows automize calculation buckets. const function ensures vector calculated once might time consuming.

the final mdx looks (note both function , calc. members created in schema script, once per schema):

with  const function clicksbysession() vector( [sessions], [measures].[click-outs], excludeempty )  member [session/clickout]  clicksbysession()->hist( [histogram].currentmember.properties("start-bucket", typed) , [histogram].currentmember.properties("end-bucket", typed)  select    {[session/clickout] } on 0,   [histogram].on 1 [clickout] --where  [geography].[europe] 

and there you've histrogram calculated dynamically can inserted in dashboard , reused.