What is the essential difference between relational and multidimensional databases?
I just don't get it. The arguments I find on the Internet do not make it clear to me, in fact they mostly do not make any sense at all. Here are some examples:
The majority of texts try to explain how multidimensional databases are better than relational databases (in terms of performance, ease of use and maintenance, etc.), but they do not explain what the formal difference between them really is. To clarify:
Relational databases implement the relational model; if multidimensional databases are really different from relational databases, then they must implement a different (non-relational) model, because otherwise they would still be relational, right? So my question is:
Is there really a different model for multidimensional databases? If so, is it formally defined and where can I find texts about it? And if it is not the case, what is the difference?
Please enlighten me.
You're asking about the differences between OnLine Transaction Processing databases (aka OLTP, e.g. SQL Server, My SQL) and On Line Analytical Processing databases (aka OLAP, e.g. Essbase, Sql Server Analysys Services SSAS).
They have very different purposes. For a store of data for a usual application (web site, desktop order processing etc etc) you would have to use an OLTP db as it's optimised for Transactions: basically CRUD operations and some reporting. An OLAP db, by contrast, is horrible at CRUD (you actually can't do CRUD in any OLAP db I know of), but fantastic at Reporting. The two work together.
Generally, your OLTP db is the workhorse. At some interval (daily, weekly, more often, less often) you extract either the whole OLTP db or just new bits into the OLAP db for reporting.
The internal structures of the two are quite different. An OLTP should have normalised tables and its code (the actual servers code, not ours) is optimised for a mix of read/write. An OLAP db is very denormalised (hence conceptually simpler in some ways) and optimised for reading. The benefits are such that an OLTP db I was using for reporting had a number of queries run daily which each took 45 mins plus. When we added a daily extract to an OLAP db, those reports ran in less than 5 seconds.
However, remember that you can't use an OLAP database for simple CRUD operations, and that an OLAP db will have extra licencing costs, management issues, and update timing issues: the users will get their data super fast, but it'll be out of date by some time and cost them extra. Also, they can be quite difficult to maintain.