[aplusdev] Primitive function Deal?

[stevan apter]

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
>