[aplusdev] Primitive function Deal?

Thu Sep 6 21:45:51 EDT 2001

----- Original Message ----- 
From: "Derek Neighbors" <derek at gnue.org>
To: "stevan apter" <sapter at earthlink.net>
Cc: <aplusdev at d13.com>
Sent: Thursday, September 06, 2001 7:49 PM
Subject: Re: [aplusdev] Primitive function Deal?

> Stevan,
> 
> All I can say wow.  Im impressed.  I dont have code to the original
> application and have no burning desire to learn A, but I think A or a
> similar language is good fit for OLAP engine.

yes, i think that's right.  

> 
> Im looking for someone interested in writing an OLAP engine, sounds like
> you might be a good candidate.  As its apparent you understand rather well
> what one is. :)

ah, sorry - i'm otherwise engaged.  :(

> 
> If you prefer K there is no mandate it should be in A.  GNU Enterprise
> would love to have someone one actively building an OLAP engine to use.  I
> would be glad to help spec out what it needs and do testing and get people
> to adopt it.  

no, it should be written in A+ -- it's free, it's got a great gui,
and an olap engine requires nothing k does better than A+.

> 
> I plan on downloading the testing this as soon as possible.

i think it might be useful as a working example of the ideas, at least in
very simple form.

> 
> Derek
> 
> On Thu, 6 Sep 2001, stevan apter wrote:
> 
> > 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
> > > 
> > 
> > 
> 