Ashish

Q:

What are the major differences between ROLAP and MOLAP? Could you explain with examples?

A:

Doug Hackney's Answer: This is a condensation of a full day's worth of seminar information into a few paragraphs. Please forgive the brevity. MOLAP (multidimensional OLAP) tools utilize a pre-calculated data set, commonly referred to as a data cube, that contains all the possible answers to a given range of questions. MOLAP tools feature very fast response, and the ability to quickly write back data into the data set (budgeting and forecasting are common applications). Primary downsides of MOLAP tools are limited scalability (the cubes get very big, very fast when you start to add dimensions and more detailed data), inability to contain detailed data (you are forced to use summary data unless your data set is very small), and load time of the cubes. The most common examples of MOLAP tools are Hyperion (Arbor) Essbase and Oracle (IRI) Express. MOLAP tools are best used for users who have "bounded" problem sets (they want to ask the same range questions every day/week/month on an updated cube, e.g. finance). ROLAP (relational OLAP) tools do not use pre-calculated data cubes. Instead, they intercept the query and pose the question to the standard relational database and its tables in order to bring back the data required to answer the question. ROLAP tools feature the ability to ask any question (you are not limited to the contents of a cube) and the ability to drill down to the lowest level of detail in the database. Primary downsides of ROLAP tools are slow response and some limitations on scalability (depending on the technology architecture that is utilized). The most common examples of ROLAP tools are MicroStrategy and Sterling (Information Advantage). ROLAP tools are best used for users who have "unbounded" problem set (they don't have any idea what they want to ask from day to day; e.g., marketing). It is very important to pay close attention to the underlying architecture of ROLAP tools, as some tools are very "scalability challenged." HOLAP (hybrid OLAP) addresses the shortcomings of both of these technologies by combining the capabilities of both approaches. HOLAP tools can utilize both precalculated cubes and relational data sources. The most common example of HOLAP architecture is OLAP services in Microsoft SQL Server 7.0. OLAP vendors of all stripes are working to make their products marketable as "hybrid" as quickly as possible. It is critically important to closely examine the architectures of these "repackaged/repositioned" offerings, as their "HOLAP" claims may be more marketing hype than architectural reality.

Types OLAP systems have been traditionally categorized using the following taxonomy [edit]Multidimensional Main article: MOLAP

MOLAP is the 'classic' form of OLAP and is sometimes referred to as just OLAP. MOLAP uses database structures that are generally optimal for attributes such as time period, location, product or account code. The way that each dimension will be aggregated is defined in advance by one or more hierarchies. [edit]Relational Main article: ROLAP ROLAP works directly with relational databases. The base data and the dimension tables are stored as relational tables and new tables are created to hold the aggregated information. Depends on a specialized schema design. [edit]Hybrid Main article: HOLAP There is no clear agreement across the industry as to what constitutes "Hybrid OLAP", except that a database will divide data between relational and specialized storage. For example, for some vendors, a HOLAP database will use relational tables to hold the larger quantities of detailed data, and use specialized storage for at least some aspects of the smaller quantities of more-aggregate or less-detailed data. [edit]Comparison Each type has certain benefits, although there is disagreement about the specifics of the benefits between providers. Some MOLAP implementations are prone to database explosion. Database explosion is a phenomenon causing vast amounts of storage space to be used by MOLAP databases when certain common conditions are met: high number of dimensions, pre-calculated results and sparse multidimensional data. The typical mitigation technique for database explosion is not to materialize all the possible aggregation, but only the optimal subset of aggregations based on the desired performance vs. storage trade off. MOLAP generally delivers better performance due to specialized indexing and storage optimizations. MOLAP also needs less storage space compared to ROLAP because the specialized storage typically includes compression techniques.

ROLAP is generally more scalable. However, large volume pre-processing is difficult to implement efficiently so it is frequently skipped. ROLAP query performance can therefore suffer. Since ROLAP relies more on the database to perform calculations, it has more limitations in the specialized functions it can use. HOLAP encompasses a range of solutions that attempt to mix the best of ROLAP and MOLAP. It can generally pre-process quickly, scale well, and offer good function support. [edit]Other

types

The following acronyms are also used sometimes, although they are not as widespread as the ones above 

WOLAP - Web-based OLAP



DOLAP - Desktop OLAP



RTOLAP - Real-Time OLAP



SOLAP - Spatial OLAP (see Location intelligence)

[edit]APIs

and query languages

Unlike relational databases - which had SQL as the standard query language, and wide-spread APIs such as ODBC, JDBC and OLEDB - there was no such unification in the OLAP world for a long time. The first real standard API was OLE DB for OLAP (ODBO) specification from Microsoft which appeared in 1997 and introduced the MDX query language. Several OLAP vendors - both server and client - adopted it. In 2001 Microsoft and Hyperion announced the XML for Analysis specification, which was endorsed by most of the OLAP vendors. Since this also used MDX as a query language, MDX became the de-facto standard in the OLAP world.

Online transaction processing From Wikipedia, the free encyclopedia (Redirected from OLTP)

Online transaction processing, or OLTP, refers to a class of systems that facilitate and manage transaction-oriented applications, typically for data entry and retrieval transaction processing. The term is somewhat ambiguous; some understand a "transaction" in the context of computer

or database transactions, while others (such as the Transaction Processing Performance Council) define it in terms of business or commercial transactions.[1] OLTP has also been used to refer to processing in which the system responds immediately to user requests. An automatic teller machine (ATM) for a bank is an example of a commercial transaction processing application. The technology is used in a number of industries, including banking, airlines, mailorder, supermarkets, and manufacturing. Applications include electronic banking, order processing, employee time clocksystems, e-commerce, and eTrading. The most widely used OLTP system is probably IBM'sCICS.[citation needed]

Contents [hide] •

1 Requirements

•

2 Benefits

•

3 Disadvantages

•

4 See also

•

5 Contrasted To

•

6 References

•

7 External links [edit]Requirements Online transaction processing increasingly requires support for transactions that span a network and may include more than one company. For this reason, new OLTP software uses client/server processing and brokering software that allows transactions to run on different computer platforms in a network. In large applications, efficient OLTP may depend on sophisticated transaction management software (such as CICS) and/or database optimization tactics to facilitate the processing of large numbers of concurrent updates to an OLTP-oriented database. For even more demanding decentralized database systems, OLTP brokering programs can distribute transaction processing among multiple computers on a network. OLTP is often integrated into service-oriented architecture and Web services. [edit]Benefits Online Transaction Processing has two key benefits: simplicity and efficiency.

Reduced paper trails and the faster, more accurate forecasts for revenues and expenses are both examples of how OLTP makes things simpler for businesses. It also provides a concrete foundation for a stable organization because of the timely updating. Another simplicity factor is that of allowing consumers the choice of how they want to pay, making it that much more enticing to make transactions. OLTP is proven efficient because it vastly broadens the consumer base for an organization, the individual processes are faster, and it’s available 24/7. [edit]Disadvantages It is a great tool for any organization, but in using OLTP, there are a few things to be wary of: the security issues and economic costs. One of the benefits of OLTP is also an attribute to a potential problem. The worldwide availability that this system provides to companies makes their databases that much more susceptible to intruders and hackers. For B2B transactions, businesses must go offline to complete certain steps of an individual process, causing buyers and suppliers to miss out on some of the efficiency benefits that the system provides. As simple as OLTP is, the simplest disruption in the system has the potential to cause a great deal of problems, causing a waste of both time and money. Another economic cost is the potential for server failures. This can cause delays or even wipe out an immeasurable amount of data. OLTP Current data Short database transactions Online update/insert/delete Normalization is promoted High volume transactions Transaction recovery is necessary OLAP Current and historical data Long database transactions Batch update/insert/delete Denormalization is promoted Low volume transactions Transaction recovery is not necessary