[aplusdev] Primitive function Deal?

Thu Sep 6 19:49:35 EDT 2001

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.

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. :)

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.  

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

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
> > 
> 
> 