[aplusdev] Primitive function Deal?

[Alexander Skomorokhov]

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