Sybase IQ
The Sybase approach to data ware hous ing is fun da men tally dif fer ent from other rela tional data base ven dors. Sybase has con cluded that con ven tional rela tional approaches to ROLAP are inef fi cient and can only pro vide ade quate per for - mance (if at all) at the cost of a sig nif i cant, and oth er wise unnec es sary, invest - ment in addi tional hard ware, soft ware, resources, money and time. Sybase, there fore, has devel oped what might best be described as an inverted rela tional data base. ... When we first reviewed Sybase IQ, we stated that, in gen eral, we found the com - pany’s argu ments in favor of a colum nar approach to be “quite con vinc ing”. How ever, today, with the increased demands made upon data ware hous ing by the increase of Web-derived data and users and the expe ri ence of cus tom ers, we are con vinced that IQ offers a supe rior approach to those of tra di tional rela - tional ven dors. Architecture Unlike ordi nary rela tional data bases, which store data in tables by row, Sybase IQ stores and accesses data in data in tables by col umn. ... In fact, it is not quite as sim ple as this, since there are a num ber of ways in which IQ sup ports these indexes as col umns, which are dis cussed below. Another con se quence of the use of col umns is that IQ is much more effi cient than tra di tional approaches when it comes to data com pres sion (accord ing to Sybase, up to five times better). ... Sybase argues (and we agree) that the colum nar nature of Sybase IQ pro - vides so much better per for mance than nor mal ROLAP approaches that it does not need to sup port hard ware par al lel ism in the same way that its major com pet i tors do or that Sybase Adap tive Server Enter prise does. ... Sybase would fur ther argue that this is sim ply a com pen sat ing mech a nism for the poor per for mance inher ent in a row-based approach. While there is a lot of truth in Sybase’s argu ments, this does not mean that Sybase eschews all forms of data par ti tion ing. ... Finally, it should be noted that Sybase does not eschew the use of OLAP. ... For this rea son, Sybase has extended its OLAP capa bil i ties in release 12. ... Supported Schemas Sybase IQ sup ports con ven tional rela tional schemas, includ ing the normalized schemas used for trans ac tion pro cess ing as well as the star, snow flake and con stel la tion (a col lec tion of stars) schemas that are used in data ware hous ing. ... Sybase IQ © Bloor Research, 2003 Page 7 • The com plex ity of fact and di men sion ta bles is sig nif i cantly re duced. As long as all the col umns in a ta ble share the same keys, IQ will man age the facts and di men sion data in one ta ble, with out data ex plo sion. ... • Be cause it is easy to add col umns to a ta ble with Sybase IQ, Rcubes are very for giv ing if there are changes in the busi ness en vi ron ment or if new data sources be come avail able. ... Sybase IQ Page 8 © Bloor Research, 2003 Fig. 1: Star Schemas and Rcubes Indexes The secret of Sybase IQ is its indexes. As Sybase cus tom ers dis cover new needs for anal y sis, Sybase can sim ply cre ate new indexes to meet those needs. ... In the realm of real-time enter prise and closed-loop appli ca tions, Sybase sees its approach to indexes as the key for even greater query per for mance against multiterabyte and (in the future) petabyte data ware houses. Today, IQ in prac tice uses eight index ing tech niques: • Low Fast In dexes – These are low car di nal ity in dexes that use a pro cess known as tokenisation. ... This is why Sybase refers to these as low-car di nal ity indexes since they are typ i cally only used for fields that have less than 1,500 unique val ues. • Bit-Wise In dexes – For high car di nal ity fields, where the num ber of pos si ble val ues ex ceeds 1,500 (for ex am ple, mon e tary val ues), Sybase uses a pat ented tech nol ogy known as Bit-Wise in dex ing. ... This type of in dex ing Sybase IQ © Bloor Research, 2003 Page 9 has not, his tor i cally, been as so ci ated with data ware hous ing. ... These fea tures mean that the index ing in Sybase IQ over comes a num ber of the tra di tional draw backs of bitmap ping, namely, that they are not suit able for join ing tables or aggre gat ing data. Database operations The way that IQ is used, in prac ti cal terms, is illus trated in Fig ure 2. As can be seen, IQ includes an SQL API that allows SQL-based access. This is SQL-95 com pli ant and is the same SQL that is used in Sybase Adap tive Server Any where and (with a few excep tions) is also com pat i ble with the syn tax employed in Sybase Adap tive Server Enter prise (that is, T-SQL).