jdbc4olap user guide

1

Table of Contents Overview............................................................................................................................................3 Objective.......................................................................................................................................3 Relatio n al vs Multidi me n sio nal............................................................................................. 3 Jd bc4 ola p's m o d el..................................................................................................................... 3 XML fo r Analysis........................................................................................................................ 5 SQL - MDX co nversio n ................................................................................................................5 Feat u r e s ............................................................................................................................................. 6 Con n ec tio n ...................................................................................................................................6 Meta d a t a....................................................................................................................................... 6 Queries.......................................................................................................................................... 6 Set u p ..................................................................................................................................................8 O pe n Office Base Mon d ria n sce n a rio.................................................................................... 9

2

Overview Objective jd bc4 ola p' s goal is, a s its n a m e s ays, t o d eliver a rea dy - t o - u s e j d bc d river fo r OLAP d a t a b a s es. Jdbc is a n interface t h a t allows a n a p plicatio n t o co n n ect t o vario u s r elatio n al e nviro n m e n t s, b u t n o t m ul ti di m e n sio nal o ne s. Indee d, t h a t p r oject i m plies t o m a ke a b ri dge be twee n relatio n al a n d m ulti di me n sio n al worl d s, w hich a re inco m p a tible a s t hey rely o n tw o differe n t m o d els a n d involve differe n t tec h n ologies.

Relational vs Multidimensional Relatio nal d a t a ba se s, s uc h a s Oracle RDBMS, MySQL, MS SQL Server, e nclo se s e t s of t a bles, divide d in colu m n s a n d co n taining r ows of d a t a. Rows fro m differe n t t a bles ca n be linke d in or de r t o re p re s e n t a n a s s ociatio n be twee n t h e se s e t s of d a t a. SQL is t he s t a n d a r d lang uage t o dialog wit h a relatio nal d a t a b a s e s erver, in o r de r t o re t rieve or m a ni p ulate d a t a. Multidi me n sio n al d a t a b a se s, s uc h a s SAP BW, MS SQL Server Analysis Services, Mon d ria n, co n sis t of cube s of s everal di m e n sio n s (not o nly 3 like in a real cu b e), co m p o s e d of o ne or m o r e hiera rc hies of levels wh o se value s, calle d m e m b e r s, are d a t a p r o pe r ties. The d a t a is loca te d a t t he inter sectio n of t h e s e di m e n sio n s, in cube cells t h a t co n t ain differe n t values calle d m e a s u re s. MDX e m e rge d a s t h e s t a n d a r d lang u age for m ul ti di m e n sio n al d a t a b a se s, a n d d e s pite s o m e si milarities wit h SQL, h a s its ow n sy n t ax a n d m ec h a nis m s.

Jdbc4olap's model In o r d e r t o co n si de r a n OLAP ba s e fro m a relatio n al p oi n t of view, a co r re s p o n d e nce m u s t be es t a blis he d be twee n t he two m o d els. Let's co n si der t h e exa m ple cu be in t he following illus tr a tio n.

3

Are s h ow n o n t his diagra m 3 differe n t di m e n sio n s: Pro d uc t s, Geogra p hy, a n d Ti me a n d t h eir re s pective levels: {Prod uc t category, Pro d uc t fa mily}, {Contine n t, Cou n t ry}, {Year, se m e s te r}. Two m e a s u re s are available: Sales a n d Pro d u ctio n. In j d bc4ola p, t h a t cu be wo ul d be re p re se n te d as t he following relatio n al d a t a b a se:

Tha t sc he m a kee p s t he links betwee n t h e di m e n sio n s a n d t he m e a s u re s. An im p o r t a n t p r ecision m u s t be m a d e: t his exa m ple dis plays a cube w h o s e di m e n sio n s inclu de o nly o ne hiera rc hy. For exa m ple if t he Geogra p hy di m e n sio n h a d differe n t hiera rc hies corre s p o n di ng t o differe n t ways of exp re s si ng a locatio n, t h a t wo ul d genera te a s m a ny t a bles.

4

