[aplusdev] Primitive function Deal?

[stevan apter]

thanks sasha

one obvious improvement would be to eliminate zero rows and columns.  e.g. click on the
country=italy row and the resulting explosion contains zero rows for non-italian cities.
these should (or could) be removed from the display.  since 0 might be a valid value 
for some aggregation functions, the problem can't be solved merely by pruning the
display.  (i seem to recall that i punted for this reason).

there are several other approaches to the problem of multi-dimensional drill-down.  the
important thing is to separate the query machinery from the gui.

it bears repeating that A+ has everything you need to duplicate (or improve) the k toy.
and the A+ gui could be used to spectacular effect.  as i recall, there is a primitive
matrix widget with labelled rows and columns, which olap.k has to fake with k's table
widget.  

in some remarks about language benchmarking, arthur suggested that the code for an 
rdbms is a better measure than incrementing an integer a million times.  that's a lot
to ask from most languages, but perhaps an olap toy is not.  

best

sa


----- Original Message ----- 
From: "Alexander Skomorokhov" <askom at obninsk.com>
To: "stevan apter" <sapter at earthlink.net>; <aplusdev at d13.com>
Sent: Thursday, September 06, 2001 8:51 PM
Subject: RE: [aplusdev] Primitive function Deal?


> Stevan,
> 
> 15 minutes ago I had a very good plan to go to sleep (it's almost 5a.m.
> here).
> I looked in my Inbox and decided to read your message quickly. Your TOY-OLAP
> description is very consist and impressive. I never expected script over
> there, but
> PgDown and script appears. Well, I work with APL many years and it's
> difficult
> to impress me with small, but powerful code. I started K and see what this
> script
> produces and I'm really impressed. Looks GREAT. It would be very good (for
> array
> languages promotion) if you expand your letter a bit and publish a paper in
> some
> mainstream Data Mining/OLAP or programming magazine.
> 
> I do not want to sleep any more, but I am not sorry. Thank you for this
> extremely nice
> piece.
> 
> Regards,
> Sasha.
> 
> 
> > -----Original Message-----
> > From: aplusdev-return-127-askom=obninsk.com at d13.com
> > [mailto:aplusdev-return-127-askom=obninsk.com at d13.com]On Behalf
> > Of stevan apter
> > Sent: Friday, September 07, 2001 3:53 AM
> > To: aplusdev at d13.com
> > Subject: Re: [aplusdev] Primitive function Deal?
> >
> >
> > hi derek - welcome to the list.
> >
> > it shouldn't be too difficult to write a decent olap engine in A+, from
> > scratch.  syntax and presentation gui will be the challenging parts.  if
> > done right, it should run like a bat out of hell.
> >
> > below, you'll find a short example of what i take to constitute basic
> > olap capabilities.
> >
> > the engine requires data in the form of a hierarchical star scheme,
> > denormalized.  thus,
> >
> > three dimensions - product, location, time
> > each dimension has a hierarchy - e.g. a product consists of an industry,
> > a color, and a size.
> >
> > the fact table (called, helpfully i think, Fact) has a set of dimension
> > attributes and a set of measure attributes (in this example, v1 and v2).
> >
> > the gui is drill-down style:  the dimensions are arranged on the rows
> > and columns of the matrix, the cells are aggregations (e.g. sum).  click
> > on a cell -> replace the display with the explosion of the row and column
> > items; click on a row -> replace the display with the explosion of the
> > row item; click on a column -> replace the display with the explosion of
> > the column item.
> >
> > operations include:  reset to start, slice on a dimension, pivot
> > the display,
> > choose aggregation function, choose measure variable, choose
> > column display,
> > choose row display, and show detail (the selected subtable, prior
> > to aggregation).
> >
> > additionally, the current state of the drill-down is dynamically
> > translated
> > out to SQL and displayed in the lower-right corner.
> >
> > obviously, this is a toy - if you intend to work with more than a
> > few million
> > records in the fact table, you should replace the in-memory stuff with
> > file-based data, probably normalizing the star as well.  the real
> > question,
> > of course, is whether the data will live in mapped files or in some alien
> > database.
> >
> > the gui is kind of ugly (but it is fast!).  the A+ gui could be used to do
> > a bang-up job here.
> >
> > how easy would it be to translate this into A+?  i think it
> > should be quite
> > easy, and lots of fun.
> >
> > ps:  who wrote the A/dyalog olap tool you mention in your note?
> >
> > to run, download k and execute the script below.
> >
> > --
> >
> > / from www.kx.com/technical/contribs/
> >
> > \m f courier-bold-12
> > \m l courier-bold-12
> >
> > N:1000000
> >
> > Fact.industry:`home`office`military N _draw 3
> > Fact.color:`red`blue`green`yellow`orange`purple N _draw 6
> > Fact.size:`tiny`small`medium`large`gigundo N _draw 5
> > Fact.city:t N
> > _draw#t:`NY`LA`London`York`Bristol`Rome`Milan`Florence`Paris`Lyons
> > Fact.country:(.+(t;`US`US`UK`UK`UK`Italy`Italy`Italy`France`France
> ))Fact.city
> > Fact.year:1995 1996 1997 1998 1999 2000 N _draw 6
> > Fact.month:N _draw 12
> > Fact.day:N _draw 30
> > Fact.v1:N _draw 1000
> > Fact.v2:1.*N _draw 1000
> >
> > nub:{.+(x;{{x@<x}@?x}'Fact x)}
> >
> > H[`product`location`time]:nub'(`industry`color`size;`country`city;
> > `year`month`day)
> > I..d:"@[H;_n;*!:]"
> > Q..d:"H;.()"
> > F..d:"H;*!T"
> > M..d:"*(!Fact)_dvl,/!:'H[]"
> > X..d:"H;*!`I"
> > Y..d:"H;(!`I)1"
> >
> > nan:(0N;0n;;`)@- 1+4::
> > inf:(0I;0i)@- 1+4::
> >
> > T[`sum`num`min`max]:(+/;+/;&/;|/)
> > sum:{x#@[(*/x)#*0#z;x _sv y;+;z]}
> > num:{z;x#@[&*/x;x _sv y;+;1]}
> > min:{x#@[(*/x)#inf z;x _sv y;&;z]}
> > max:{x#@[(*/x)#-inf z;x _sv y;|;z]}
> >
> > Z..d:":[Detail;detail[I]Q;drill[I;Q;X;Y;F]M]"
> > Z..l..d:"\"Result: \",:[.k.Detail;$*|^.k.Z[];1_,/\" \",'$^.k.Z[]]"
> >
> > drill:{[i;q;x;y;f;m]
> >  n:#:'s:{H[x;i x]}'x,y
> >  d:f[n;s?/:'Fact[i x,y;j];Fact[m;j:true q]]
> >  d:(,nan[s 1],s 1),(,/T[f]'(,/d;+d)),'(,T[f]d),d
> >  a:.+(`e`k`bg;(0;".k.k[*_i].k.vn[]";bg))
> >  t:.+(i[y,x],g:`$"x",'$!*n;d;a)
> >  .[t;(~g;`l);:;$*s]}
> >
> > true:{[q]{x@&z=Fact[y;x]}/[_n;!q;q[]]}
> >
> > detail:{[i;q].+(f;Fact[f:df i;true q];.,`e,0)}
> > df:{[i]((!Fact)_dvl,/!:'H[]),,/(!i){((!H x)?y)_!H x}'i[]}
> >
> > vn:{`$(1+#$_d)_$_v}
> > bg:{:[((0=*_i)&~v=I Y)|I[X]=v:vn[];909090]}
> >
> > k:{:[(y=I Y)&x>0;ky x;(~y=I X)&x>0;kx[x]y]}
> >
> > nx:{(x,*|x)1+x?y}
> >
> > kx:{
> >  Q[I X,Y]:(H[X;I X;-2+(!Z)?y];H[Y;I Y;-1+x])
> >  if[I[X,Y]~i:(nx[!H X]I X;nx[!H Y]I Y);Detail::1]
> >  I[X,Y]:i
> > }
> >
> > ky:{
> >  Q[I Y]:H[Y;I Y;-1+x]
> >  if[I[Y]~i:nx[!H Y]I Y;Detail::1]
> >  I[Y]:i
> > }
> >
> > Start:"H::;Detail:0"
> > Pivot:"@[_d;`X`Y;:;Y,X];"
> > Slices..d:".+(!H;(#!H)#,\"Slice..a:_i\")"
> > Xs:@[H;_n;:[;"if[~_i=Y;X:_i]"]]
> > Ys:@[H;_n;:[;"if[~_i=X;Y:_i]"]]
> > Fs:@[_n;!T;:[;"F:_i"]]
> > Ms:@[_n;(!Fact)_dvl,/!:'H[];:[;"M:_i"]]
> > Start..c:Pivot..c:Slices..c:Ms..c:Fs..c:Xs..c:Ys..c:`button
> >
> > Detail:0
> > Detail..c:`check
> >
> > query:{[d;i;q;x;y;f;m]select[d;f;m],where[q],,group[i;x]y}
> > select:{[d;f;m]("select ",:[d;"*";($f),"(",($m),")"];"from Fact")}
> > where:{[q]:[0=#!q;();@[" and ",/:($!q){x,"=",value y}'q[];0;"where ",5_]]}
> > value:{:[4=4:x;"'",($x),"'";$x]}
> > group:{[i;x;y]"group by ",($i x),",",$i y}
> >
> > Query..d:".k.query[Detail;.k.I;.k.Q;.k.X;.k.Y;.k.F].k.M"
> > Query..e:0
> >
> > Slice..d:"sliced/[.+(!H;{.+(!x;x[];slicea[])}'H[]);!Q;Q[]]"
> > Slice..t:"if[`X _in _i;Q:slice[Q]Slice]"
> > Slice..c:`form
> > Slice..a..d:"*((!.k.H)_dvl .k.X,.k.Y),!.k.H"
> > slicea:{.+(`e`k`X`bg;(0;".k.c[]";();.k.bgc))}
> > sliced:{.[x;((!x)(y _in'!:'x[])?1;~y;`X);:;z]}
> > c:{@[~_v;`X;:;(d;0#d)(d:_v ._i)~(~_v)`X];.[_v;();::];}
> > bgc:{(;606060)(_v ._i)_in(),(~_v)`X}
> > slice:{[q;s]{x _di(!x)@&0=#:'x[]}q{@[x;!y;:;y[.;`X]]}/s[]}
> >
> > Z.[`y`x]:30 80
> > Query.[`y`x]:10 30
> > Slice.[`y`x]:10 50
> >
> > .k..a:(`Start`Slices`Pivot`Fs`Ms`Xs`Ys`Detail;`Z;`Slice`Query)
> > `show$`.k
> >
> >
> >
> >
> > ----- Original Message -----
> > From: "Derek Neighbors" <derek at gnue.org>
> > To: <aplusdev at d13.com>
> > Sent: Thursday, September 06, 2001 1:45 PM
> > Subject: Re: [aplusdev] Primitive function Deal?
> >
> >
> > > Im new to the list, heard about it from wxWindows list.  In a
> > nutshell we
> > > use a commerical product written in A and something called
> > Dyalog.  It is
> > > an OLAP engine.
> > >
> > > My question is any working on an OLAP engine with aplus?  I am
> > > co-maintainer of a large project called GNU Enterprise, that is aimed at
> > > enterprise software.  One thing we are lacking is OLAP
> > capabilities in the
> > > free software world.
> > >
> > > Derek Neighbors
> > > GNU Enterprise
> > >
> >
> >
> >
> >
>