Bridging the Gap between OLAP and SQL

  • Published on

  • View

  • Download


Bridging the Gap between OLAP and SQLJens-Peter Dittrich1, Donald Kossmann1,2 Alexander Kreutz21ETH Zurich 2i-TV-T AGSwitzerland www.i-tv-t.deAbstractIn the last ten years, database vendors haveinvested heavily in order to extend their prod-ucts with new features for decision support.Examples of functionality that has been addedare top N [2], ranking [13, 7], spreadsheetcomputations [19], grouping sets [14], datacube [9], and moving sums [15] in order toname just a few. Unfortunately, many mod-ern OLAP systems do not use that functional-ity or replicate a great deal of it in addition toother database-related functionality. In fact,the gap between the functionality provided byan OLAP system and the functionality usedfrom the underlying database systems haswidened in the past, rather than narrowed.The reasons for this trend are that SQL asa data definition and query language, the re-lational model, and the client/server archi-tecture of the current generation of databaseproducts have fundamental shortcomings forOLAP. This paper lists these deficiencies andpresents the BTell OLAP engine as an exam-ple on how to bridge these shortcomings. Inaddition, we discuss how to extend currentDBMS to better support OLAP in the future.1 IntroductionThe key observation that motivates this work is thatmodern industrial strength OLAP systems implementa great deal of database functionality which would ide-ally be provided by the underlying database product.Permission to copy without fee all or part of this material isgranted provided that the copies are not made or distributed fordirect commercial advantage, the VLDB copyright notice andthe title of the publication and its date appear, and notice isgiven that copying is by permission of the Very Large Data BaseEndowment. To copy otherwise, or to republish, requires a feeand/or special permission from the Endowment.Proceedings of the 31st VLDB Conference,Trondheim, Norway, 2005A typical and prominent example is SAPs Business In-formation Warehouse product (BW). Essentially, BWimplements a full-fledged query processor on top ofthe SQL query processor provided by the underlyingDBMS. SAP BW is just one example: all OLAP sys-tems we are aware of follow the same approach, inparticular, our own product BTell.It is unfortunate for both sides that OLAP systemsmake so little use of the functionality of a DBMS, evenmore so as DBMS vendors have made significant in-vestments in the past to improve OLAP capabilitiesof their systems [9, 14, 19, 5, 6, 17]. There are historicreasons for this situation [4] because certain develop-ments in OLAP systems precede the latest amend-ments to DBMSes. There are also technical reasons,due to missing functionality in state-of-the-art DBMSproducts. In addition, there are also economic rea-sons because OLAP vendors do not want to becomedependent on non-standard functionality provided bycertain DBMS vendors.1.1 ContributionsThe purpose of this paper is to explore the missingfunctionality and show how it can be implemented,using as an example the reporting component of i-TV-Ts BTell product. In summary, this paper makes thefollowing contributions:1. The Gap: We list the shortcomings of currentDBMS for building OLAP engines and reportingfront-ends.2. Bridging the Gap: We present i-TV-Ts OLAPand reporting engine as an example on how tobridge these shortcomings.3. Closing the Gap: We present a wish-list on howcurrent DBMS technology should be extended tobetter support OLAP and reporting front-ends inthe future.Former affiliation, 20032004: SAP AG, BW OLAP tech-nologyFigure 1: BTell reporting front-end (HTML)Based on our work, we hope to revive discussions onthe suitability of SQL for modern OLAP systems.This paper is structured as follows: the followingsection presents the requirements of a modern OLAPsystem using BTell as an example. After that, Sec-tion 3 identifies the problems encountered when build-ing a OLAP and reporting engine on top of currentDBMS technology. Section 4 presents how these prob-lems are solved in i-TV-Ts BTell product. Finally,Section 5 presents a wish-list on how current DBMSshould be extended to better support OLAP.2 Features of Modern OLAP SystemsAs an example for a modern OLAP system, we usethe BTell product of i-TV-T AG. BTell is a platformfor the development of Web-based information sys-tems. It has been used, among others, for the devel-opment of e-Procurement applications (e.g., forecast-ing, standard cost analysis, factory service agreements,electronic tenders and auctions) and massive multiple-player games (e.g. stock market simulations, businessdevelopment games). As of 2004, more than 100,000users in Europe have worked on various applicationsbuilt on the BTell platform. Currently BTell is used tobuild a large e-Procurement tool for Unilever in USA,Canada, and Puerto Rico. The applications typicallyimplement a large number of business processes andvery complex and flexible reporting. The users rangefrom power users that use the software everyday tousers that sporadically use the software, e.g., to down-load a pre-canned report.In this work, we focus on the reporting componentof BTell which is used to give users a live view on theirbusiness data. Figure 1 shows an example report gen-erated by BTell for a savings project application (allnumbers are fake). This application manages infor-mation of projects that help to reduce the costs of anenterprise. Each project is carried out by a team andreduces costs for products of a particular brand, for aparticular factory, in a particular country or businessunit, thereby making use of certain strategies (e.g.,outsourcing).The report of Figure 1 shows for each team, its tar-get savings, the number of projects it is involved in andthe actual savings by brand and subcategory. This isa typical report that a user of that application mightgenerate. It shows some features that a modern OLAPsystem must provide:a.) Multi-dimensional Pivot Tables: In Figure 1,sub-category and brand are pivoted; that is,each brand (142-SURF PWD, 148-SURF LIQ,etc.) is given its own column, subcategories (Fab-ric Cleaning, Fabric Conditioning) are repre-sented by a set of columns (one for each brand).b.) Moving Sums: For each subcategory, the totalof all savings of all brands in that subcategory isshown. These totals can also be pivoted.c.) Split Results: Depending on user settings, re-ports are divided into several pages so that theuser is not flooded with too much information. InFigure 1, the report is divided into two pages andonly the first page is displayed. Users can navi-gate to the second page by clicking on Page 2 inthe top part of the page.Obviously, BTell has a number of features which arenot shown in Figure 1, but which are also crucial forthe success of a modern OLAP system:a.) Interactive Controls: With simple clicks, moremoving sums can be generated, additional metricscan be displayed, and dimensions can be added orremoved. Furthermore, pivoting and un-pivotingas well as drill-down and roll-up are controlledby simple GUI features. For example, clicking onBody in the team column will allow the userto get the project information for each member ofthe team.b.) Selection lists: It is possible to specify selectionsby the use of condition boxes. For instance, itis possible to generate a report that includes allbrands except the brand 148-SURF LIQ.c.) Layout: Specific color encodings (e.g. trafficlights) can be used in all reports. Furthermore,reporting orders can be redefined (e.g., group theteams according to certain criteria rather thanlisting them in alphabetical order).d.) Downloads, Graphics: A report can be down-loaded to Excel. Furthermore, bar charts, piecharts, speedometers, etc. can be generated.e.) Pre-canned reports: The reports can be storedas bookmarks and then be re-evaluated with asimple click. Furthermore, bookmarks can be sentto other users (e.g. managers) by email so thatthese users can trace the latest results.While there has been significant progress on DBMSproducts, e.g., on Pivot Tables [3] and integrationof spreadsheet functionality [19], this progress is notenough in order to implement all these OLAP featuresdirectly using a DBMS. There are fundamental short-comings which will be described in the next section.3 The Gap: Why SQL is not EnoughMost of todays OLAP platforms rely on a relationaldatabase (ROLAP) which is used to store a historicalsnapshot of integrated data from several underlyingOLTP systems. The snapshot is either stored in spe-cialized schemas like the Star or Snowflake Schema;or in flat views like Operational Data Stores (ODS).The functionality of the RDBMS is extended by eachOLAP vendor (like SAP or i-TV-T) through a pro-prietary OLAP engine built on top of the RDBMS asdisplayed in the following Figure:OLAP Client OLAP ClientOLAP EngineRDBMS/SQLRDBMS/SQLThis architecture is used to perform a two-step (fil-ter/refine) data processing strategy:1. Filter: The RDBMS retrieves a superset of thedata that is actually needed. The RDBMS is onlyused to perform heavy data processing tasks likepre-aggregation and joins.2. Refine: The OLAP engine uses the superset tocompute the exact result to each query.There are several reasons why vendors choose a two-step architecture:1. Though SQL has been extended with a variety ofimportant new OLAP operators, e.g. the Cube [9],these operators are still not provided with eachRDBMS. Therefore, OLAP vendors tend to sup-port only the minimal set of SQL that is sup-ported by all RDBMS vendors.2. Even systems that implement the latest SQL stan-dard lack important OLAP features. As a con-sequence, system architects use only the commonset of functionality that is provided by all RDBMSvendors. Everything else will be implemented in-side the OLAP engine, even those tasks that couldbe performed by certain RDBMS products.ProfitsState Customer Product ProfitS1 C1 P1 1.0S1 C1 P2 1.0S1 C1 NULL 2.0S1 C2 P1 1.0S1 C2 P2 1.0S1 C2 NULL 2.0S1 NULL NULL 4.0S2 C1 P1 1.0S2 C1 P2 1.0S2 C1 NULL 2.0S2 C2 P1 1.0S2 C2 P2 1.0S2 C2 NULL 2.0S1 NULL NULL 4.0NULL NULL NULL 8.0:ProfitsState Customer Product ProfitS1 C1 P1 1.0S1 C1 P2 1.0S1 C1P2.0S1 C2 P1 1.0S1 C2 P2 1.0S1 C2P2.0S1PP4.0S2 C1 P1 1.0S2 C1 P2 1.0S2 C1P2.0S2 C2 P1 1.0S2 C2 P2 1.0S2 C2P2.0S2PP4.0PPP8.0:ProfitsState Customer Product ProfitP1 1.0C1 P2 1.0P2.0S1 P1 1.0C2 P2 1.0P2.0PP4.0P1 1.0C1 P2 1.0P2.0S2 P1 1.0C2 P2 1.0P2.0PP4.0PPP8.0a) The result of a ROLLUP operation b) Interpreting NULL-values c) Interpreting adjacent similaras multi columns values as multi rowsFigure 2: The result of a ROLLUP and its interpretationsIn summary, commercial OLAP engines tend to re-implement considerable database functionality. Theyperform database-like tasks like pivot computation,post-aggregation, hierarchy operations, semantic cor-rectness checks, caching, etc. The OLAP enginesbridge the gap between the relational world of theRDBMS and the multidimensional analysis requiredby the user.The following sections (3.13.3) identify three ofthese gaps. After that, Section 4 present how thesegaps are bridged in BTell. Finally, Section 5 presentsa wish-list on how to close the gap, i.e., how to ex-tend current RDBMS to better support OLAP in thefuture.3.1 Non-Relational Data ModelThis section shows that the tabular relational model isnot always suitable for OLAP because OLAP systemsmust present query results as part of a GUI. We ar-gue that a non-relational, cell-oriented representationof data is more appropriate to present query resultsthan the relational model. Furthermore, the relationalmodel is not able to unambiguously represent certainvalues.SQL 99 introduced two new operators for OLAP:CUBE and ROLLUP [9]. These operators compute mul-tiple groupings as well as intermediate aggregates andsums. The difference between the two operators is thatCUBE creates all existing aggregates whereas ROLLUPcreates only the subset of CUBE corresponding to a hi-erarchy of columns.For example, if we do a ROLLUP on State, Customerand Product, i.e.,SELECT State, Customer, Product, sum(Profit)FROM ProfitsGROUP BY ROLLUP (State, Customer, Product)ORDER BY State, Customer, Product;we receive the table displayed in Figure 2a.Example 1: (Multi Column Results) Figure 2acontains all rows from the base table Profits as well asadditional rows containing NULL-values. These NULL-values have to be interpreted as sums, since SQL doesnot provide a special format for sum. Figure 2b shows,how these NULL-values are interpreted over one or mul-tiple columns, respectively. The problem is that SQLalso uses NULL-values for outer joins. In this case, theNULL-value is interpreted as value does not exist. Todisambiguate between the two different semantics ofthe NULL-value, SQL 99 introduced a special columnfunction named GROUPING(). If GROUPING() is calledwith a NULL-value representing a sum, 1 is returned, 0if it has different semantics. Since OLAP-queries typ-ically contain outer joins, GROUPING() has to be usedwith a combination of CASE to ensure correctness. Thismakes SQL cumbersome and error-prone and simplynot expressive enough for OLAP applications.Example 2: (Multi Row Results) The ROLLUPoperation in the previous example used an ORDER BYstatement to sort the relation lexicographically oncolumns State, Customer and Product. Note, thatrelations are defined as sets, i.e., Profits State Customer Product Profit. If we sort a relationinto a sequence, it is not a relation anymore. In otherwords, a relation is not a sequence but a set.Figure 2b shows the sorted output of the ROLLUP-operation. Many key columns contain similar values inconsecutive rows, i.e., similar values are repeated foreach row. This is another interpretation convention ofSQL. It means, that these values represent an entrythat spans multiple rows, i.e., a multi row entry. Fig-ure 2c visualizes this interpretation. Adjacent similarvalues are merged to form a multi row cell.These multi row entries are neither supported bySQL nor by the relational model.Example 3: (Column Orders) Figure 2c shows adrill-down by State, Customer and Product. In otherwords: Profits are first drilled-down by State, theneach value of the State column is drilled-down by Cus-tomer. After that, each value of the Customer columnis drilled-down by Product.If the columns were in a different order1, say Cus-tomer, State, Product, we would see a different table.The order of columns implicitly defines a 3-level hierar-chy, where State is the root and Product the leaf level.Neither the order of columns nor inter-column hierar-chical dependencies are part of the relational model.Example 4: (Pivot Tables) A pivot table is a2-dimensional representation that displays values onboth the x- and the y-axis. For example, a pivot tablewith State and Customer on the y-axis and Product onthe x-axis looks as follows (another example is givenin Figure 1):ProfitsProductState Customer P1 P2PS1 C1 1.0 1.0 2.0S1 C2 1.0 1.0 2.0S1P2.0 2.0 4.0S2 C1 1.0 1.0 2.0S2 C2 1.0 1.0 2.0S2P2.0 2.0 4.0PP4.0 4.0 8.0The above issues on multi row results, multi col-umn results and column orders fully apply to pivottables. However, since pivot tables can be seen as a2-dimensional extension of a roll-up, things get evenmore complicated. For example, the pivot table con-tains three columns for the Profit measure, one foreach value appearing in the Product column and atotals column. In addition, the pivot table in the ex-ample contains more aggregate values. For instance,the values ((S1S2),P,(P1P2)) are not part of theROLLUP in Figure 2.Currently, SQL does not support pivot tables. Re-cently, two new operators PIVOT and UNPIVOT havebeen proposed as first-class RDBMS operators [3].However, the proposal in [3] is not sufficient becausecolumns must be explicitly defined as part of the query,i.e., no dynamic pivot tables are allowed. In addi-tion, only one pivot dimension is possible. Given theseshortcomings and since only one DBMS vendor hasstarted to work on this topic, OLAP vendors are forcedto implement this important feature in their OLAP en-gine.The next section will explore additional deficienciesof SQL that are related to the client-server architectureof multi-tiered OLAP systems.3.2 Client/Server ArchitectureThis section shows that the current client/server com-puting model has problems when used for OLAP.Example 5: (Navigation) An important paradigmof OLAP is the concept of navigation. Typically, auser starts analyzing data by selecting an initial query.This query consists of a set of dimensions on the x- andthe y-axis as well as a set of filter conditions. After1A table with n key columns has n! different column orders.that, the user modifies the query in an interactive fash-ion by adding or removing columns (drill-down androll-up), adding or removing filter conditions (slicing),moving columns from the y- to the x- axis (dicing) andso on.This navigational pattern is best described by agraph representation, where the current selection of di-mensions and filter conditions corresponds to a node,i.e., the current state of the OLAP query. The edgesrepresent transitions between different states:Dimensions:CustomerProductDimensions:CustomerRoll-upDrill-downDimensions:Roll-upDrill-down Drill-downRoll-upby ProductbyCustomerbyCustomer, ProductThe transition from one state to another is unambigu-ously defined by the parameters of the transition. Itis not necessary to resubmit the entire query.In contrast, the query language that is used to de-clare OLAP queries, SQL, is stateless by definition. Ateach step, the entire query is resubmitted again andagain. No knowledge of previously submitted queriesof a user is preserved.Example 6: (Caching) In a three-tier architecture,each tier (client, application server and DBMS) main-tains separate caches. These caches are used to storedata received from other tiers or results computed forother queries. All caches and data stores outside theDBMS must be kept in sync manually. This again islabor intensive and error-prone.Figure 3 depicts the three tiers as well as their asso-ciated data stores and caches. The DBMS stores thebase tables. These tables are joined and aggregated tocompute views. Some of them are stored, i.e. material-ized [10], on the DBMS tier. Then, the OLAP engineat the application tier stores a subset of these views.It uses them to compute cubes, roll-ups and pivot ta-bles. Some of the results are stored in a separate cacheon the application tier. Finally, the client tier storesa subset of the results computed by the OLAP engine.The client further processes the data to produce for-matted reports in HTML, XML or MS Excel. Some ofthese reports are also cached on the client tier.The Figure shows that, from a birds eye perspec-tive, all three tiers perform the same task:1. Receive and store some input data.OLAP Client{ Cubes, Rollups, Pivots }{ HTML, XML, Excel }OLAP Engine{ Cubes, Rollups, Pivots }{ Materialized Views }RDBMS/SQLRDBMS/SQL{ Materialized Views }{ Tables }Figure 3: The 3 tiers and their associated caches anddata stores2. Perform algebraic query processing and optimiza-tion on the data.3. Store some output data, send some of it to othertiers.The punch line is that each tier has specific cachinglogic and that the DBMS cache which would be usefulfor all tiers is in the DBMS cage and cannot be used onthe different tiers. As a result, standard DBMS logicmust be replicated at all tiers.3.3 Computability of AggregatesIn this section, we show that the GROUP BY statementdoes not always produce the correct result. This prob-lem is called the problem of summarizability and wasfirst identified in [18]. [16] presents a detailed overviewon the problem and provides three necessary condi-tions for summarizability. [11, 12] study summariz-ability for selected classes of heterogeneous schemas.In order to implement summarizability correctly,the DBMS must be aware of the functional dependen-cies between columns, even in those cases, where basetables are joined and aggregated to create new views:it is not enough to consider functional dependencieson the base relations only. Since current DBMS op-timizers do not support this feature, summarizabilityawareness has to be provided by the OLAP engine.The following examples show how the lack of this fea-ture results in wrong query results.Example 7: (Unexpected Results) Consider a re-source planning application of a freight shipping com-pany. The following Figure shows a table Trucks, con-taining data on trucks and their capacity; and a tableTransports, containing company names, city and therequired capacity.SELECT Make, sum(capacity)FROM ( SELECT *FROM Trucks NATURAL JOIN Transports )GROUP BY Make;TrucksMake CapacityFord 10.0VW 10.0TransportsCompany City CapacityBigComp NYC 10.0BigComp LA 10.0 SELECT *FROM Trucks NATURAL JOIN Transports;PossibleTransportsMake Company City CapacityFord BigComp NYC 10.0Ford BigComp LA 10.0VW BigComp NYC 10.0VW BigComp LA 10.0SELECT Make, sum(Capacity)FROM PossibleTransportsGROUP BY Make;Trucks (Aggregate)Make CapacityFord 20.0 EVW 20.0 EBoth tables are joined by a natural join2. The resultingtable PossibleTransports contains a list of possibletruck-transport pairs.Now, the user asks for a GROUP BY on the Makecolumn of PossibleTransports using sum as theaggregation function. The resulting table Trucks(Aggregate) has the same structure as the source ta-ble Trucks. However, it contains different, i.e. unex-pected, data entries in the Capacity column; for ex-ample, the query result indicates that the capacity ofa Ford is 20 whereas it really is only 10.The source of this irritation is that the Possible-Transports table, an intermediate query result, is notnormalized and, thus, contains data redundancies. Ifsuch an intermediate query result is aggregated, dataitems from the base tables are used multiple times.As a consequence, the measures in the result of theGROUP BY query are wrong. Modern OLAP systemscan detect such situations and are able to computethe expected result; this, however, comes at the addi-tional price to carry out the aggregation in the OLAPengine, rather than pushing the whole query down tothe DBMS.Example 8: (Sales) OLAP measures are typicallyrelated to a unit and only values of the same unit canbe aggregated. The awareness for units is another fea-ture that SQL and state-of-the-art DBMS are lackingin order to adequately support OLAP applications. In2The natural join is used for sake of simplicity. In areal application, we would perform a theta join using =Trucks.Capacity Transports.Capacity as the predicate.the following, we will consider a sales application withthe following simple table representing sales profits:ProfitsState Customer Product Profit UnitS1 C1 P1 1.0 MM USDS1 C1 P2 1.0 MM USDS1 C2 P1 1.0 MM USDS1 C2 P2 1.0 MM EURS2 C1 P1 1.0 MM EURS2 C1 P2 1.0 MM USDS2 C2 P1 1.0 MM EURS2 C2 P2 1.0 MM EURThe following query should result in an ERROR:SELECT Customer, sum(Profit)FROM ProfitsGROUP BY Customer;However, standard DBMS will execute this queryand return the wrong result. (Alternatively, a user-defined aggregation function needs to be executed.)Again, an OLAP system will consider the peculiaritiesof units and make sure that all aggregates are carriedout correctly.4 BTell: How to Bridge the Gap?Ideally, todays RDBMS products and SQL should beextended to solve the problems mentioned in the previ-ous section. The relational model should be extendedto support order, hierarchies, multi-columns, multi-rows and multi-dimensional concepts like pivot ta-bles. The client/server paradigm should be broadenedto provide support for a more open query processingmodel. Last but not least, SQL should be extended tomodel functional dependencies and units not onlyat table, but also at query, view and result level inorder to guarantee correct results.Since all this is not likely to happen in the near fu-ture, OLAP vendors, like i-TV-T, have invented theirown solutions. In the following, we sketch some ofBTells solutions to these problems.4.1 Operator ModelIn this section, we first introduce the multi-dimensional operator model of BTell. After that, weexplain how BTells operators are used to provide effi-cient caching and pivot computation.Query processing in BTell is based on an operatormodel. In contrast to standard relational operators,we distinguish two classes of operators:1S The first class takes one or more input stream(s)and returns a single output stream. These opera-tors are non-blocking operators, i.e., iterators thatcomply with the open-next-close interface [8].3S The second class takes one or more inputstream(s) and returns three output streams.These operators are blocking operators.The streams of the 1S operators contain mixed datathat may apply to one of the axis or both of them. Incontrast, the three streams of the 3S operators havethe following semantics: the first stream contains datafor the x-axis; the second, data for the y-axis; and thethird stream data, that applies to both the x-axis andthe y-axis.Example: Recall the pivot table from the previousexamples:ProfitsProductState Customer P1 P2PS1 C1 1.0 1.0 2.0S1 C2 1.0 1.0 2.0S1P2.0 2.0 4.0S2 C1 1.0 1.0 2.0S2 C2 1.0 1.0 2.0S2P2.0 2.0 4.0PP4.0 4.0 8.0For this pivot table, BTell generates an x-streamcontaining tuples (S1,C1,2.0), (S1,C2,2.0), (S1,P,4.0),etc.; a y-stream containing tuples (P1,4.0), (P2,4.0);and an xy-stream containing tuples (S1,C1,P1,1.0),(S1,C1,P2,1.0), (S1,C2,P1,1.0), (S1,C2,P2,1.0), etc., re-spectively. This means, BTell splits the pivot into cellsthat are valid only on the x-, y-, or on both axis. Thisstrongly facilitates pivot computation as will be ex-plained in Sections PipeliningFigure 4 shows the pipeline of BTell OLAP. The oper-ators displayed as boxes are 3S operators. The opera-tors displayed as circles are 1S operators.Query processing is triggered by the clients. Theclient creates a ReportOptions instance, which col-lects the parameters of the query, like dimensions todisplay, filter conditions, etc. The ReportOptions aresent to BTells OLAP engine which passes them tothe top-level operator of the pipeline, e.g., the ExcelConvert operator. For the moment, we will assumethat the pipeline does not contain cached data. There-fore, each operator will call its parent operator untilthe Fetch operator is reached. The Fetch operatorsends SQL-queries to the RDBMS and retrieves theresult rows. The result is then split into three streams(x, y and xy) and sent to the next operator. Thenext 3S operators perform caching, filtering, sortingand grouping. After that, 1S operators perform pivotcomputation and post-processing on the pivot tables.4.3 CachingThe main idea of caching in BTell is to mixstandard query operators and special OLAPoperators with caching operators3. Therefore,3The same approach has recently been applied in a differentcontext to better utilize instruction cache performance [20].CacheSortGroupDrill-down FilterFilterObservable Result CacheCacheFetchSplitFormatLinkAlignXMLConvertCellMergeHTMLConvertExcelConvertx xy yx, xy, yx xy ymerged data3S operators1S operatorsRDBMSClient Client ClientBtell OLAPEnginePivotx xy yx xy yx xy yx xy yx xy yx xy yx xy yFigure 4: The processing pipeline of BTells OLAP Engineall 3S operators support a special operationmust reevaluate(ReportOptions). The semanticsof this operation are as follows: each time the reportoptions change must reevaluate(ReportOptions)may be called on an operator to determine whetherthat operator would now compute a different resultfor the given options. In the latter case, true is re-turned, false otherwise. Like that, must reevaluateweaves caching into the operator model. This greatlyfacilitates the implementation of the cache updatepolicies.4.4 Pivot TablesWe will now sketch the algorithm that is used by the3S operators to compute pivot tables.4.4.1 Pivot ComputationThe following algorithm extends the algorithm pro-posed in [9] to the 3S operator model. The mainidea of the our algorithm is to apply two lexicograph-ical sorts on the data. Assume we have a table withcolumns c1,..,ck,ck+1,..,cd, where d is the total numberof columns. We want to compute a pivot table withcolumns c1,..,ck on the y-axis and columns ck+1,..,cdon the x-axis, respectively. The pivot algorithm worksas follows:1. Y-Sort: Sort the data using columnsck+1,..,cd,c1,..,ck as the lexicographical com-pare order.2. Y-Group: Compute moving sums of all rows bysimply iterating column-wise through the data.Each group change generates a moving sum.3. X-Sort: Sort the data plus the newly createdmoving sums using c1,..,ck, ck+1,..,cd as the lexi-cographical compare order.4. X-Group: Compute moving sums of all columnsby simply iterating row-wise through the data.Each group change generates a moving sum.Steps 3 and 4 can be swapped with 1 and 2. This willproduce the same result.We will now briefly discuss the pivot algorithm asimplemented on top of the 3S operator model. TheSort and Group operators create three separate out-put streams of data. The y-stream contains tuplesrepresenting the keys displayed on the y-axis as wellas the measure m; the x- and xy-streams contain keysdisplayed on the x- and xy-axis, respectively. Thus, y-stream tuples have then format (, m); the x-and xy- streams have then format (, m) and(, m), respectively. For this reason,the actual Pivot operator simply performs a mergeof the three streams using columns c1,..,cd as the joinkeys. The latter join is implemented as a three-waymid4-outer sort-merge join.4.4.2 Post-processingThe Pivot operator generates a sorted stream of cellswith format (,) as itsoutput. After that, the cells are enriched by the 1Soperators. For example, the Format iterator convertsnumbers and dates to formatted output strings ap-plying the users regional settings; the Align iterator,aligns data cells to the right or left margin. Finally,three different Convert operators convert the cells toeither HTML, XML, or MS Excel.4.5 Computability of AggregatesIn this section, we present BTells algorithm for deter-mining summarizability of aggregates. To the best ofthe authors knowledge, BTell is the only product thatperforms such kind of summarizability check.4.5.1 Main AlgorithmThe main idea of our algorithm is as follows: First,the data model as well as functional dependencies be-tween columns have to be declared in the data dictio-nary. Second, at runtime the functional dependenciesare exploited to determine whether the current drill-down is valid.Recall the table from the running example5:ProfitsState Customer Product ProfitS1 C1 P1 42.00S1 C1 P2 42.42S1 C2 P1 11.00S1 C2 P2 5.00S2 C1 P1 42.00S2 C1 P2 42.42S2 C2 P1 11.00S2 C2 P2 5.00For this table, a user might declare a list of functionaldependencies as follows:{Customer, Product} ProfitThis means, Customer and Product determine themeasures column Profit. But, the column State nei-ther determines any other column nor does it dependon any other column.The following table shows the aggregates, i.e. mov-ing sums, that are valid for this example:ProfitsState Customer Product Profit is valid?. . . . YES. .P. YES.P. YESP. E NO E4outer is applied to the xy-stream only.5We have changed the numbers in the Profit column toavoid the trivial functional dependencies caused by a constantvalue.ALGORITHM FunctionalDependencyCheckInput:-columns c0,...,cd-measure m-set of functional dependencies F1: {ci cj}-set of functional dependencies F2: {cj m}Output:-interval T of dimensions, where totals are allowed(1) Using F1 and F2 compute minimal setof columns A={ci} that determines m(2) compute closure A+ of A(3) start dim = d + 1(4) ForEach i in {d,...,0}:(5) If ci A+:(6) start dim = i(7) Else(8) break(9) EndIf(10) EndFor(11) T = (start dim, d)(12) return TFigure 5: FunctionalDependencyCheck AlgorithmBTells FunctionalDependencyCheck algorithm is de-picted in Figure 5. It is invoked with a list of columnsci to check, one measure m and two sets of functionaldependencies: one containing dependencies betweencolumns, the other containing dependencies betweencolumns and the measure. The extension of the al-gorithm to multiple measures is straightforward andomitted for reasons of readability. The algorithm re-turns an interval T that contains the range of columnswhere movings sums are valid.The algorithm works as follows: it starts by com-puting the minimal set of columns A that determinethe measure m (line 1). After that, the closure A+ of Ais computed, i.e., all functional dependencies that areimplied by A (line 2). The variable start dim is usedto store the first column that may be aggregated. It isset to d+1 (line 3). Then, the algorithm iterates overthe columns starting at the rightmost column (lines 310). If the current column is determined by the closureA+ (line 4), the iteration continues and sets start dimto the current column index (line 5). Otherwise, the it-eration halts (line 8). The algorithm returns the inter-val T=(start dim,d) as the result, where start dimrefers to the last valid column that was checked in thefor-loop (lines 1112).For our example, the algorithm would compute A asA = {Customer,Product}. Then, the cover A+ wouldbe computed as A+ = A. The iteration starts with col-umn Product. Product is contained in the cover set.Therefore the iteration continues. The next columnto check is column Customer. Again, this column iscontained in the cover set A+. The next column State,however, is not contained in the cover set. Therefore,the iteration halts. T=(1,2) is returned as the result.This is the expected result.4.5.2 ExtensionsThere are some important extensions that haveto be considered when implementing Functional-DependencyCheck.Multiple Minimal Sets There are situations, inwhich multiple minimal sets exist that determine themeasure m (compare line 1 in Figure 5, also see Exam-ple 7). For these situations, the algorithm must notallow moving sums on the entire range of columns.Therefore, T = (d+1,d+1) should be returned.Pivot Tables For pivot tables, the algorithm is ap-plied separately on both drill-down dimensions (x-axisand y-axis). The result is then combined to determineplacement of moving sums. Note, that a moving summight be valid on both axis, on one axis or none ofthem.Units Units are treated separately. There are twocases: if no unit is present, or the input data set isrestricted to tuples that all have the same unit, valuescan safely be aggregated. Otherwise, aggregation isrestricted to those columns that have the same unit,or can be converted to a common unit.5 How to Close the Gap?The previous sections have identified the gap betweenOLAP and SQL and showed how this gap is bridged ina commercial product. In this section, we will explorehow to close the gap, i.e., how to extend DBMS to bet-ter support OLAP and reporting technology in the fu-ture. The extensions proposed here are not part of theBTell product; they are, however, currenty discussedas future development directions of our product.We think there are two paths to follow: the firstis to extend SQL with new OLAP features. This willhelp to close a lot of gaps like summarizabilty, unithandling, pivot computation and so on. On the otherhand, it is hard to extend SQL with features to rep-resent non-relational, multidimensional data (compareSection 3.1). Though the latter could be accomplishedby, e.g., using nested relations, handling OLAP queriesin SQL then would not become much easier.The second path to follow is to develop a new querylanguage designed for OLAP from the beginning6.This new language should be standardized. DBMSvendors should then provide add-on products to theirDBMSes that translate SQL to that new language andvice versa. This would, in the long-run, remove theneed to implement proprietary OLAP engines.6Microsoft has already developed a proprietary languagecalled MDX (Multi-Dimensional eXpressions). Though MDXhelps to fix some of the problems with SQL, many of the gapspresented in this paper are not tackled.In this section, we will sketch how this new queryand data definition language could look like. First ofall, recall that only small amounts of data are trans-fered between the users client and the OLAP engine.The heavy data processing tasks are performed onlyinside the DBMS or inside the OLAP engine. For thisreason we choose XML as the data return format the overhead introduced by XML will not substantiallydecrease the performance of our system proposal. Alsonote, that XML can already be processed by a hugenumber of reporting tools. To provide efficient queryprocessing on XML data XQuery is currently devel-oped to become the lingua franca of the XML world.Just recently powerful OLAP extensions have beenproposed to facilitate analytic queries with XQuery [1].5.1 Wish List for an Analytical Query Lan-guageWe will now sketch how an Analytic Query Language(AQL) should be designed to not only bridge but closethe gaps described in the previous sections of thiswork. We assume that XQuery plus the OLAP ex-tensions proposed in [1] will build the foundation forsuch a language. We do not present a complete spec-ification of these extensions here. We think that thisshould be accomplished by the research communityand the W3C XQuery committee. Our primary goalhere is to stimulate discussion on the topic. The fol-lowing items represent our wish list:1. AQL should represent all data and metadataavailable in the DBMS as XML views. Note, thatthe data has neither to be stored nor processed inXML format inside the DBMS. We only requireXML views in order to provide unified access tothe data.Impact: This allows to perform all data definitiontasks using XQuery7.2. AQL should be extended to enable abstract datadefinitions.Impact: This allows to model semantic relation-ships as well as functional dependencies.3. AQL should provide a facility to place, i.e. drill-down, attributes on rows or columns (just likeMDX).Example statement:for $f in //profitsgroup by $f/state, $f/customer ON ROWS,$f/product ON COLUMNSreturn ...Impact: This strongly facilitates the semantics ofOLAP queries.7We assume that update and insert operations will becomeavailable in XQuery in the near future.4. AQL should provide operators to automaticallygenerate multi-dimensional pivot, cube and rolluprepresentations.Example statement:for $f in //profitsgroup by ROLLUP ($f/state, $f/customer) on rows,ROLLUP ($f/product) on columnsreturn ...Impact: This allows to easily compute rollup,cube and pivot representations.5. AQL should provide multi-dimensional return for-mats.Example statement:Lets assume we want to compute the followingpivot result:ProfitsProductState Customer P1 P2PS1 C1 1.0 1.0 2.0S1 C2 1.0 1.0 2.0S1P2.0 2.0 4.0S2 C1 1.0 1.0 2.0S2 C2 1.0 1.0 2.0S2P2.0 2.0 4.0PP4.0 4.0 8.0We propose, that the statementfor $f in //profitsgroup by rollup ($f/state, $f/customer) on rows,rollup($f/product) on columnsreturn AS MDVIEWcreates the following result: 1.0 1.0 2.0 1.0 ... 8.0 Note, that this format preserves both hierarchies,as well on the x- as well on the y-axis. In addition,no result tuples of the aggregation get repeated.Impact: This allows to compute query results thatcan easily be postprocessed by a client applica-tion.6. AQL should allow to modify existing queries. Inaddition, stateful queries and sessions should bepossible.Example statement:DEFINE SESSION $s ASfor $f in //profitsgroup by $f/state on rows,$f/product on columnsreturn as mdview$ret = EVAL($s)Note, that the DEFINE command does not com-pute any results. This is only triggered by thefollowing EVAL statement. Lets assume the userwants to drill-down on attribute state. Thismeans, she has to modify the query. She coulddo this as follows:REDEFINE SESSION $sINSERT $f/customer$ AFTER $f/state on rows$ret = eval($s)Impact: Modifications performed by the user onthe reporting front-end are directly translatedinto statements of the query language. It is notnecessary anymore to reissue the entire query.7. AQL should allow to create query subscriptions(aka ative queries).Example statement:define session $s asfor $f in //profitsgroup by $f/state on rows,$f/product on columnsreturn as mdviewdefine function notify($res as $s/result,$metadata as $s/metadata)ON $s CHANGED{(: code to handle query result $res :)}The function notify is called whenever the sub-scribed query produces a different result. This iseither the case if the underlying data is changedor the query session gets modifed by a redefinestatement. This mechanism can easily be used bythe client software to redraw the screen: every-thing that remains to be done is to call redrawwhenever notify is called. In that case no ex-plicit call to eval is necessary anymore:define function notify($res as $s/result,$metadata as $s/metadata)ON $s CHANGED{call redraw_result_screen($res, $metadata)}Impact: This statement facilitates implementa-tion. In addition, this feature greatly facilitatesactive warehousing and monitoring applications.6 ConclusionDespite all efforts, database vendors are not makingthe impact on the OLAP market that they could have.BI vendors such as SAP, Cognos, or i-TV-T build thereown engines on top of DB products, thereby replicat-ing a great deal of DB functionality and only using thevery basic SQL 92 functionality (joins, group by andnested queries). The reason is that DBMS vendors arestill overlooking some of the fundamental deficienciesof SQL and the relational model. The gap is wideningand more and more stuff is added to OLAP enginesthat should ideally be implemented inside the DBMS.This paper has explored the gap between OLAP andSQL from a vendor point of view. Our contribution isthreefold: First, we presented the gap vendors are con-fronted with when building reporting engines on topof current DBMS technology. Second, we showed howthis gap can be bridged by a commerical OLAP engine,i-TV-Ts BTell product. Third, we presented a wishlist on how to extend DBMS to close the gap, i.e., howto better support OLAP and reporting functionalityin the future.We hope that our work revives discussions in the re-search community on the suitability of SQL for modernOLAP systems.References[1] K. Beyer, D. Chamberlin, L. Colby, F. Ozcan,H. Pirahesh, and Y. Xu. Extending XQuery forAnalytics. In ACM SIGMOD, 2005 (to appear).[2] M. J. Carey and D. Kossmann. Processing Top Nand Bottom N Queries. IEEE Data EngineeringBulletin, 20(3):1219, 1997.[3] C. Cunningham, G. Graefe, and C. A. Galindo-Legaria. PIVOT and UNPIVOT: Optimizationand Execution Strategies in an RDBMS. InVLDB, pages 9981009, 2004.[4] J. Doppelhammer, T. Hoppler, A. Kemper, andD. Kossmann. Database Performance in the RealWorld: TPC-D and SAP R/3. In ACM SIGMOD,pages 123134, 1997.[5] C. D. French. One Size Fits All Database Ar-chitectures Do Not Work For DSS. In ACM SIG-MOD, pages 449450, 1995.[6] C. D. French. Teaching an OLTP Database Ker-nel Advanced Data Warehousing Techniques. InIEEE ICDE, pages 194198, 1997.[7] F. Geerts, H. Mannila, and E. Terzi. RelationalLink-based Ranking. In VLDB, pages 552563,2004.[8] G. Graefe. Volcano - An Extensible and Par-allel Query Evaluation System. IEEE TKDE,6(1):120135, 1994.[9] J. Gray, A. Bosworth, A. Layman, and H. Pi-rahesh. Data Cube: A Relational AggregationOperator Generalizing Group-By, Cross-Tab, andSub-Total. In IEEE ICDE, pages 152159, 1996.[10] H. Gupta, V. Harinarayan, A. Rajaraman, andJ. D. Ullman. Index Selection for OLAP. In IEEEICDE, pages 208219, 1997.[11] C. A. Hurtado and A. O. Mendelzon. Reasoningabout Summarizability in Heterogeneous Multidi-mensional Schemas. In IEEE ICDT, pages 375389, 2001.[12] C. A. Hurtado and A. O. Mendelzon. OLAP Di-mension Constraints. In ACM PODS, pages 169179, 2002.[13] I. F. Ilyas, R. Shah, W. G. Aref, J. S. Vitter, andA. K. Elmagarmid. Rank-aware Query Optimiza-tion. In ACM SIGMOD, pages 203214, 2004.[14] ISO/IEC. SQL 1999. 9075-1:1999.[15] R. Kimball and K. Strehlo. Why Decision SupportFails and How To Fix It. ACM SIGMOD Record,24(3):9297, 1995.[16] H.-J. Lenz and A. Shoshani. Summarizability inOLAP and Statistical Data Bases. In SSDBM,pages 132143, 1997.[17] R. MacNicol and B. French. Sybase IQ Multiplex- Designed For Analytics. In VLDB, pages 12271230, 2004.[18] M. Rafanelli and A. Shoshani. STORM: A Statis-tical Object Representation Model. In SSDBM,pages 1429, 1990.[19] A. Witkowski, S. Bellamkonda, T. Bozkaya,G. Dorman, N. Folkert, A. Gupta, L. Shen, andS. Subramanian. Spreadsheets in RDBMS forOLAP. In ACM SIGMOD, pages 5263, 2003.[20] J. Zhou and K. A. Ross. Buffering Database Op-erations for Enhanced Instruction Cache Perfor-mance. In ACM SIGMOD, pages 191202, 2004.


View more >