XML for Analysis In o r d e r t o re t rieve d a t a, we n e e d a way t o dialog wit h t h e s e rver. Amo ng t h e vario u s p o s sible ways t o acces s OLAP d a t a ba se s, we cho se XMLA. The se initials in dicate a s t a n d a r di z a tio n of t he interface of acces s t o m ul ti di me n sio n al b a se s, d efine d by a co n s o r ti u m of m a jo r ac tor s of t h e m a r ke t. Here's t he list of t h e p ri nci p al d ecisio n al sys te m s t h a t u s e t his s t a n d a r d: ● ● ● ●

Hyperio n Essba se 7 Micro s oft Analysis Services 2 0 0 5 Mon d ria n SAP BW fro m 3.0a

Base d o n XML, SOAP a n d HTTP s t a n d a r d s, t his tec h n ology co n sis t s of a web s ervice available o n t h e OLAP s erver w hich dialogue s wit h XMLA client s by exch a n ging SOAP m e s s ages . There are s everal ways t o s ec u re a web s e rvice a n d t h u s a XMLA acces s: SSL e nco di ng o n t he t r a n s p o r t layer, XML e nco di ng o n t h e m e s s age layer, a n d also acces s co n t rol wit h a p r oxy. An d, t he d river bei ng exclu sively in rea d - o nly m o d e, t h e re are n o p o s sible d a t a - d a m age iss ue s.

SQL-MDX conversion Cu bes bei ng re p re se n te d a s a relatio nal m o d el, a n d t he d river ai mi ng for t h e b e t t er JDBC co m pliancy, SQL wa s n a t u r ally t h e la ng uage t o u s e. But a s m o s t OLAP s erver s o nly s u p p o r t s MDX, a co nvert o r h a d t o be d e signe d a n d integra te d t o t h e q u e ry p r oce s s. Tha t's w hy t he re a re limitatio n s o n t h e s u p p o r te d q ue ries, a s all SQL q u ery co nce p t s ca n' t be t r a n sla te d in MDX.

5

Features Connection The firs t s te p in JDBC co m plia nce is t o co nfor m t o its co n n ectio n m ec h a nis m. Indee d, o nce t h e d river ins t alle d, a ny j d bc co n nect at te m p t wit h a locatio n s t a r ti ng wit h j d bc:jd bc4ola p: will s elect j d bc4ola p a s t he d river t o u s e fo r t h e co n n ectio n a n d fu r t he r o pe ra tio n s.

Metadata The JDBC's Data ba seMeta d a t a inte rface is fully im ple m e n t e d. First, t h a t m e a n s t h a t t he d river's p o s sibilities a n d li mita tio n s a re in dica te d. But it also m e a n s t h a t t he u s e r ca n inte rroga te a s e rver t o di scover its d a t a. Indee d h e ca n get n avigate t h r o ug h available catalogs, sc he m a s, t a bles a n d colu m n s. The se fea t u r e s ar e u s u ally u s e d t o h el p t h e u s e r s e t u p a q ue ry.

Queries As ex plaine d a bove, t he re are li mitatio n s in t he s u p p o r t e d SQL q u e ries, d u e t o t h e co nversio n t o MDX a n d t he im p o s sibility t o a d a p t s o m e co nce p t s. The q u e ry m u s t look like t his : query := 'SELECT' fieldList 'FROM' tableList 'WHERE' filterList  'GROUP BY' expressionList ';' fieldList := (field (',' field)*) | '*' field := tableStar | columnName tableStar := (catalog.schema.table|table|tableAlias)'.*' columnName := [catalog.schema.table.|table.|tableAlias.]column  [['AS'] fieldAlias] tableList := table (',' table)* table := catalog.schema.table [tableAlias] filterList := sqlExpression ('AND' sqlExpression)* sqlExpression := operand (relationalExpression | inClause) operand := NUMBER | STRING | filterColumn | '?' relationalExpression := '=' operand inClause := 'IN' '(' expressionList ')' expressionList := operand (',' operand)* filterColumn := [catalog.schema.table.|table.|tableAlias.](column| fieldAlias)

6

