To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=85825 Issue #|85825 Summary|Multidimensional Spreadsheets and OLAP Functionality Component|Spreadsheet Version|1.0.0 Platform|All URL|http://www.olapreport.com/origins.htm OS/Version|All Status|UNCONFIRMED Status whiteboard| Keywords| Resolution| Issue type|FEATURE Priority|P2 Subcomponent|code Assigned to|spreadsheet Reported by|discoleo
------- Additional comments from [EMAIL PROTECTED] Sun Feb 3 17:09:40 +0000 2008 ------- This issue is part of a more comprehensive issue describing *major design flaws* of existing spreadsheets. I have posted already major design flaws part (1.)(issue 79924), part (2.) (issue 80139) and a subissue of part (4.) (issue 83767). This is the Part (3.) of Major Spreadsheet Design Flaws continuing the design flaws in the Spreadsheet Structure. The implementation of this issue will also depend significantly on the Part (4.) issue in this series and on splitting the spreadsheet into 2 components (as described briefly in this issue and further on in a future post). SUMMARY ======= With the recent acquisition of MySQL by SUN Microsystems and the various developments in the IT world, a new appraisal of *multidimensional software* has become necessary. TOC ==== - INTRO: Multidimensional Spreadsheets -- Problem - SOLUTION -- Concept - UTILITY - LESSONS A. INTRO ======== So, what are Multidimensional Spreadsheets? I will begin with a small description of the problem. A.1. PROBLEM Lets say, a business takes account of its sales using a spreadsheet. Monthly data is analysed and used for various calculations. Now, a new month has started, and the data begins to be entered in the spreadsheet. The problem is, the user has to copy all the formulas from the previous month to the new month (and correct/adapt eventual copy-errors), then correct the formulas yielding yearly data/analysis (like yearly sales, ...) to use the data from the new month. This process is cumbersome and error prone. B. SOLUTION =========== The solution would be to make the spreadsheet automatically aware of this new data and: i.) apply all formulas correctly on this new data, while ii.) correcting any global formulas to take account of this data. This is already possible using so called multidimensional spreadsheets. A good account on the history of multidimensional software can be found at: http://www.olapreport.com/origins.htm. B.1. CONCEPT What is the concept of multidimensional software? The data is split into multiple dimensions. Basically, in this simple scenario, we have 2 dimensions: one describing the revenues, and the other the time (basically the months). When we add data for a new month, basically we extend the time dimension with one month. All formulas applying to monthly data, will apply to this new month as well. The global formulas will similarly consider the values for the new month. C. UTILITY ========== So, what is the utility of this software. There are many use cases. Please note, that this is a feature most useful in high-end and professional settings. It is likely that casual spreadsheet users (like most home users) won't benefit from this feature. This is why I advocate splitting the spreadsheet application into 2 separate entities: I.) a general canvas/table/grid-formatting program (this is what most home users do most of the time – only minor spreadsheet functionality needed – current spreadsheets fail miserably at this point), and II.) a professional multidimensional spreadsheet module. (I will deal with this splitting requisite in a future issue – for a glimpse of this requirement, you could read further details on the wiki page: http://wiki.services.openoffice.org/wiki/User_Experience/Grand_Concept#Calc but please note that this is work in progress and not ready yet for further discussions). The utility resides in corporate environments where complex spreadsheets are in use. Adding new dimensions to a spreadsheet is a rather cumbersome process, and one very error prone and hard to debug. Most users will simply copy some old formula columns to their new destination, and will try to correct the resulting formulas, but this generates a lot of hard to detect errors. Also, this process becomes significantly more complex with more than 2 dimensions. In our simple example, we had only 2 dimension (revenues vs time). But complex spreadsheets may contain a lot more dimensions: items sold, sales revenues, time, world region, OR to give another example from life sciences: study centre, time, visit-number, various physiologic parameters, and outcome data. Therefore, a multidimensional model would make life so much more easy and less error prone: instead of working with millions of formulas (in every cell a formula), one works only with general formulas that link one dimension to another one, e.g. Instead of C[1] = A[1] + B[1], we will have C[] = A[] + B[] applying to the whole array (dimension). When one extends one of the dimensions with a new value (say new month), than the C[] for the new dimension value will correctly be computed from the corresponding A[] and B[] in this new dimension-value (for the new month). I will end this account by quoting (and analysing) the lessons mentioned in the OLAP report: D. LESSONS ========== > So, what lessons can we draw from this 45-year history? > > 1. Multidimensionality is here to stay. Even hard to use, expensive, > slow and elitist, multidimensional products survive in limited niches; > When these restrictions are removed, it booms. We are about to see > the biggest-ever growth of multidimensional applications. Yes, observing recent developments in the IT community, I am fairly convinced, that the multidimensional concept is booming and will replace most of the spreadsheet functionality in high end businesses. Unfortunately, some remarks are needed here: I.) proprietary (DB-based)-software strted to replace the spreadsheets. This is very bad for the spreadsheet market. II.) there are many multidimensional-tools for DBs and MS Excel, but, to my knowledge, none for Calc. Still, I definitely prefer standalone multidimensional spreadsheets for many reasons (the multidimensional model is simply much too different from traditional spreadsheets). III.) Oracle, and other DB-companies have similar analysis functionality in their DBs, so the logical consequence will be that Sun will have to implement one in MySQL. This makes the idea of implementing the same engine both in MySQL and in Calc very tentative and feasible. On the long run, Sun will have to do it anyway to remian competitve. IV.) The money trail leads us invariantly to the big corporations, so these will remain one of the major revenue-generating customers for Sun. Multidimensional functionality will significantly increase the potential of Calc (and MySQL). > 2. End-users will not give up their general-purpose spreadsheets. > Even when accessing multidimensional databases, spreadsheets are > the most popular client platform, and there are numerous third-party > Excel add-ins for Microsoft Analysis Services to fill the gaps in > Microsoft’s own offering. > Most other BI vendors now also offer Excel add-ins as alternative > front-ends. Stand-alone multidimensional spreadsheets are not successful > unless they can provide full upwards compatibility with traditional > spreadsheets, something that Improv and Compete failed to do. Splitting the spreadsheet into a general Canvas/Grid-editing/formatting unit and a multidimensional model will be discussed further in a future mail. However, the lack of specialised multidimensional analysis packages for both Calc and MySQL will in general hurt the market for these products and should be taken into account when deciding when to implement such a functionality. > 3. Most people find it easy to use multidimensional applications, but > building and maintaining them takes a particular aptitude — which has > stopped them from becoming mass-market products. But, using a combination > of simplicity, pricing and bundling, Microsoft now seems determined > to prove that it can make OLAP servers almost as widely used as > relational databases. OOo is free, and bundling multidimensional functionality within OOo will surely promote it massively. Although home-users won't benefit much, this will generate momentum and accustom users to this functionality, making it easy to implement in businesses. > 4. Multidimensional applications are often quite large and are usually > suitable for workgroups, rather than individuals. Although there is > a role for pure single-user multidimensional products, the most successful > installations are multi-user, client/server applications, with the bulk > of the data downloaded from feeder systems once rather than many times. > There usually needs to be some IT support for this, even if the > application is driven by end-users. As pointed out, this is something most useful for big businesses, and these usually look forward to good IT support (and pay well for it). > 5. Simple, cheap OLAP products are much more successful than powerful, > complex, expensive products. Buyers generally opt for the lowest cost, > simplest product that will meet most of their needs; if necessary, they > often compromise their requirements. > Projects using complex products also have a higher failure rate, > probably because there is more opportunity for things to go wrong. A free and open-source OLAP product should allow for better development and rapid recognition of potential risks, therefore allowing a more rapid corrective response. I am sure that such a free and open source product will evolve significantly driven by the community, mitigating any potential disastrous effects for customers. --------------------------------------------------------------------- Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