As we ca n see o n t his gra m m a r, m o s t of t h e limita tio n s co ncer n t h e filter s, w h o ca n o nly be co m bi ne d by AND o pe ra t or s, a n d co n sis t of ' =' o r 'IN' cla u se s. An im p o r t a n t p r ecisio n, in t h e act u al releas e, t h e filter s like 'colu m n = colu m n' are ignore d. Indee d it's i m plie d t h a t t he join s a re s e t correctly t o es t a blis h t h e m o d el d e scribe d before. So it's i m p o s sible t o p r oces s a car tesia n p r o d u c t. If yo u look caref ully a t t he gra m m a r, you'll n o tice t h a t '?' is s u p p o r te d in t h e filter s. In dee d, n o t o nly s t a te m e n t s b u t als o p re p a re d s t a te m e n t s a re s u p p o r te d in t his release.

7

Setup You n ee d t o a d d t he differe n t jar files fro m t he zi p a rc hive t o yo u r clas s p a t h. The clas s used to se t u p yo ur j d bc acces s is o rg.j d bc4 ola p.j d bc.Ola pDriver The u rl m u s t s ta r t wit h 'j d bc:jd bc4ola p:'. Here are differe n t exa m ple s wit h s erver s t es t e d wit h t his relea se: ●

MS SQL Server: j d bc:jdbc4ola p:ht t p: / / s e rver:por t / OLAP / m s m d p u m p. dll

●

Mon d ria n: j d bc:jd bc4ola p:h t t p: / / s e rver:por t / m o n d ria n / x mla

●

SAP BW: j d bc:jdbc4ola p:ht t p: / / s e rver:por t / s a p / b w / s o a p / x mla?sa p clien t = n u m b e r

8

Open Office Base

Mondrian scenario

Here are t he differe n t d e t aile d s te p s in u si ng t he d river fro m O pe n Office Base t o acces s a Mon d ria n OLAP d a t a ba se. Star t O pe n Office Base, a n d si nce a d a t a b a se m u s t o p e ne d, create o n e if n eces s ary:

In t he n ext scree n, yo u d o n' t n ee d t o register b u t choo s e t o p o pe n t h e d a t a b a se for e diting. Finally, choo se a locatio n a n d filena m e for t h a t b a se. Go t o Tools / O p tio n s a n d o pe n Ope n Office.org /Java se t tings:

9

Select a JRE 1.5 or a d d o ne wit h t h e «Ad d... » b u t t o n if n o ne is available. Click o n « Class Pat h... » a n d a d d all t h e jar files fro m t he j d bc4ola p a rc hive:

Everyt hing is n ow correctly s e t u p, a n d we d o n' t n ee d a ny m o re t he ba se we ju s t create d. We ca n re s t a rt Ope n Office t o create a JDBC co n nec tio n:

10

The «Tes t Clas s» b u t t o n s h o ul d give t his:

11

An d t he «Con nectio n Tes t» fro m t h e n ext sc ree n s h o ul d e n d t he s a m e way :

Once again, choo s e t o regis ter o r n o t b u t o p t for t h e e diting m o d e, a n d ch o o s e a filena m e. The n go t o Tables, a n d after a few seco n d s you'll s ee t h e d a t a b a se wit h t h e j d bc4ola p relatio n al m o d el:

12

Now you ca n really u s e t h e d a t a b a s e co n n ectio n. All t he fu nc tio n n alities h ave n' t b ee n te s te d yet, b u t you ca n for exa m ple crea te a n ew q ue ry :

Only a d d t a bles fro m t h e s a m e sc he m a, beca u s e o t he rwise it wo ul d ge ne r a te a q u e ry fr o m differe n t cube s, a n d t h a t ca n't be d o n e. The n s elect t h e colu m n s yo u wa n t o n yo ur q u e ry. A b ug in Ope n Office ca n occ u r h e re: if yo u d o u ble click in a b ox (tha t a p p e a re d w he n yo u a d d e d a t a ble) t o s elect a colu m n it work s fine, b u t if yo u u s e t h e co m b o in t h e b o t t o m p a r t of t h e scree n t o m a ke you r s electio n it ch a nges t h e n a m e of t h e colu m n a n d t he n t he q ue ry fails.

13

You ca n finally lau nc h t he q ue ry t o check its validity a n d its re s ults :

14